Using Spring AbstractRoutingDataSource to manage multiple data sources

1. Principle of multi data switching: the core of multi data source dynamic switching is that the bottom layer of spring provides AbstractRoutingDataSource class for data source routing. We mainly inherit this class and implement the methods in it.

 The main implementation method is: determineCurrentLookupKey(), which only needs to return the name of a database.

 Our core is to have a class to manage the thread pool of the data source, which is the core processing class of the dynamic data source. Another is to use aop to switch data sources before methods.

2. Add multi data source configuration: take the dev environment as an example, add four commonly used databases: base, erp, order, finance.

 

Multi data source configuration - Dev spring: AOP: Auto: Auto: true proxy - target class: true datassource: Type: com.alibaba.druid.pool.druiddatassource Druid: the database database database: URL: JDBC: MySQL: mysql://192.168.3.228: 3306 / dxh_base? Useunicode = true & characharacterencoding = utf8 & autoreconnect = true & zerodatetimebehavior = converttonull & transformebitbitisboolean = true username: Username: dxh: dxh: dxh: dxh: dxh: 3306.3.228: 3306 / dxh_base? Useunicode = true & characharacharacterencoding = utf8 & autoreconnect = true & autoreconconnect = true & zerodatetimebehavior = converttonull & transftransformeditbitbitisboolean = true = true = true username = password: DX h2017$$ driver-class-name: com.mysql.jdbc.Driver initialSize: 5 minIdle: 5 max-active: 200 max-wait: 10000 test-on-borrow: false test-on-return: false time-between-eviction-runs-millis: 15000 default-auto-commit: true min-evictable-idle-time-millis: 30000 use-global-data-source-stat: true filter: stat: enabled: false db-type: mysql log-slow-sql: t rue slow-sql-millis: 2 slf4j: data-source-log-enabled: true data-source-logger-name: DRUID statement-executable-sql-log-enable: true statement-logger-name: DRUID

  db-erp:
    url: jdbc:mysql://192.168.3.228:3306/dxh_erp?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true
    username: dxh
    password: Dxh2017$$
    driver-class-name: com.mysql.jdbc.Driver
    initialSize: 5
    minIdle: 5
    max-active: 200
    max-wait: 10000
    test-on-borrow: false
    test-on-return: false
    time-between-eviction-runs-millis: 15000
    default-auto-commit: true
    min-evictable-idle-time-millis: 30000
    use-global-data-source-stat: true
    filter:
      stat:
        enabled: false
        db-type: mysql
        log-slow-sql: true
        slow-sql-millis: 2
      slf4j:
        data-source-log-enabled: true
        data-source-logger-name: DRUID
        statement-executable-sql-log-enable: true
        statement-logger-name: DRUID

  db-order:
    url: jdbc:mysql://192.168.3.228:3306/dxh_order?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true
    username: dxh
    password: Dxh2017$$
    driver-class-name: com.mysql.jdbc.Driver
    initialSize: 5
    minIdle: 5
    max-active: 200
    max-wait: 10000
    test-on-borrow: false
    test-on-return: false
    time-between-eviction-runs-millis: 15000
    default-auto-commit: true
    min-evictable-idle-time-millis: 30000
    use-global-data-source-stat: true
    filter:
      stat:
        enabled: false
        db-type: mysql
        log-slow-sql: true
        slow-sql-millis: 2
      slf4j:
        data-source-log-enabled: true
        data-source-logger-name: DRUID
        statement-executable-sql-log-enable: true
        statement-logger-name: DRUID

  db-finance:
    url: jdbc:mysql://192.168.3.228:3306/dxh_finance?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true
    username: dxh
    password: Dxh2017$$
    driver-class-name: com.mysql.jdbc.Driver
    initialSize: 5
    minIdle: 5
    max-active: 200
    max-wait: 10000
    test-on-borrow: false
    test-on-return: false
    time-between-eviction-runs-millis: 15000
    default-auto-commit: true
    min-evictable-idle-time-millis: 30000
    use-global-data-source-stat: true
    filter:
      stat:
        enabled: false
        db-type: mysql
        log-slow-sql: true
        slow-sql-millis: 2
      slf4j:
        data-source-log-enabled: true
        data-source-logger-name: DRUID
        statement-executable-sql-log-enable: true
        statement-logger-name: DRUID

3. Create data source enumeration class

4. Create related notes (optional)

5. Write aop to cut data source

aop code @Component @Aspect @Order(-100) @Slf4j public class DataSourceSwitchAspect {

@Pointcut("execution(* com.dxh.multi.source.base.service..*.*(..))")
private void dbBaseAspect() {}

@Pointcut("execution(* com.dxh.multi.source.erp.service..*.*(..))")
private void dbErpAspect() {}

@Pointcut("execution(* com.dxh.multi.source.order.service..*.*(..))")
private void dbOrderAspect() {}

@Pointcut("execution(* com.dxh.multi.source.finance.service..*.*(..))")
private void dbFinanceAspect() {}

@Pointcut("execution(* com.baomidou.mybatisplus.service..*.*(..)) ")
private void dbDefault() {}


@Before( "dbDefault()" )
public void dbDefault(JoinPoint joinPoint) {

    String packageName = joinPoint.getThis().getClass().getPackage().getName();

    switch (packageName) {
        case "com.dxh.multi.source.base.service.impl":
            dbBase(joinPoint);
            break;
        case "com.dxh.multi.source.erp.service.impl":
            dbErp(joinPoint);
            break;
        case "com.dxh.multi.source.order.service.impl":
            dbOrder(joinPoint);
            break;
        case "com.dxh.multi.source.finance.service.impl":
            dbFinance(joinPoint);
            break;
        default:
            dbBase(joinPoint);
    }

    log.debug("Common method switch data source");
}


@Before( "dbBaseAspect()" )
public void dbBase(JoinPoint joinPoint) {
    log.debug("Switch to base data source...");
    setDataSource(joinPoint, DBTypeEnum.dbBase);
}

@Before("dbErpAspect()" )
public void dbErp (JoinPoint joinPoint) {
    log.debug("Switch to erp data source...");
    setDataSource(joinPoint,DBTypeEnum.dbErp);
}

@Before("dbOrderAspect()" )
public void dbOrder (JoinPoint joinPoint) {
    log.debug("Switch to order data source...");
    setDataSource(joinPoint,DBTypeEnum.dbOrder);
}

@Before("dbFinanceAspect()" )
public void dbFinance (JoinPoint joinPoint) {
    log.debug("Switch to finance data source...");
    setDataSource(joinPoint,DBTypeEnum.dbFinance);
}

/**
 * Add annotation method. If there is annotation priority annotation, it will be configured according to the transmitted data source
 * @param joinPoint
 * @param dbTypeEnum
 */
private void setDataSource(JoinPoint joinPoint, DBTypeEnum dbTypeEnum) {
    MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
    DataSource dataSourceSwitch = methodSignature.getMethod().getAnnotation(DataSource.class);
    if (Objects.isNull(dataSourceSwitch)) {
        DbContextHolder.setDbType(dbTypeEnum);
    }else{
        log.debug("Switch data source by annotation,target:"+dataSourceSwitch.value());
        DbContextHolder.setDbType(dataSourceSwitch.value());
    }
}

}

6. Kill the noisy DataSourceAutoConfiguration and rewrite MybatisConfig. Chen Duxiu, please sit down and give our SqlSessionFactory a chance

mybatis config code @ enableconfigurationproperties ({mybatisplusproperties. Class}) @ autoconfigurebeefore ({datasourceautoconfiguration. Class}) @ configuration @ mapperscan (basepackages = {"com. Dxh. Multi. Source. * *. Mapper"}, markerinterface = mymapper. Class) public class mybatisplusconfig{

@Autowired
private MybatisPlusProperties properties;

/**
 * mybatis-plus Paging plug-ins < br >
 * Document: http://mp.baomidou.com < br >
 */
@Bean
public PaginationInterceptor paginationInterceptor() {

    return new PaginationInterceptor();
}

@Bean(name = "db-base")
@ConfigurationProperties(prefix = "spring.datasource.druid.db-base")
public DataSource dbBase() {

    return DruidDataSourceBuilder.create().build();
}

@Bean(name = "db-erp")
@ConfigurationProperties(prefix = "spring.datasource.druid.db-erp")
public DataSource dbErp() {

    return DruidDataSourceBuilder.create().build();
}

@Bean(name = "db-order")
@ConfigurationProperties(prefix = "spring.datasource.druid.db-order")
public DataSource dbOrder() {

    return DruidDataSourceBuilder.create().build();
}

@Bean(name = "db-finance")
@ConfigurationProperties(prefix = "spring.datasource.druid.db-finance")
public DataSource dbFinance() {

    return DruidDataSourceBuilder.create().build();
}


/**
 * Dynamic data source configuration
 *
 * @return
 */
@Bean
@Primary
public DataSource multipleDataSource(@Qualifier("db-base") DataSource dbBase,
                                     @Qualifier("db-erp") DataSource dbErp,
                                     @Qualifier("db-order") DataSource dbOrder,
                                     @Qualifier("db-finance") DataSource dbFinance) {
    DynamicDataSource dynamicDataSource = new DynamicDataSource();
    Map<Object, Object> targetDataSources = new HashMap<>();
    targetDataSources.put(DBTypeEnum.dbBase.getValue(), dbBase);
    targetDataSources.put(DBTypeEnum.dbErp.getValue(), dbErp);
    targetDataSources.put(DBTypeEnum.dbOrder.getValue(), dbOrder);
    targetDataSources.put(DBTypeEnum.dbFinance.getValue(), dbFinance);

    dynamicDataSource.setTargetDataSources(targetDataSources);
    dynamicDataSource.setDefaultTargetDataSource(dbBase);
    return dynamicDataSource;
}

@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
    sqlSessionFactory.setDataSource(dataSource);
    sqlSessionFactory.setVfs(SpringBootVFS.class);

    MybatisConfiguration configuration = new MybatisConfiguration();
    configuration.setJdbcTypeForNull(JdbcType.NULL);
    configuration.setMapUnderscoreToCamelCase(true);
    configuration.setCacheEnabled(false);
    sqlSessionFactory.setConfiguration(configuration);
    sqlSessionFactory.setPlugins(new Interceptor[]{
            paginationInterceptor()
    });

    if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {
        sqlSessionFactory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());
    }

    if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) {
        sqlSessionFactory.setMapperLocations(this.properties.resolveMapperLocations());
    }

    sqlSessionFactory.setGlobalConfig(globalConfiguration());
    return sqlSessionFactory.getObject();
}

@Bean
public GlobalConfiguration globalConfiguration() {
    GlobalConfiguration conf = new GlobalConfiguration(new LogicSqlInjector());
    conf.setLogicDeleteValue("-1");
    conf.setLogicNotDeleteValue("1");
    conf.setIdType(0);
    conf.setDbColumnUnderline(true);
    conf.setRefresh(true);
    return conf;
}

}7. Fill in all kinds of crud code and unit tests (better about code generator & usage with code generator)

8. Other precautions 1) the default data source is as follows

 2) the @ datasource annotation takes precedence over the above default configuration

 3) in addition to annotations, you can use DbContextHolder.setDbType(DBTypeEnum.dbErp) to switch data sources.

 4) after the data source is switched, the transaction is rolled back

Tags: Programming Druid MySQL SQL JDBC

Posted on Sun, 10 Nov 2019 09:02:12 -0800 by gauravupadhyaya