Spring Boot 配置多数据源【最简单的方式】
Druid连接池 Spring Boot 配置多数据源【最简单的方式】
0.前言
看了网上好多关于Spring Boot 配置数据库 多数据源的文章，其实不用那么多重复造轮子，目前已经有了一个特别成熟的开源组件dynamic-datasource ，已经支持各种各样的场景，来满足你多数据源的需求，我大概整理了一下，希望对初学者有用。
1.基础介绍
本文我们使用 dynamic-datasource 多数据源组件实现在springboot 项目中快速集成多数据源。
- 支持 数据源分组 ，适用于多种场景 纯粹多库 读写分离 一主多从 混合模式。
- 支持数据库敏感配置信息 加密 ENC()。
- 支持每个数据库独立初始化表结构schema和数据库database。
- 支持无数据源启动，支持懒加载数据源（需要的时候再创建连接）。
- 支持 自定义注解 ，需继承DS(3.2.0+)。
- 提供并简化对Druid，HikariCp，BeeCp，Dbcp2的快速集成。
- 提供对Mybatis-Plus，Quartz，ShardingJdbc，P6sy，Jndi等组件的集成方案。
- 提供 自定义数据源来源 方案（如全从数据库加载）。
- 提供项目启动后 动态增加移除数据源 方案。
- 提供Mybatis环境下的 纯读写分离 方案。
- 提供使用 spel动态参数 解析数据源方案。内置spel，session，header，支持自定义。
- 支持 多层数据源嵌套切换 。（ServiceA >>> ServiceB >>> ServiceC）。
- 提供 **基于seata的分布式事务方案。
- 提供 本地多数据源事务方案。
2.步骤
2.1. 引入依赖
- 1.引入依赖，具体版本参考你当前的项目依赖管理中添加
dependencyManagement中统一管理
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
-
- 排除原生Druid的快速配置类。
注意：v3.3.3及以上版本不用排除了。
方法1. 利用注解排除
@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
- 排除原生Druid的快速配置类。
@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
方法2. 利用配置排除
或者也可以使用这种方式在配置文件中排除
spring:
autoconfigure:
exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
2.2. 配置文件
增加配置如下，下面为了方便期间使用h2数据库作为示例数据库，mysql和其他数据库请按正确的jdbc url
配置
spring:
datasource:
druid:
stat-view-servlet:
enabled: true
login-username: admin
login-password: 123456
dynamic:
lazy: true
# 配置全局druid参数，请按需配置
druid:
initial-size: 5
max-active: 8
min-idle: 3
max-wait: 1000
validation-query: 'select 1'
datasource:
master:
username: sa
password: "123456"
url: jdbc:h2:mem:test;MODE=MySQL
driver-class-name: org.h2.Driver
druid:
socketTimeout: 1111
slave_1:
username: sa
password: "123456"
url: jdbc:h2:mem:test;MODE=MySQL
driver-class-name: org.h2.Driver
druid:
initial-size: 6
slave_2:
username: sa
password: "123456"
url: jdbc:h2:mem:test;MODE=MySQL
driver-class-name: org.h2.Driver
druid 的原生配置在dynamic-datasource都是实现了的，大家可以按需选择
spring:
datasource:
druid:
stat-view-servlet:
enabled: true
loginUsername: admin
loginPassword: 123456
dynamic:
druid: #以下是支持的全局默认值
initial-size:
max-active:
filters: stat # 注意这个值和druid原生不一致，默认启动了stat。 如果确定什么filter都不需要 这里填 ""
...等等基本都支持
wall:
none-base-statement-allow:
stat:
merge-sql:
log-slow-sql:
slow-sql-millis:
datasource:
master:
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://xx.xx.xx.xx:3306/dynamic?characterEncoding=utf8&useSSL=false
druid: # 以下是独立参数，每个库可以重新设置
initial-size: 20
validation-query: select 1 FROM DUAL #比如oracle就需要重新设置这个
public-key: #（非全局参数）设置即表示启用加密,底层会自动帮你配置相关的连接参数和filter，推荐使用本项目自带的加密方法。
# ......
# 生成 publickey 和密码，推荐使用本项目自带的加密方法。
# java -cp druid-1.1.10.jar com.alibaba.druid.filter.config.ConfigTools youpassword
2.3. 核心源码
多数据源组件核心源码解释
Druid数据源创建器
package com.baomidou.dynamic.datasource.creator.druid;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.logging.CommonsLogFilter;
import com.alibaba.druid.filter.logging.Log4j2Filter;
import com.alibaba.druid.filter.logging.Log4jFilter;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import com.baomidou.dynamic.datasource.creator.DataSourceCreator;
import com.baomidou.dynamic.datasource.creator.DataSourceProperty;
import com.baomidou.dynamic.datasource.enums.DdConstants;
import com.baomidou.dynamic.datasource.exception.ErrorCreateDataSourceException;
import com.baomidou.dynamic.datasource.toolkit.DsStrUtils;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import javax.sql.DataSource;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.*;
/**
* Druid数据源创建器
*
* 该类是一个实现了DataSourceCreator接口的类，提供了创建Druid数据源的方法。
*
* @since 2020/1/21
*/
@Slf4j
@NoArgsConstructor
@AllArgsConstructor
public class DruidDataSourceCreator implements DataSourceCreator {
// 配置参数列表
private static final Set<String> PARAMS = new HashSet<>();
// 从配置文件中复制配置信息的方法
private static Method configMethod = null;
static {
fetchMethod();
}
static {
// 设置Druid支持的连接参数
PARAMS.add("defaultCatalog");
PARAMS.add("defaultAutoCommit");
PARAMS.add("defaultReadOnly");
PARAMS.add("defaultTransactionIsolation");
PARAMS.add("testOnReturn");
PARAMS.add("validationQueryTimeout");
PARAMS.add("sharePreparedStatements");
PARAMS.add("connectionErrorRetryAttempts");
PARAMS.add("breakAfterAcquireFailure");
PARAMS.add("removeAbandonedTimeoutMillis");
PARAMS.add("removeAbandoned");
PARAMS.add("logAbandoned");
PARAMS.add("queryTimeout");
PARAMS.add("transactionQueryTimeout");
PARAMS.add("timeBetweenConnectErrorMillis");
PARAMS.add("connectTimeout");
PARAMS.add("socketTimeout");
}
// @Autowired(required = false)
// private ApplicationContext applicationContext;
// Druid配置对象
private DruidConfig gConfig;
/**
* Druid since 1.2.17 use 'configFromPropeties' to copy config
* Druid < 1.2.17 use 'configFromPropety' to copy config
* 根据Druid的版本选择从配置文件中复制配置信息的方法
*/
private static void fetchMethod() {
Class<DruidDataSource> aClass = DruidDataSource.class;
try {
configMethod = aClass.getMethod("configFromPropeties", Properties.class);
return;
} catch (NoSuchMethodException ignored) {
}
try {
configMethod = aClass.getMethod("configFromPropety", Properties.class);
return;
} catch (NoSuchMethodException ignored) {
}
throw new RuntimeException("Druid does not has 'configFromPropeties' or 'configFromPropety' method!");
}
/**
* 创建Druid数据源。
*
* @param dataSourceProperty 数据源配置信息
* @return 创建的Druid数据源对象
*/
@Override
public DataSource createDataSource(DataSourceProperty dataSourceProperty) {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUsername(dataSourceProperty.getUsername());
dataSource.setPassword(dataSourceProperty.getPassword());
dataSource.setUrl(dataSourceProperty.getUrl());
dataSource.setName(dataSourceProperty.getPoolName());
String driverClassName = dataSourceProperty.getDriverClassName();
if (DsStrUtils.hasText(driverClassName)) {
dataSource.setDriverClassName(driverClassName);
}
DruidConfig config = dataSourceProperty.getDruid();
Properties properties = DruidConfigUtil.mergeConfig(gConfig, config);
// 初始化Druid过滤器
List<Filter> proxyFilters = this.initFilters(dataSourceProperty, properties.getProperty("druid.filters"));
dataSource.setProxyFilters(proxyFilters);
try {
configMethod.invoke(dataSource, properties);
} catch (Exception ignore) {
}
// 设置连接参数
dataSource.setConnectProperties(config.getConnectionProperties());
// 设置Druid内置properties不支持的参数
for (String param : PARAMS) {
DruidConfigUtil.setValue(dataSource, param, gConfig, config);
}
if (Boolean.FALSE.equals(dataSourceProperty.getLazy())) {
try {
dataSource.init();
} catch (SQLException e) {
throw new ErrorCreateDataSourceException("druid create error", e);
}
}
返回创建的Druid数据源对象。
return dataSource;
}
/**
* 初始化Druid过滤器。
*
* @param dataSourceProperty 数据源配置信息
* @param filters 过滤器列表
* @return 初始化后的过滤器列表
*/
private List<Filter> initFilters(DataSourceProperty dataSourceProperty, String filters) {
List<Filter> proxyFilters = new ArrayList<>(2);
if (DsStrUtils.hasText(filters)) {
String[] filterItems = filters.split(",");
for (String filter : filterItems) {
switch (filter) {
case "stat":
// 初始化Druid Stat过滤器
proxyFilters.add(DruidStatConfigUtil.toStatFilter(dataSourceProperty.getDruid().getStat(), gConfig.getStat()));
break;
case "wall":
// 初始化Druid Wall过滤器
WallConfig wallConfig = DruidWallConfigUtil.toWallConfig(dataSourceProperty.getDruid().getWall(), gConfig.getWall());
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig);
proxyFilters.add(wallFilter);
break;
case "slf4j":
// 初始化Druid Slf4j Log过滤器
proxyFilters.add(DruidLogConfigUtil.initFilter(Slf4jLogFilter.class, dataSourceProperty.getDruid().getSlf4j(), gConfig.getSlf4j()));
break;
case "commons-log":
// 初始化Druid Commons Log过滤器
proxyFilters.add(DruidLogConfigUtil.initFilter(CommonsLogFilter.class, dataSourceProperty.getDruid().getCommonsLog(), gConfig.getCommonsLog()));
break;
case "log4j":
// 初始化Druid Log4j过滤器
proxyFilters.add(DruidLogConfigUtil.initFilter(Log4jFilter.class, dataSourceProperty.getDruid().getLog4j(), gConfig.getLog4j()));
break;
case "log4j2":
// 初始化Druid Log4j2过滤器
proxyFilters.add(DruidLogConfigUtil.initFilter(Log4j2Filter.class, dataSourceProperty.getDruid().getLog4j2(), gConfig.getLog4j2()));
break;
default:
log.warn("dynamic-datasource current not support [{}]", filter);
}
}
}
// TODO: 从Spring容器中获取过滤器
// if (this.applicationContext != null) {
// for (String filterId : gConfig.getProxyFilters()) {
// proxyFilters.add(this.applicationContext.getBean(filterId, Filter.class));
// }
// }
return proxyFilters;
}
/**
* 判断是否支持创建该类型的数据源。
*
* @param dataSourceProperty 数据源配置信息
* @return 如果支持创建该类型的数据源则返回true，否则返回false
*/
@Override
public boolean support(DataSourceProperty dataSourceProperty) {
Class<? extends DataSource> type = dataSourceProperty.getType();
return type == null || DdConstants.DRUID_DATASOURCE.equals(type.getName());
}
}
Druid配置项 DruidConfig
dynamic-datasource 多数据源组件，将Druid 的配置项进行了支持，目前支持以下这些
package com.baomidou.dynamic.datasource.creator.druid;
import lombok.Getter;
import lombok.Setter;
import java.util.*;
/**
* Druid参数配置
*
* @author TaoYu
* @since 1.2.0
*/
@Getter
@Setter
public class DruidConfig {
private Integer initialSize;
private Integer maxActive;
private Integer minIdle;
private Integer maxWait;
private Long timeBetweenEvictionRunsMillis;
private Long timeBetweenLogStatsMillis;
private Long keepAliveBetweenTimeMillis;
private Integer statSqlMaxSize;
private Long minEvictableIdleTimeMillis;
private Long maxEvictableIdleTimeMillis;
private String defaultCatalog;
private Boolean defaultAutoCommit;
private Boolean defaultReadOnly;
private Integer defaultTransactionIsolation;
private Boolean testWhileIdle;
private Boolean testOnBorrow;
private Boolean testOnReturn;
private String validationQuery;
private Integer validationQueryTimeout;
private Boolean useGlobalDataSourceStat;
private Boolean asyncInit;
private String filters;
private Boolean clearFiltersEnable;
private Boolean resetStatEnable;
private Integer notFullTimeoutRetryCount;
private Integer maxWaitThreadCount;
private Boolean failFast;
private Long phyTimeoutMillis;
private Long phyMaxUseCount;
private Boolean keepAlive;
private Boolean poolPreparedStatements;
private Boolean initVariants;
private Boolean initGlobalVariants;
private Boolean useUnfairLock;
private Boolean killWhenSocketReadTimeout;
private Properties connectionProperties;
private Integer maxPoolPreparedStatementPerConnectionSize;
private String initConnectionSqls;
private Boolean sharePreparedStatements;
private Integer connectionErrorRetryAttempts;
private Boolean breakAfterAcquireFailure;
private Boolean removeAbandoned;
private Integer removeAbandonedTimeoutMillis;
private Boolean logAbandoned;
private Integer queryTimeout;
private Integer transactionQueryTimeout;
private String publicKey;
private Integer connectTimeout;
private Integer socketTimeout;
private Long timeBetweenConnectErrorMillis;
private Map<String, Object> wall = new HashMap<>();
private Map<String, Object> slf4j = new HashMap<>();
private Map<String, Object> log4j = new HashMap<>();
private Map<String, Object> log4j2 = new HashMap<>();
private Map<String, Object> commonsLog = new HashMap<>();
private Map<String, Object> stat = new HashMap<>();
private List<String> proxyFilters = new ArrayList<>();
}
3.示例项目
3.1. pom
3.1.1. 依赖版本定义
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<ds.version>4.1.2</ds.version>
<mybatis-spring-boot-starter.version>3.0.0</mybatis-spring-boot-starter.version>
<druid.version>1.2.18</druid.version>
<p6spy.version>3.9.1</p6spy.version>
<h2.version>2.2.220</h2.version>
<spring-boot-dependencies.version>2.7.13</spring-boot-dependencies.version>
</properties>
3.1.2. 依赖版本管理
<dependencyManagement>
<dependencies>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>${ds.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot-dependencies.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<dependency>
<groupId>org.springdoc</groupId>
<artifactId>springdoc-openapi-ui</artifactId>
<version>1.7.0</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis-spring-boot-starter.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>${h2.version}</version>
</dependency>
</dependencies>
</dependencyManagement>
3.1.3. pom依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<dependency>
<groupId>org.springdoc</groupId>
<artifactId>springdoc-openapi-ui</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
3.2. 源代码
3.2.1. DruidApplication
import lombok.extern.slf4j.Slf4j;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@Slf4j
@SpringBootApplication
@MapperScan("com.icepip.samples.druid.mapper")
public class DruidApplication {
public static void main(String[] args) {
SpringApplication.run(DruidApplication.class, args);
log.info("open http://localhost:8080/swagger-ui.html n" +
"http://localhost:8080/druid/index.html");
}
}
3.2.2. UserService
import com.icepip.samples.druid.entity.User;
import java.util.List;
public interface UserService {
List<User> selectMasterUsers();
List<User> selectSlaveUsers();
void addUser(User user);
void deleteUserById(Long id);
}
3.2.2. UserServiceImpl
在切换数据源的时候使用注解 @DS("slave")
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.samples.druid.entity.User;
import com.baomidou.samples.druid.mapper.UserMapper;
import com.baomidou.samples.druid.service.UserService;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
private final UserMapper userMapper;
public UserServiceImpl(UserMapper userMapper) {
this.userMapper = userMapper;
}
@Override
public List<User> selectMasterUsers() {
return userMapper.selectUsers(1);
}
@DS("slave")
@Override
public List<User> selectSlaveUsers() {
return userMapper.selectUsers(1);
}
@Override
public void addUser(User user) {
userMapper.addUser(user.getName(), user.getAge());
}
@Override
public void deleteUserById(Long id) {
userMapper.deleteUserById(id);
}
}
3.2.3. UserMapper
import com.baomidou.icepip.druid.entity.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public interface UserMapper {
@Select("select * from t_user where age > #{age}")
List<User> selectUsers(@Param("age") Integer age);
@SuppressWarnings("UnusedReturnValue")
@Insert("insert into t_user (name,age) values (#{name},#{age})")
boolean addUser(@Param("name") String name, @Param("age") Integer age);
@Delete("delete from t_user where id = #{id}")
void deleteUserById(Long id);
}
3.2.3. User
import lombok.Data;
@Data
public class User {
private Integer id;
private String name;
private Integer age;
}
3.2.4. UserDto
import lombok.Data;
@Data
public class UserDto {
private Integer id;
private String name;
private Integer age;
}
3.2.5. UserController
import com.baomidou.icepip.druid.entity.User;
import com.baomidou.icepip.druid.service.UserService;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Random;
@RestController
@AllArgsConstructor
@RequestMapping("/users")
public class UserController {
private static final Random RANDOM = new Random();
private final UserService userService;
@GetMapping("master")
public List<User> masterUsers() {
return userService.selectMasterUsers();
}
@GetMapping("slave")
public List<User> slaveUsers() {
return userService.selectSlaveUsers();
}
@PostMapping
public User addUser() {
User user = new User();
user.setName("测试用户" + RANDOM.nextInt());
user.setAge(RANDOM.nextInt(100));
userService.addUser(user);
return user;
}
@DeleteMapping("{id}")
public String deleteUser(@PathVariable Long id) {
userService.deleteUserById(id);
return "成功删除用户" + id;
}
}
3.2.6. 示例sql
CREATE TABLE IF NOT EXISTS t_user
(
id BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NULL DEFAULT NULL,
age INT(11) NULL DEFAULT NULL,
PRIMARY KEY (id)
);
4.参考文档
1.多数据源 https://baomidou.com/pages/a61e1b/#dynamic-datasource