springboot项目中使用动态数据源

springboot项目中使用动态数据源

需求：

动态修改数据源，可通过ulr参数决定使用哪个数据源

按照项目上需求：已有一个项目是针对某省的业务创建的，目前业务成熟，有其他省份的项目进来，功能和业务相同，需要对不同省份的业务数据分库管理，这样一来不同省份使用多个库，项目就需要使用动态数据源。

说明：使用阿里的druid连接池

代码：

添加druid连接池依赖

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.3</version>
</dependency>

配置文件：（多数据源暂时未配置到配置文件，目前是写到代码里，后面可以改）

server.port=8088
mybatis.mapper-locations=classpath:mapping/*.xml

# 使用druid数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.jdbc.Driver

创建DataSourceContextHolder类用于存数据源的key，因为所有数据源都存在一个map容器中

public class DataSourceContextHolder {
    private static ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    // 设置数据源名
    public static void setDB(String db) {
        contextHolder.set(db);
    }

    //获取数据源名
    public static  String getDB(){
        return contextHolder.get();
    }
    //清空数据源
    public static  void clearDB(){
        contextHolder.remove();
    }

}

数据源配置：创建数据源bean

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.example.dynamicdatasource.dao", sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {

    @Bean(name = "dynamicDataSource")
    public Ds2DynamicDataSource dataSource() {
        return Ds2DynamicDataSource.getInstance();
    }


    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource) {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        try {
            Resource[] resources = new PathMatchingResourcePatternResolver()
                    .getResources("classpath:mapping/*.xml");
            sqlSessionFactoryBean.setMapperLocations(resources);
            return sqlSessionFactoryBean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

动态数据源继承AbstractRoutingDataSource类：实现2个方法：

setTargetDataSources(Map<Object, Object> targetDataSources)

determineCurrentLookupKey()

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import java.util.HashMap;
import java.util.Map;

/**
 * @author zj
 * @version 1.0
 * @description 动态数据源
 * @create 2019/6/11 18:00
 **/
@PropertySource({"classpath:application.properties"})
public class Ds2DynamicDataSource extends AbstractRoutingDataSource {
    private static Ds2DynamicDataSource instance;
    private static byte[] lock = new byte[0];
    private static Map<Object, Object> dataSourceMap = new HashMap<Object, Object>();

    // 数据源配置
    {
        DruidDataSource wdataSource = new DruidDataSource();
        wdataSource.setDriverClassName("com.mysql.jdbc.Driver");
        wdataSource.setUrl("jdbc:mysql://127.0.0.1:3306/miaosha?useUnicode=true&characterEncoding=utf8");
        wdataSource.setUsername("root");
        wdataSource.setPassword("123456");
        wdataSource.setMaxActive(5);
        wdataSource.setMinIdle(5);
        wdataSource.setInitialSize(5);
        dataSourceMap.put("miaosha", wdataSource);

        DruidDataSource wdataSource1 = new DruidDataSource();
        wdataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        wdataSource1.setUrl("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8");
        wdataSource1.setUsername("root");
        wdataSource1.setPassword("123456");
        wdataSource1.setMaxActive(5);
        wdataSource1.setMinIdle(5);
        wdataSource1.setInitialSize(5);
        dataSourceMap.put("test", wdataSource1);
    }

    public Ds2DynamicDataSource() {
        this.setTargetDataSources(dataSourceMap);
    }


    public static synchronized Ds2DynamicDataSource getInstance() {
        if (instance == null) {
            synchronized (lock) {
                if (instance == null) {
                    instance = new Ds2DynamicDataSource();
                }
            }
        }
        return instance;
    }

    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
        dataSourceMap.putAll(targetDataSources);
        super.afterPropertiesSet();// 必须添加该句，否则新添加数据源无法识别到
    }
    
    @Override
    protected Object determineCurrentLookupKey() {
        String dbKey = DataSourceContextHolder.getDB();
        return dbKey;
    }

测试：

编写userService.query（id)

通过DataSourceContextHolder.setDB("miaosha"); 去切换数据源

@RequestMapping("/query")
    @ResponseBody
    public String query(Integer id, Integer key) {
        if (key == 1){
            DataSourceContextHolder.setDB("miaosha");
        }else {
            DataSourceContextHolder.setDB("test");
        }
        UserDO userDO = userService.query(id);
        System.out.println(userDO.toString());
        return "ok";
    }

结果：

http://localhost:8088/api/cxy/query?id=22&key=1

输出结果为：

http://localhost:8088/api/cxy/query?id=22&key=2

输出结果为：

结果：切换数据源后，通过id为1，查询不同数据源的数据 

---

demo示例（需要在test及miaosha两个mysql数据库中动态切换数据源）：

一、 创建mysql数据库test、miaosha

二、 2个库中都存在user表

DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '""',
  `gender` tinyint(4) NOT NULL COMMENT '1代表男性 2代表女性',
  `age` int(11) NOT NULL,
  `telphone` varchar(255) NOT NULL,
  `register_mode` varchar(255) NOT NULL COMMENT '//byphone,bywechat,byqq',
  `third_party_id` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `telphone_unique_index` (`telphone`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

三、java代码：

https://github.com/GitHub42096/dynamicdatasource

 

---

Spring 动态数据源切换

从bean中拿到TransactionAwareDataSourceProxy，修改其数据源

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.Map;

/**
 * @author zj
 * @version 1.0
 * @description TODO
 * @create 2019/7/4 11:11
 **/
@Component
public class Test implements ApplicationContextAware {
    private static ApplicationContext applicationContext;
    private static Map<String, Object> dataSourceMap = new HashMap<String, Object>();
    static int flag = 1;

    static {
        DruidDataSource wdataSource = new DruidDataSource();
        wdataSource.setDriverClassName("com.mysql.jdbc.Driver");
        wdataSource.setUrl("jdbc:mysql://127.0.0.1:3306/miaosha?useUnicode=true&characterEncoding=utf8");
        wdataSource.setUsername("root");
        wdataSource.setPassword("123456");
        wdataSource.setMaxActive(5);
        wdataSource.setMinIdle(5);
        wdataSource.setInitialSize(5);
        dataSourceMap.put("1", wdataSource);

        DruidDataSource wdataSource1 = new DruidDataSource();
        wdataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        wdataSource1.setUrl("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8");
        wdataSource1.setUsername("root");
        wdataSource1.setPassword("123456");
        wdataSource1.setMaxActive(5);
        wdataSource1.setMinIdle(5);
        wdataSource1.setInitialSize(5);
        dataSourceMap.put("2", wdataSource1);
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
       this.applicationContext = applicationContext;
    }

    public void switchDB(){
        TransactionAwareDataSourceProxy dataSourceProxy = (TransactionAwareDataSourceProxy) applicationContext.getBean("dataSourceProxy");
        DruidDataSource dataSource;
        if (flag % 2 == 0){
            dataSource = (DruidDataSource) dataSourceMap.get("1");// 采用数据源1
        }else {
            dataSource = (DruidDataSource) dataSourceMap.get("2"); // 采用数据源2
        }
        flag ++;
        dataSourceProxy.setTargetDataSource(dataSource);
    }
}