Druid連接池 Spring Boot 配置多數(shù)據(jù)源【最簡單的方式】

0.前言

看了網(wǎng)上好多關(guān)于Spring Boot 配置數(shù)據(jù)庫 多數(shù)據(jù)源的文章，其實不用那么多重復(fù)造輪子，目前已經(jīng)有了一個特別成熟的開源組件dynamic-datasource ，已經(jīng)支持各種各樣的場景，來滿足你多數(shù)據(jù)源的需求，我大概整理了一下，希望對初學(xué)者有用。

1.基礎(chǔ)介紹

本文我們使用 dynamic-datasource 多數(shù)據(jù)源組件實現(xiàn)在springboot 項目中快速集成多數(shù)據(jù)源。

• 支持 數(shù)據(jù)源分組 ，適用于多種場景 純粹多庫 讀寫分離 一主多從 混合模式。
• 支持數(shù)據(jù)庫敏感配置信息 加密 ENC()。
• 支持每個數(shù)據(jù)庫獨立初始化表結(jié)構(gòu)schema和數(shù)據(jù)庫database。
• 支持無數(shù)據(jù)源啟動，支持懶加載數(shù)據(jù)源（需要的時候再創(chuàng)建連接）。
• 支持 自定義注解 ，需繼承DS(3.2.0+)。
• 提供并簡化對Druid，HikariCp，BeeCp，Dbcp2的快速集成。
• 提供對Mybatis-Plus，Quartz，ShardingJdbc，P6sy，Jndi等組件的集成方案。
• 提供 自定義數(shù)據(jù)源來源 方案（如全從數(shù)據(jù)庫加載）。
• 提供項目啟動后 動態(tài)增加移除數(shù)據(jù)源 方案。
• 提供Mybatis環(huán)境下的 純讀寫分離 方案。
• 提供使用 spel動態(tài)參數(shù) 解析數(shù)據(jù)源方案。內(nèi)置spel，session，header，支持自定義。
• 支持 多層數(shù)據(jù)源嵌套切換 。（ServiceA >>> ServiceB >>> ServiceC）。
• 提供 **基于seata的分布式事務(wù)方案。
• 提供 本地多數(shù)據(jù)源事務(wù)方案。

2.步驟

2.1. 引入依賴

• 1.引入依賴，具體版本參考你當(dāng)前的項目依賴管理中添加dependencyManagement中統(tǒng)一管理

		<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數(shù)據(jù)庫作為示例數(shù)據(jù)庫，mysql和其他數(shù)據(jù)庫請按正確的jdbc url
配置

spring:datasource:druid:stat-view-servlet:enabled: truelogin-username: adminlogin-password: 123456dynamic:lazy: true# 配置全局druid參數(shù)，請按需配置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都是實現(xiàn)了的，大家可以按需選擇

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: # 以下是獨立參數(shù)，每個庫可以重新設(shè)置initial-size: 20validation-query: select 1 FROM DUAL #比如oracle就需要重新設(shè)置這個public-key: #（非全局參數(shù)）設(shè)置即表示啟用加密,底層會自動幫你配置相關(guān)的連接參數(shù)和filter，推薦使用本項目自帶的加密方法。
#           ......# 生成 publickey 和密碼，推薦使用本項目自帶的加密方法。
# java -cp druid-1.1.10.jar com.alibaba.druid.filter.config.ConfigTools youpassword

2.3. 核心源碼

多數(shù)據(jù)源組件核心源碼解釋

Druid數(shù)據(jù)源創(chuàng)建器

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數(shù)據(jù)源創(chuàng)建器* * 該類是一個實現(xiàn)了DataSourceCreator接口的類，提供了創(chuàng)建Druid數(shù)據(jù)源的方法。* * @since 2020/1/21*/
@Slf4j
@NoArgsConstructor
@AllArgsConstructor
public class DruidDataSourceCreator implements DataSourceCreator {// 配置參數(shù)列表private static final Set<String> PARAMS = new HashSet<>();// 從配置文件中復(fù)制配置信息的方法private static Method configMethod = null;static {fetchMethod();}static {// 設(shè)置Druid支持的連接參數(shù)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* 根據(jù)Druid的版本選擇從配置文件中復(fù)制配置信息的方法*/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!");}/*** 創(chuàng)建Druid數(shù)據(jù)源。* * @param dataSourceProperty 數(shù)據(jù)源配置信息* @return 創(chuàng)建的Druid數(shù)據(jù)源對象*/@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) {}// 設(shè)置連接參數(shù)dataSource.setConnectProperties(config.getConnectionProperties());// 設(shè)置Druid內(nèi)置properties不支持的參數(shù)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);}}返回創(chuàng)建的Druid數(shù)據(jù)源對象。return dataSource;}/*** 初始化Druid過濾器。* * @param dataSourceProperty 數(shù)據(jù)源配置信息* @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;}/*** 判斷是否支持創(chuàng)建該類型的數(shù)據(jù)源。* * @param dataSourceProperty 數(shù)據(jù)源配置信息* @return 如果支持創(chuàng)建該類型的數(shù)據(jù)源則返回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 多數(shù)據(jù)源組件，將Druid 的配置項進行了支持，目前支持以下這些

package com.baomidou.dynamic.datasource.creator.druid;import lombok.Getter;
import lombok.Setter;import java.util.*;/*** Druid參數(shù)配置** @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

在切換數(shù)據(jù)源的時候使用注解 @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.多數(shù)據(jù)源 https://baomidou.com/pages/a61e1b/#dynamic-datasource