1. Scenario

In the production business, some tasks perform long-time query operations. When the real-time requirements are not high, we hope to separate these query SQLs and query them from the database to reduce the pressure of the application on the main database.

One solution is to configure multiple data sources in the configuration file, and then obtain data sources and mapper-related scan configurations through configuration classes. Different data sources are configured with different mapper scan locations, and then inject which data source is needed. A mapper interface is enough, this method is relatively simple. The feature is to distinguish data sources by mapper scan location.

The second solution is to configure a default data source, then define multiple other data sources, and use aop to form annotated selection data sources. The core of the implementation of this scheme is the inheritance of the AbstractRoutingDataSource class. This is the focus of this article.

2. Principle

The core logic of the dynamic switching of multiple data sources of AbstractRoutingDataSource is: when the program is running, dynamically weave the data source data source into the program through AbstractRoutingDataSource, and flexibly switch the data source.
The dynamic switching of multiple data sources based on AbstractRoutingDataSource can realize the separation of read and write. The logic is as follows:

/**
     * Retrieve the current target DataSource. Determines the
     * {@link #determineCurrentLookupKey() current lookup key}, performs
     * a lookup in the {@link #setTargetDataSources targetDataSources} map,
     * falls back to the specified
     * {@link #setDefaultTargetDataSource default target DataSource} if necessary.
     * @see #determineCurrentLookupKey()
     */
    protected DataSource determineTargetDataSource() {
        Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
        Object lookupKey = determineCurrentLookupKey();
        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 + "]");
        }
        return dataSource;
    }/**
     * Determine the current lookup key. This will typically be
     * implemented to check a thread-bound transaction context.
     * <p>Allows for arbitrary keys. The returned key needs
     * to match the stored lookup key type, as resolved by the
     * {@link #resolveSpecifiedLookupKey} method.
     */
    @Nullable
    protected abstract Object determineCurrentLookupKey();

Specify the data source to be switched by implementing the abstract method determineCurrentLookupKey

3. Code example

The main dependencies in the example 

com.alibaba.druid;tk.mybatis

Define a class to associate with the data source. Use TheadLocal to save the flag (key) of which data source each thread selects

@Slf4jpublic class DynamicDataSourceContextHolder {
 
 
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
    public static List<String> dataSourceIds = new ArrayList<String>();
 
    public static void setDataSourceType(String dataSourceType) {
        log.info("Set the current data source to {}", dataSourceType);
        contextHolder.set(dataSourceType);
    }
 
    public static String getDataSourceType() {
        return contextHolder.get() ;
    }
 
    public static void clearDataSourceType() {
        contextHolder.remove();
    }
 
    public static boolean containsDataSource(String dataSourceId){
        log.info("list = {},dataId={}", JSON.toJSON(dataSourceIds),dataSourceId);
        return dataSourceIds.contains(dataSourceId);
    }}

inherit

AbstractRoutingDataSource

public class DynamicDataSource extends AbstractRoutingDataSource {
 
    @Override
    protected Object determineCurrentLookupKey() {
 
        return DynamicDataSourceContextHolder.getDataSourceType();
    }}

Configure the master database master and slave database slave (omitted). Data source configuration can be simplified

@[email protected](value = {"com.server.dal.dao"})@ConditionalOnProperty(name = "java.druid.datasource.master.url")public class JavaDruidDataSourceConfiguration {
 
    private static final Logger logger = LoggerFactory.getLogger(JavaDruidDataSourceConfiguration.class);
 
    @Resource
    private JavaDruidDataSourceProperties druidDataSourceProperties;
    @Primary
    @Bean(name = "masterDataSource", initMethod = "init", destroyMethod = "close")
    @ConditionalOnMissingBean(name = "masterDataSource")
    public DruidDataSource javaReadDruidDataSource() {
 
        DruidDataSource result = new DruidDataSource();
 
        try {// result.setName(druidDataSourceProperties.getName());
            result.setUrl(druidDataSourceProperties.getUrl());
            result.setUsername(druidDataSourceProperties.getUsername());
            result.setPassword(druidDataSourceProperties.getPassword());
            result.setConnectionProperties(
                    "config.decrypt=false;config.decrypt.key=" + druidDataSourceProperties.getPwdPublicKey());
            result.setFilters("config");
            result.setMaxActive(druidDataSourceProperties.getMaxActive());
            result.setInitialSize(druidDataSourceProperties.getInitialSize());
            result.setMaxWait(druidDataSourceProperties.getMaxWait());
            result.setMinIdle(druidDataSourceProperties.getMinIdle());
            result.setTimeBetweenEvictionRunsMillis(druidDataSourceProperties.getTimeBetweenEvictionRunsMillis());
            result.setMinEvictableIdleTimeMillis(druidDataSourceProperties.getMinEvictableIdleTimeMillis());
            result.setValidationQuery(druidDataSourceProperties.getValidationQuery());
            result.setTestWhileIdle(druidDataSourceProperties.isTestWhileIdle());
            result.setTestOnBorrow(druidDataSourceProperties.isTestOnBorrow());
            result.setTestOnReturn(druidDataSourceProperties.isTestOnReturn());
            result.setPoolPreparedStatements(druidDataSourceProperties.isPoolPreparedStatements());
            result.setMaxOpenPreparedStatements(druidDataSourceProperties.getMaxOpenPreparedStatements());
 
            if (druidDataSourceProperties.isEnableMonitor()) {
                StatFilter filter = new StatFilter();
                filter.setLogSlowSql(druidDataSourceProperties.isLogSlowSql());
                filter.setMergeSql(druidDataSourceProperties.isMergeSql());
                filter.setSlowSqlMillis(druidDataSourceProperties.getSlowSqlMillis());
                List<Filter> list = new ArrayList<>();
                list.add(filter);
                result.setProxyFilters(list);
            }
 
        } catch (Exception e) {
 
            logger.error("Data source load failed:", e);
 
        } finally {
            result.close();
        }
 
 
        return result;
    }}

Note the bean name of the master-slave database

Configure DynamicDataSource 

• targetDataSources stores the kv pair of the data source

• defaultTargetDataSource stores the default data source

Configure Transaction Manager and SqlSessionFactoryBean

@Configurationpublic class DynamicDataSourceConfig {    private static final String MAPPER_LOCATION = "classpath*:sqlmap/dao/*Mapper.xml";     @Bean(name = "dynamicDataSource")    public DynamicDataSource dynamicDataSource(@Qualifier("masterDataSource") DruidDataSource masterDataSource,                                               @Qualifier("slaveDataSource") DruidDataSource slaveDataSource) {        Map<Object, Object> targetDataSource = new HashMap<>();        DynamicDataSourceContextHolder.dataSourceIds.add("masterDataSource");        targetDataSource.put("masterDataSource", masterDataSource);        DynamicDataSourceContextHolder.dataSourceIds.add("slaveDataSource");        targetDataSource.put("slaveDataSource", slaveDataSource);        DynamicDataSource dataSource = new DynamicDataSource();        dataSource.setTargetDataSources(targetDataSource);        dataSource.setDefaultTargetDataSource(masterDataSource);        return dataSource;    }     @Primary    @Bean(name = "javaTransactionManager")    @ConditionalOnMissingBean(name = "javaTransactionManager")    public DataSourceTransactionManager transactionManager(@Qualifier("dynamicDataSource") DynamicDataSource druidDataSource) {        return new DataSourceTransactionManager(druidDataSource);    }     @Bean(name = "sqlSessionFactoryBean")    public SqlSessionFactoryBean myGetSqlSessionFactory(@Qualifier("dynamicDataSource") DynamicDataSource dataSource) {        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();        try {            sqlSessionFactoryBean.setMapperLocations(resolver.getResources(MAPPER_LOCATION));        } catch (IOException e) {            e.printStackTrace();        }        sqlSessionFactoryBean.setDataSource(dataSource);        return sqlSessionFactoryBean;    }}

Define an annotation to specify the data source

@Target({ ElementType.METHOD, ElementType.TYPE })@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface TargetDataSource {
    String value();}

Aspect business logic. Note that order is specified to ensure that it is executed before the transaction is opened.

@Aspect@Slf4j@Order(-1)@Componentpublic class DataSourceAop {
 
    @Before("@annotation(targetDataSource)")
    public void changeDataSource(JoinPoint point, TargetDataSource targetDataSource) {
        String dsId = targetDataSource.value();
        if (!DynamicDataSourceContextHolder.containsDataSource(dsId)) {
            log.error("Data source [{}] does not exist, use default data source > {}" + targetDataSource.value() + point.getSignature());
        } else {
            log.info("UseDataSource : {} > {}" + targetDataSource.value() + point.getSignature());
            DynamicDataSourceContextHolder.setDataSourceType(targetDataSource.value());
 
        }
    }
 
    @After("@annotation(targetDataSource)")
    public void restoreDataSource(JoinPoint point, TargetDataSource targetDataSource) {
        log.info("RevertDataSource : {} > {}"+targetDataSource.value()+point.getSignature());
        DynamicDataSourceContextHolder.clearDataSourceType();
    }}

The above omits pom.xml and application.yml

Example of use

    @Resource
    private ShopBillDOMapper shopBillDOMapper;
 // use default data source
    public ShopBillBO queryTestData(Integer id){
 
        return shopBillDOMapper.getByShopBillId(id);
    }
 //Switch to the specified data source
    @TargetDataSource("slaveDataSource")
    public ShopBill queryTestData2(Integer id){
        return shopBillDOMapper.getByShopBillId(id);
    }

If it returns a different result then success!