Spring-动态切换数据源

Spring动态切换数据源相关笔记

Spring动态切换数据源实例

Spring中AOP方式实现多数据源切换
https://www.jianshu.com/p/ddebf4ae57c1

SpringBoot 自定义+动态切换数据源
https://blog.csdn.net/DuShiWoDeCuo/article/details/78189043

spring 动态切换数据源多数据库
https://blog.csdn.net/laojiaqi/article/details/78964862

基于spring的aop实现多数据源动态切换
https://lanjingling.github.io/2016/02/15/spring-aop-dynamicdatasource/

application.properties配置文件

application-prod.properties
这里我们配置了3个数据源，一个master读写库，一个slave只读库，master和slave是mysql主从关系，还有一个tidb

# mydb master
mariadb.mydb.master.url=jdbc:mysql://localhost/mydb?useAffectedRows=true
mariadb.mydb.master.username={cipher}xxxxxxx
mariadb.mydb.master.password={cipher}xxxxxxx
mariadb.mydb.master.maximum-pool-size=50
mariadb.mydb.master.readonly=false
mariadb.mydb.master.connection-timeout=30000
mariadb.mydb.master.idle-timeout=60000
mariadb.mydb.master.max-lifetime=1800000

# mydb slave
mariadb.mydb.slave.url=jdbc:mysql://localhost/mydb?useAffectedRows=true
mariadb.mydb.slave.username={cipher}xxxxxx
mariadb.mydb.slave.password={cipher}xxxxxx
mariadb.mydb.slave.maximum-pool-size=50
mariadb.mydb.slave.readonly=true
mariadb.mydb.slave.connection-timeout=30000
mariadb.mydb.slave.idle-timeout=60000
mariadb.mydb.slave.max-lifetime=1800000

# tidb
tidb.mydb.url=jdbc:mysql://localhost:4000/mydb?useAffectedRows=true&useSSL=true
tidb.mydb.username={cipher}xxxxxx
tidb.mydb.password={cipher}xxxxxx
tidb.mydb.maximum-pool-size=50
tidb.mydb.readonly=false
tidb.mydb.connection-timeout=30000
tidb.mydb.idle-timeout=60000
tidb.mydb.max-lifetime=1800000

数据源Bean配置

这一步可以使用 xml 配置bean，也可以使用 JavaConfig 配置类，下面使用 JavaConfig 进行配置：

package com.masikkk.common.config;

import com.google.common.collect.Maps;
import com.masikkk.common.config.DataSourceContextHolder;
import com.masikkk.common.config.DynamicDataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.session.AutoMappingBehavior;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Value;
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.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
public class MariadbConfig {
    // ***************** master 数据源配置 *****************
    @Value("${mariadb.mydb.master.url}")
    private String masterURL;

    @Value("${mariadb.mydb.master.username}")
    private String masterUsername;

    @Value("${mariadb.mydb.master.password}")
    private String masterPassword;

    @Value("${mariadb.mydb.master.readonly:false}")
    private boolean masterReadonly;

    // 连接数据库的超时时间，单位毫秒
    @Value("${mariadb.mydb.master.connection-timeout:30000}")
    private long masterConnectionTimeout;

    // 最大空闲时间，非核心线程的空闲时间如果超过此阈值，则被线程池销毁掉, 单位毫秒
    @Value("${mariadb.mydb.master.idle-timeout:60000}")
    private long masterIdleTimeout;

    // 最大生存时间，核心线程如果存活的时间超过此阈值，会被销毁, 单位毫秒
    @Value("${mariadb.mydb.master.max-lifetime:1800000}")
    private long masterMaxLifetime;

    // 最大线程池容量
    @Value("${mariadb.mydb.master.maximum-pool-size:100}")
    private int masterMaximumPoolSize;

    @Value("${mariadb.mydb.master.drive:com.mysql.jdbc.Driver}")
    private String masterDrive;

    // ***************** slave 数据源配置 *****************
    @Value("${mariadb.mydb.slave.url}")
    private String slaveURL;

    @Value("${mariadb.mydb.slave.username}")
    private String slaveUsername;

    @Value("${mariadb.mydb.slave.password}")
    private String slavePassword;

    @Value("${mariadb.mydb.slave.readonly:false}")
    private boolean slaveReadonly;

    @Value("${mariadb.mydb.slave.connection-timeout:30000}")
    private long slaveConnectionTimeout;

    @Value("${mariadb.mydb.slave.idle-timeout:60000}")
    private long slaveIdleTimeout;

    @Value("${mariadb.mydb.slave.max-lifetime:1800000}")
    private long slaveMaxLifetime;

    @Value("${mariadb.mydb.slave.maximum-pool-size:100}")
    private int slaveMaximumPoolSize;

    @Value("${mariadb.mydb.slave.drive:com.mysql.jdbc.Driver}")
    private String slaveDrive;

    // ***************** tidb 数据源配置 *****************
    @Value("${tidb.mydb.url}")
    private String tidbURL;

    @Value("${tidb.mydb.username}")
    private String tidbUsername;

    @Value("${tidb.mydb.password}")
    private String tidbPassword;

    @Value("${tidb.mydb.readonly:false}")
    private boolean tidbReadonly;

    @Value("${tidb.mydb.connection-timeout:30000}")
    private long tidbConnectionTimeout;

    @Value("${tidb.mydb.idle-timeout:60000}")
    private long tidbIdleTimeout;

    @Value("${tidb.mydb.max-lifetime:1800000}")
    private long tidbMaxLifetime;

    @Value("${tidb.mydb.maximum-pool-size:100}")
    private int tidbMaximumPoolSize;

    @Value("${tidb.mydb.drive:com.mysql.jdbc.Driver}")
    private String tidbDrive;

    // master Hikari 数据源
    @Bean(name = "masterDataSource")
    public DataSource masterDataSource() {
        return new HikariDataSource(
                createHikariConfig(
                        masterURL,
                        masterUsername,
                        masterPassword,
                        masterReadonly,
                        masterConnectionTimeout,
                        masterIdleTimeout,
                        masterMaxLifetime,
                        masterMaximumPoolSize,
                        masterDrive
                )
        );
    }

    // slave Hikari 数据源
    @Bean(name = "slaveDataSource")
    public DataSource slaveDataSource() {
        return new HikariDataSource(
                createHikariConfig(
                        slaveURL,
                        slaveUsername,
                        slavePassword,
                        slaveReadonly,
                        slaveConnectionTimeout,
                        slaveIdleTimeout,
                        slaveMaxLifetime,
                        slaveMaximumPoolSize,
                        slaveDrive
                )
        );
    }

    // tidb Hikari 数据源
    @Bean(name = "tidbDataSource")
    public DataSource tidbDataSource() {
        return new HikariDataSource(
                createHikariConfig(
                        tidbURL,
                        tidbUsername,
                        tidbPassword,
                        tidbReadonly,
                        tidbConnectionTimeout,
                        tidbIdleTimeout,
                        tidbMaxLifetime,
                        tidbMaximumPoolSize,
                        tidbDrive
                )
        );
    }

    private HikariConfig createHikariConfig(String url, String username, String password, boolean readonly, long connectionTimeout, long idleTimeout,
            long maxLifeTime, int maxPoolSize, String driver) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setReadOnly(readonly);
        config.setConnectionTimeout(connectionTimeout);
        config.setIdleTimeout(idleTimeout);
        config.setMaxLifetime(maxLifeTime);
        config.setMaximumPoolSize(maxPoolSize);
        config.setDriverClassName(driver);
        return config;
    }

    // dataSource Bean
    @Bean(name = "dataSource")
    public DataSource dataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();

        // 默认数据源
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource());

        // 目标数据源map
        Map<Object, Object> dataSources = Maps.newHashMap();
        dataSources.put(DataSourceContextHolder.DS_MASTER, masterDataSource());
        dataSources.put(DataSourceContextHolder.DS_SLAVE, slaveDataSource());
        dataSources.put(DataSourceContextHolder.DS_TIDB, tidbDataSource());
        dynamicDataSource.setTargetDataSources(dataSources);
        return dynamicDataSource;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();
        transactionManager.setDataSource(dataSource());
        return transactionManager;
    }

    @Primary
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // 全局映射器启用缓存
        configuration.setCacheEnabled(true);
        // 查询时，关闭关联对象即时加载以提高性能
        configuration.setLazyLoadingEnabled(true);
        // 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指 定)，不会加载关联表的所有字段，以提高性能
        configuration.setAggressiveLazyLoading(false);
        // 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果
        configuration.setMultipleResultSetsEnabled(true);
        // 允许使用列标签代替列名
        configuration.setUseColumnLabel(true);
        // 给予被嵌套的resultMap以字段-属性的映射支持
        configuration.setAutoMappingBehavior(AutoMappingBehavior.FULL);
        // 对于批量更新操作缓存SQL以提高性能
        configuration.setDefaultExecutorType(ExecutorType.SIMPLE);
        // 数据库超过25000秒仍未响应则超时
        configuration.setDefaultStatementTimeout(25000);
        configuration.setMapUnderscoreToCamelCase(true);
        // 允许使用自定义的主键值(比如由程序生成的UUID 32位编码作为键值)，数据表的PK生成策略将被覆盖
        //configuration.setUseGeneratedKeys(true);

        Properties properties = new Properties();
        properties.setProperty("dialect", "mysql");

        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        sqlSessionFactoryBean.setConfiguration(configuration);
        sqlSessionFactoryBean.setConfigurationProperties(properties);
        //sqlSessionFactoryBean.setConfigLocation(resolver.getResource("classpath:mybatis-config.xml"));
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
}

这个config文件中，生成了一个名为 dataSource 的bean，是 DynamicDataSource 类的一个实例，
注意 DynamicDataSource 类型的 dataSource bean 中，targetDataSources 属性的key类型必须和 DataSourceContextHolder 中的key类型一致，否则无法从map中找到对应数据源。

DynamicDataSource类

建立动态数据源类，注意，这个类必须继承 AbstractRoutingDataSource, 且实现方法 determineCurrentLookupKey，该方法返回一个Object，一般是返回字符串：

package com.masikkk.common.config;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSource.class);

    @Override
    protected Object determineCurrentLookupKey() {
        logger.debug("The current mariadb datasource is {}", DataSourceContextHolder.getDataSource());
        return DataSourceContextHolder.getDataSource();
    }
}

AbstractRoutingDataSource 抽象类部分源码如下：

package org.springframework.jdbc.datasource.lookup;

public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
  private Map<Object, DataSource> resolvedDataSources;

  protected DataSource determineTargetDataSource() {
      Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
      Object lookupKey = this.determineCurrentLookupKey();
      DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);
      if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
          dataSource = this.resolvedDefaultDataSource;
      }

      if (dataSource == null) {
          throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
      } else {
          return dataSource;
      }
  }

  protected abstract Object determineCurrentLookupKey();
}

determineTargetDataSource() 方法决定当前使用的数据源，此方法会根据 determineCurrentLookupKey() 决定当前数据源的key，然后从 resolvedDataSources 这个map中取出数据源并返回，我们只需要实现 determineCurrentLookupKey() 方法即可实现自己的数据源选择。
而 resolvedDataSources 是我们在配置 DynamicDataSource bean的时候set进去的map

DataSourceContextHolder

保存数据源名称常量的上下文环境类，记录当前线程的当前数据源

package com.masikkk.common.config;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DataSourceContextHolder {
    private static final Logger logger = LoggerFactory.getLogger(DataSourceContextHolder.class);

    // 线程隔离
    private static final ThreadLocal<String> holder = new ThreadLocal();

    public static final String DS_MASTER = "ds-master";
    public static final String DS_SLAVE = "ds-slave";
    public static final String DS_TIDB = "ds-tidb";

    // 设置当前线程数据源
    public static void setDataSource(String db) {
        logger.debug("Mariadb switch to {}", db);
        holder.set(db);
    }

    // 获取当前线程数据源
    public static String getDataSource() {
        return holder.get();
    }

    // 清除数据源
    public static void clearDataSource() {
        holder.remove();
    }
}

数据源切换注解的切面

匹配 @Master @Slave @Tidb 注解，在被注解方法执行之前切换数据源，在被注解方法执行之后切换回默认数据源。

package com.masikkk.common.aspect;

import com.masikkk.common.annotation.Master;
import com.masikkk.common.annotation.Slave;
import com.masikkk.common.annotation.Tidb;
import com.masikkk.common.config.DataSourceContextHolder;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class DynamicDataSourceAspect {
    private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);

    @Before("@annotation(com.masikkk.common.annotation.Master)")
    public void beforeMaster(JoinPoint point) {
        if (((MethodSignature) point.getSignature()).getMethod().isAnnotationPresent(Master.class)) {
            DataSourceContextHolder.setDataSource(DataSourceContextHolder.DS_MASTER);
        } else {
            logger.error("Bug, method should be annotated by @Master!");
        }
    }

    @After("@annotation(com.masikkk.common.annotation.Master)")
    public void afterMaster() {
        DataSourceContextHolder.clearDataSource();
    }

    @Before("@annotation(com.masikkk.common.annotation.Tidb)")
    public void beforeTidbMaster(JoinPoint point) {
        if (((MethodSignature) point.getSignature()).getMethod().isAnnotationPresent(Tidb.class)) {
            DataSourceContextHolder.setDataSource(DataSourceContextHolder.DS_TIDB);
        } else {
            logger.error("Bug, method should be annotated by @Tidb!");
        }
    }

    @After("@annotation(com.masikkk.common.annotation.Tidb)")
    public void afterTidbMaster() {
        DataSourceContextHolder.clearDataSource();
    }

    @Before("@annotation(com.masikkk.common.annotation.Slave)")
    public void beforeSlave(JoinPoint point) {
        if (((MethodSignature) point.getSignature()).getMethod().isAnnotationPresent(Slave.class)) {
            DataSourceContextHolder.setDataSource(DataSourceContextHolder.DS_SLAVE);
        } else {
            logger.error("Bug, method should be annotated by @Slave!");
        }
    }

    @After("@annotation(com.masikkk.common.annotation.Slave)")
    public void afterSlave() {
        DataSourceContextHolder.clearDataSource();
    }
}

数据源切换注解

@Master 注解

package com.masikkk.common.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface Master {
}

@Slave 注解

package com.masikkk.common.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface Slave {
}

@Tidb 注解

package com.masikkk.common.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface Tidb {
}

DAO层切换数据源

@Slave 注解切换到从库

package com.masikkk.common.dao;

import com.masikkk.common.annotation.Slave;
import com.masikkk.common.mapper.MyMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class MyDao {

    @Autowired
    private MyMapper myMapper;

    @Slave
    public String queryClassNameById(long id) {
        return myMapper.queryClassNameById(id);
    }
}

对应mapper

package com.masikkk.common.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface MyRelationMapper {
    @Select("SELECT class_name FROM class WHERE id = #{id}")
    String queryClassNameById(@Param("id") long id);
}

Spring多数据源

多数据源

不同mapper使用不同的SqlSessionFactory，对应不同的数据源

Spring Boot + Mybatis多数据源和动态数据源配置
https://blog.csdn.net/neosmith/article/details/61202084

动态数据源

禁用SpringBoot数据源自动配置

首先要将spring boot自带的DataSourceAutoConfiguration禁掉，因为它会读取application.properties文件的spring.datasource.*属性并自动配置单数据源。在@SpringBootApplication注解中添加exclude属性即可：

@SpringBootApplication(exclude = {
        DataSourceAutoConfiguration.class
})
public class SpringInitializrApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringInitializrApplication.class, args);
    }
}

SpringBoot多数据源及MyBatis配置详解
https://my.oschina.net/angerbaby/blog/715281

当前位置 : 首页 » 文章分类 : 开发 » Spring-动态切换数据源

Spring-动态切换数据源