SpringBoot配置多数据源Mysql+Sqlite

​ 配置了一下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仓库

参考：

https://my.oschina.net/u/3681868/blog/1813011