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>

• 2. 排除原生Druid的快速配置类。
     注意：v3.3.3及以上版本不用排除了。
     方法1. 利用注解排除
     @SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)

@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: truelogin-username: adminlogin-password: 123456dynamic:lazy: true# 配置全局druid参数，请按需配置druid:initial-size: 5max-active: 8min-idle: 3max-wait: 1000validation-query: 'select 1'datasource:master:username: sapassword: "123456"url: jdbc:h2:mem:test;MODE=MySQLdriver-class-name: org.h2.Driverdruid:socketTimeout: 1111slave_1:username: sapassword: "123456"url: jdbc:h2:mem:test;MODE=MySQLdriver-class-name: org.h2.Driverdruid:initial-size: 6slave_2:username: sapassword: "123456"url: jdbc:h2:mem:test;MODE=MySQLdriver-class-name: org.h2.Driver

---

druid 的原生配置在dynamic-datasource都是实现了的，大家可以按需选择

spring:datasource:druid:stat-view-servlet:enabled: trueloginUsername: adminloginPassword: 123456dynamic: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: rootpassword: 123456driver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://xx.xx.xx.xx:3306/dynamic?characterEncoding=utf8&useSSL=falsedruid: # 以下是独立参数，每个库可以重新设置initial-size: 20validation-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数据源对象*/@Overridepublic 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*/@Overridepublic 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;}@Overridepublic List<User> selectMasterUsers() {return userMapper.selectUsers(1);}@DS("slave")@Overridepublic List<User> selectSlaveUsers() {return userMapper.selectUsers(1);}@Overridepublic void addUser(User user) {userMapper.addUser(user.getName(), user.getAge());}@Overridepublic 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();}@PostMappingpublic 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