配置了一下druid的多数据源配置，尝试了很多方法，Spring boot关于对Mysql和Sqlite多数据源的配置，记录下来:
涉及技术点：
Springboot + Druid + Mysql +Sqlite
一、引入Jar包:
<!--Spring Boot依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.1.8.RELEASE</version>
</dependency>
<!--MYSQL连接依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
<!--阿里数据源连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!--sqlite依赖-->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.28.0</version>
</dependency>
<!--aspects依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
二、配置参数:
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
#druid相关配置
druid:
one:
name: ds_mysql
driver-class-name: com.mysql.cj.jdbc.Driver
#基本属性
url: @jdbc.url@
username: @jdbc.username@
password: @jdbc.password@
test-while-idle: false
two:
name: ds_sqlite
driver-class-name: org.sqlite.JDBC
#基本属性
url: jdbc:sqlite:db/test.db
username: test
password: test
test-while-idle: false
use-global-data-source-stat: true
#监控统计拦截的filters
filters: stat
#配置初始化大小/最小/最大
initial-size: 2
min-idle: 2
max-active: 20
#获取连接等待超时时间
max-wait: 60000
#间隔多久进行一次检测，检测需要关闭的空闲连接
time-between-eviction-runs-millis: 60000
#一个连接在池中最小生存的时间
min-evictable-idle-time-millis: 300000
validation-query: SELECT 'x' FROM DUAL
# mysql需要设置校验
# 指明是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个.注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串
test-while-idle: false
# 指明是否在归还到池中前进行检验 注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串
test-on-borrow: false
# 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除. 注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串
test-on-return: false
#打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
pool-prepared-statements: false
(参数配置,可参考: https://gitee.com/wenshao/druid/tree/master/druid-spring-boot-starter)
三、编写配置文件:
1、定义数据源名称常量 :
package com.meng.scaffold.config.datasource;
/**
* @Description： 数据源名称
* @author： MengW9
* @Date： 2019-11-28
* @Time： 10:26
*/
public interface DataSourceNames {
String ONE = "ONE";
String TWO = "TWO";
}
2、创建动态数据源:
package com.meng.scaffold.config.datasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* @program: scaffold
* @description: 动态数据源
* @author: MengW9
* @create: 2019-11-28 10:26
**/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
/**
* 配置DataSource, defaultTargetDataSource为主数据库
*/
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return getDataSource();
}
public static void setDataSource(String dataSource) {
contextHolder.set(dataSource);
}
public static String getDataSource() {
return contextHolder.get();
}
public static void clearDataSource() {
contextHolder.remove();
}
}
3、动态数据源配置:
package com.meng.scaffold.config.datasource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
/**
* @program: scaffold
* @description: 多数据源配置
* @author: MengW9
* @create: 2019-11-28 10:16
**/
@Configuration
public class DynamicDataSourceConfig {
/**
* 创建 DataSource Bean
*/
@Bean("oneDataSource")
@ConfigurationProperties("spring.datasource.druid.one")
public DataSource oneDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean("twoDataSource")
@ConfigurationProperties("spring.datasource.druid.two")
public DataSource twoDataSource() {
return DruidDataSourceBuilder.create().build();
}
/**
* 如果还有数据源,在这继续添加 DataSource Bean
*/
@Bean
@Primary
public DynamicDataSource dataSource(DataSource oneDataSource, DataSource twoDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>(2);
targetDataSources.put(DataSourceNames.ONE, oneDataSource);
targetDataSources.put(DataSourceNames.TWO, twoDataSource);
// 还有数据源,在targetDataSources中继续添加
System.out.println("DataSources:" + targetDataSources);
return new DynamicDataSource(oneDataSource, targetDataSources);
}
}
4、定义动态数据源注解:
package com.meng.scaffold.config.datasource;
import java.lang.annotation.*;
/**
* @description: 多数据源注解
* @author: MengW9
* @create: 2019-11-28 10:31
**/
@Documented
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
String value() default DataSourceNames.ONE;
}
5、设置数据源 AOP 代理:
package com.meng.scaffold.config.datasource;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* @description: 数据源AOP切面处理
* @author: MengW9
* @create: 2019-11-28 10:33
**/
@Aspect
@Component
public class DataSourceAspect implements Ordered {
protected Logger logger = LoggerFactory.getLogger(getClass());
/**
* 切点: 所有配置 DataSource 注解的方法
*/
@Pointcut("@annotation(com.meng.scaffold.config.datasource.DataSource)")
public void dataSourcePointCut() {
}
@Around("dataSourcePointCut()")
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DataSource ds = method.getAnnotation(DataSource.class);
// 通过判断 DataSource 中的值来判断当前方法应用哪个数据源
DynamicDataSource.setDataSource(ds.value());
System.out.println("当前数据源: " + ds.value());
logger.debug("set datasource is " + ds.value());
try {
return point.proceed();
} finally {
DynamicDataSource.clearDataSource();
logger.debug("clean datasource");
}
}
@Override
public int getOrder() {
return 1;
}
}
四、修改启动文件:
package com.meng.scaffold;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.Import;
import com.gy.fast.common.config.data.DynamicDataSourceConfig;
**
* 动态数据源配置,需要将自有的配置依赖(DynamicDataSourceConfig),将原有的依赖去除(DataSourceAutoConfiguration)
* exclude = DataSourceAutoConfiguration.class
* @author Meng
*/
@Import({DynamicDataSourceConfig.class})
@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
public class DeviceApplication {
public static void main(String[] args) {
SpringApplication.run(DeviceApplication.class, args);
}
}
五、配置完成, 进行测试:
测试接口编写：
package com.meng.scaffold.service;
import com.meng.scaffold.config.datasource.DataSource;
import com.meng.scaffold.config.datasource.DataSourceNames;
import com.meng.scaffold.dao.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @description: 测试多数据源
* @author: MengW9
* @create: 2019-11-28 10:39
**/
@Service
public class DataSourceTestService {
@Autowired
private UserService userService;
public User test1(Long userId) {
return userService.selectById(userId);
}
/**
* @Description： 多数据注解必须放在接口实现类的上面
* @Param： [userId]
* @Author： MengW9
*/
@DataSource(DataSourceNames.TWO)
public User test2(Long userId) {
return userService.selectById(userId);
}
}
编写测试类：
package com.meng.scaffold;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.meng.scaffold.dao.User;
@RunWith(SpringRunner.class)
@SpringBootTest
public class DynamicDataSourceTest {
@Autowired
private DataSourceTestService dataSourceTestService;
@Test
public void test(){
// 数据源ONE
SysUser user1 = dataSourceTestService.test1(1L);
System.out.println(ToStringBuilder.reflectionToString(user1));
// 数据源TWO
SysUser user2 = dataSourceTestService.test2(1L);
System.out.println(ToStringBuilder.reflectionToString(user2));
// 数据源ONE
SysUser user3 = dataSourceTestService.test1(1L);
System.out.println(ToStringBuilder.reflectionToString(user3));
}
}
代码地址：Git仓库
参考：