spring boot 多数据源动态切换

最新推荐文章于 2024-03-27 14:47:11 发布

BUG胡汉三

最新推荐文章于 2024-03-27 14:47:11 发布

阅读量689

点赞数 1

分类专栏： Spring boot 文章标签：多数据源 spring boot 数据源动态切换

本文链接：https://blog.csdn.net/hzw2312/article/details/94575708

版权

Spring boot 专栏收录该内容

1 篇文章 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
参考链接2

源码地址

码云——源代码地址

BUG胡汉三

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
spring boot 多数据源动态切换

介绍版本说明spring boot版本：2.0.2.RELEASE数据源：druid数据库：mysqlORM映射：MyBatis，JPA（Hibernate）需求说明因为需要在同一个项目中连接多个数据库，而且后期可能还回继续新增新的数据库连接。所以除了实现多数据源之外，还需要实现多个数据源之间动态的进行切换。多数据源的话，声明出来就好了，动态切换就需要用到AbstractRoutin...
复制链接

扫一扫