spring boot 多数据源动态切换

心已赠人 2021-12-14 15:23 353阅读 0赞

# 介绍 #

## 版本说明 ##

spring boot版本：2.0.2.RELEASE  
数据源：druid  
数据库：mysql  
ORM映射：MyBatis，JPA（Hibernate）

## 需求说明 ##

因为需要在同一个项目中连接多个数据库，而且后期可能还回继续新增新的数据库连接。所以除了实现多数据源之外，还需要实现多个数据源之间动态的进行切换。多数据源的话，声明出来就好了，动态切换就需要用到AbstractRoutingDataSource跟AOP切面来实现。在示例中只实现数据源切换不实现AOP。关于AOP的部分结合自己的业务来写就好了。

# POM #

<dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-aop</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.10</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.1</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-devtools</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-lang3</artifactId>
                <version>3.0</version>
            </dependency>
            <dependency>
                <groupId>javax.activation</groupId>
                <artifactId>activation</artifactId>
                <version>1.1.1</version>
            </dependency>
            <dependency>
                <groupId>net.sf.json-lib</groupId>
                <artifactId>json-lib</artifactId>
                <version>2.4</version>
                <classifier>jdk15</classifier>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
        </dependencies>

# 配置文件 #

# 数据源相关配置
    ds:
      # 数据库1
      basic:
        datasource:
          url: jdbc:mysql://192.168.31.203:3306/ktwlsoft_framework_basic_pl?useUnicode=true&characterEncoding=utf8&useSSL=false&useAffectedRows=true&serverTimezone=GMT
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
      # 数据库2
      base:
        datasource:
          url: jdbc:mysql://192.168.31.203:3306/tensquare_base?useUnicode=true&characterEncoding=utf8&useSSL=false&useAffectedRows=true&serverTimezone=GMT
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
      # 数据库3
      article:
        datasource:
          url: jdbc:mysql://192.168.31.203:3306/tensquare_article?useUnicode=true&characterEncoding=utf8&useSSL=false&useAffectedRows=true&serverTimezone=GMT
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
      # 数据库4
      friend:
        datasource:
          url: jdbc:mysql://192.168.31.203:3306/tensquare_friend?useUnicode=true&characterEncoding=utf8&useSSL=false&useAffectedRows=true&serverTimezone=GMT
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
      # 连接池配置
      datasource:
        initial_size: 20
        min_idle: 20
        max_active: 200
        max_wait: 60000
        time_between_eviction_runs_millis: 60000
        min_evictable_idle_time_millis: 300000
        test_while_idle: true
        test_on_borrow: false
        test_on_return: false
        pool_prepared_statements: true
        max_pool_prepared_statement_per_connection_size: 20
    
    
    # JPA 相关配置
    spring:
      jpa:
        database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
        show-sql: true
    # mybatis 打印sql
    logging:
      level:
        com.hzw.mapper : debug

# 数据源的代码实现 #

## 自定义数据源切换类 ##

/**
     * 自定义数据源切换类
     */
    public class DatabaseContextHolder {
    
        private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
    
        public static void setDBKey(String dataSourceKey) {
            contextHolder.set(dataSourceKey);
        }
    
        public static String getDBKey() {
            return contextHolder.get();
        }
    
        public static void clearDBKey() {
            contextHolder.remove();
        }
    }

## 动态数据源 ##

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    import java.util.HashMap;
    import java.util.Map;
    
    /**
     * 动态数据源
     *
     */
    public class DynamicDataSource extends AbstractRoutingDataSource {
        private static DynamicDataSource instance;
        private static byte[] lock=new byte[0];
        private static Map<Object,Object> dataSourceMap=new HashMap<Object, Object>();
    
        @Override
        public void setTargetDataSources(Map<Object, Object> targetDataSources) {
            super.setTargetDataSources(targetDataSources);
            dataSourceMap.putAll(targetDataSources);
            // 必须添加该句，否则新添加数据源无法识别到
            super.afterPropertiesSet();
        }
    
        public Map<Object, Object> getDataSourceMap() {
            return dataSourceMap;
        }
    
        @Override
        protected Object determineCurrentLookupKey() {
            String dbKey = DatabaseContextHolder.getDBKey();
            return dbKey;
        }
    
        private DynamicDataSource() {}
    
        public static synchronized DynamicDataSource getInstance(){
            if(instance==null){
                synchronized (lock){
                    if(instance==null){
                        instance=new DynamicDataSource();
                    }
                }
            }
            return instance;
        }
    
    }

## 数据源KEY ##

/**
     * 数据库数据源名称
     */
    public class DbUtil {
    
        /**数据库basic**/
        public static final String DB_BASIC = "ds_basic";
        /**数据库base**/
        public static final String DB_BASE = "ds_base";
        /**数据库article**/
        public static final String DB_ARTICLE = "ds_article";
        /**数据库friend**/
        public static final String DB_FRIEND = "ds_friend";
    
    }

## 数据源配置 ##

import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import com.hzw.util.DbUtil;
    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.beans.factory.annotation.Value;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.Map;
    
    /**
     * 数据源配置
     */
    @Configuration
    // 扫描 Mapper 接口并容器管理
    @MapperScan(basePackages = DatasourceConfig.PACKAGE, sqlSessionFactoryRef = "sqlSessionFactory")
    public class DatasourceConfig {
        // mapper扫描
        static final String PACKAGE = "com.hzw.mapper";
        static final String MAPPER_LOCATION = "classpath:mapper/*.xml";
    
        @Value("${ds.basic.datasource.url}")
        private String urlBasic;
        @Value("${ds.basic.datasource.username}")
        private String userBasic;
        @Value("${ds.basic.datasource.password}")
        private String passwordBasic;
        @Value("${ds.basic.datasource.driver-class-name}")
        private String driverClassBasic;
    
        @Value("${ds.base.datasource.url}")
        private String urlBase;
        @Value("${ds.base.datasource.username}")
        private String userBase;
        @Value("${ds.base.datasource.password}")
        private String passwordBase;
        @Value("${ds.base.datasource.driver-class-name}")
        private String driverClassBase;
    
        @Value("${ds.article.datasource.url}")
        private String urlArticle;
        @Value("${ds.article.datasource.username}")
        private String userArticle;
        @Value("${ds.article.datasource.password}")
        private String passwordArticle;
        @Value("${ds.article.datasource.driver-class-name}")
        private String driverClassArticle;
    
        @Value("${ds.friend.datasource.url}")
        private String urlFriend;
        @Value("${ds.friend.datasource.username}")
        private String userFriend;
        @Value("${ds.friend.datasource.password}")
        private String passwordFriend;
        @Value("${ds.friend.datasource.driver-class-name}")
        private String driverClassFriend;
    
        @Value("${ds.datasource.max_active}")
        private Integer maxActive;
        @Value("${ds.datasource.min_idle}")
        private Integer minIdle;
        @Value("${ds.datasource.initial_size}")
        private Integer initialSize;
        @Value("${ds.datasource.max_wait}")
        private Long maxWait;
        @Value("${ds.datasource.time_between_eviction_runs_millis}")
        private Long timeBetweenEvictionRunsMillis;
        @Value("${ds.datasource.min_evictable_idle_time_millis}")
        private Long minEvictableIdleTimeMillis;
        @Value("${ds.datasource.test_while_idle}")
        private Boolean testWhileIdle;
        @Value("${ds.datasource.test_while_idle}")
        private Boolean testOnBorrow;
        @Value("${ds.datasource.test_on_borrow}")
        private Boolean testOnReturn;
    
        @Bean(name = "dynamicDataSource")
        @Primary
        public DynamicDataSource dynamicDataSource() {
            DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();
    
            // basic数据源
            DruidDataSource dataSourceBasic = initDataSource(driverClassBasic,urlBasic,userBasic,passwordBasic);
            // base数据源
            DruidDataSource dataSourceBase = initDataSource(driverClassBase,urlBase,userBase,passwordBase);
            // article数据源
            DruidDataSource dataSourceArticle = initDataSource(driverClassArticle,urlArticle,userArticle,passwordArticle);
            // friend数据源
            DruidDataSource dataSourceFriend = initDataSource(driverClassFriend,urlFriend,userFriend,passwordFriend);
    
            Map<Object,Object> map = new HashMap<>();
            map.put(DbUtil.DB_BASIC, dataSourceBasic);
            map.put(DbUtil.DB_BASE, dataSourceBase);
            map.put(DbUtil.DB_ARTICLE, dataSourceArticle);
            map.put(DbUtil.DB_FRIEND, dataSourceFriend);
    
            dynamicDataSource.setTargetDataSources(map);
            // 默认数据源
            dynamicDataSource.setDefaultTargetDataSource(dataSourceBasic);
            return dynamicDataSource;
        }
    
        /**
         * 初始数据源
         * @param driver    驱动
         * @param url       数据库连接
         * @param username  用户名
         * @param password  密码
         * @return
         */
        public DruidDataSource initDataSource(String driver,String url,String username,String password){
            //jdbc配置
            DruidDataSource rdataSource = new DruidDataSource();
            rdataSource.setDriverClassName(driver);
            rdataSource.setUrl(url);
            rdataSource.setUsername(username);
            rdataSource.setPassword(password);
            setPool(rdataSource);
            return rdataSource;
        }
    
        /**
         * 连接池配置
         * @param rdataSource
         */
        private void setPool(DruidDataSource rdataSource){
            //连接池配置
            rdataSource.setMaxActive(maxActive);
            rdataSource.setMinIdle(minIdle);
            rdataSource.setInitialSize(initialSize);
            rdataSource.setMaxWait(maxWait);
            rdataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            rdataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            rdataSource.setTestWhileIdle(testWhileIdle);
            rdataSource.setTestOnBorrow(testOnBorrow);
            rdataSource.setTestOnReturn(testOnReturn);
            rdataSource.setValidationQuery("SELECT 'x'");
            rdataSource.setPoolPreparedStatements(true);
            rdataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
            try {
                rdataSource.setFilters("stat");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        @Bean(name = "transactionManager")
        @Primary
        public DataSourceTransactionManager transactionManager() {
            return new DataSourceTransactionManager(dynamicDataSource());
        }
    
        @Bean(name = "sqlSessionFactory")
        @Primary
        public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(dynamicDataSource);
            sessionFactory.setTypeAliasesPackage("com.hzw.model");
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(DatasourceConfig.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    
    
        @Bean
        public ServletRegistrationBean druidServlet() {
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
            servletRegistrationBean.setServlet(new StatViewServlet());
            servletRegistrationBean.addUrlMappings("/druid/*");
            Map<String, String> initParameters = new HashMap<String, String>();
            // 用户名
            initParameters.put("loginUsername", "admin");
            // 密码
            initParameters.put("loginPassword", "admin");
            // 禁用HTML页面上的“Reset All”功能
            initParameters.put("resetEnable", "false");
            // IP白名单 (没有配置或者为空，则允许所有访问)
            initParameters.put("allow", "");
            servletRegistrationBean.setInitParameters(initParameters);
            return servletRegistrationBean;
        }
    
        @Bean
        public FilterRegistrationBean filterRegistrationBean() {
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
            filterRegistrationBean.setFilter(new WebStatFilter());
            filterRegistrationBean.addUrlPatterns("/*");
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }
    
    }

如此这般，多数据源，动态切换的功能就有了，那么下面来验证是否成功。

# 验证 #

## 启动类 ##

import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    /**
     * 启动类
     */
    @SpringBootApplication
    public class Application{
        public static void main(String[] args) {
            SpringApplication.run(Application.class, args);
        }
    }

下面可以写几个mybatis的例子跟JPA(Hibernate)的例子，保证我们的mybatis跟JPA都是可用的，数据源都是可以切换成功的。这里就不具体写了，只把在service层怎么来动态切换的代码贴出来

### article数据库 ###

@Override
        public List<Channel> findChannel() {
            // 指定数据源
            DatabaseContextHolder.setDBKey(DbUtil.DB_ARTICLE);
            return channelRepository.findAll();
        }

### base数据库 ###

@Override
        public List<City> findCity() {
            // 指定数据源
            DatabaseContextHolder.setDBKey(DbUtil.DB_BASE);
            return cityMapper.findCity();
        }

### basic数据库 ###

@Override
        public List<Users> findUser() {
            // 指定数据源
            DatabaseContextHolder.setDBKey(DbUtil.DB_BASIC);
            return usersMapper.findUser();
        }

这里是在代码中手动指定当前方法使用的数据库，我们可以根据自己的业务来进行改造

## 场景一 ##

需求是不同的用户，根据区域或其他属性来进行分库，某些用户访问某个数据库。  
这种场景，只需要在用户登录的时候就把用户能访问的数据库存储起来，在AOP中获取当前用户能访问的数据库并调用DatabaseContextHolder.setDBKey()方法来设置。AOP的切面可以定义到service或者是action层。

## 场景二 ##

需求是指定的某个AOP切面能访问某个数据库。  
这种场景就更简单了，只需要在切面中去设置就行了

# 结语 #

我们的场景是根据不同的用户来进行数据库的动态切换，本来想的是可以在线上新增数据库连接并且能够切换。目前只实现了在项目中把数据源全部配置出来，然后动态切换，后面在慢慢实现动态新增的事情吧。

## 参考链接 ##

[参考链接1][1]  
[参考链接2][2]

## 源码地址 ##

[码云——源代码地址][Link 1]

[1]: https://blog.csdn.net/liuxiao723846/article/details/80463842
[2]: https://www.cnblogs.com/caichaoqi/p/9216865.html
[Link 1]: https://gitee.com/gzsjd/springBootData