spring+mybatis? Multi data source configuration

Master and slave data source configuration

Data source configuration of sub database and sub table

Using dynamic data source and AOP to realize sub database

1. Configure multiple data sources in the application.yml file

spring: 
  http: 
    encoding:
      charset: UTF-8 
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    default-property-inclusion: NON_NULL      
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    # Multi data source configuration
    uts1: 
      url: jdbc:mysql://192.168.99.100:3306/uts1?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 111111
    uts2: 
      url: jdbc:mysql://192.168.99.100:3306/uts2?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 111111
    uts3: 
      url: jdbc:mysql://192.168.99.100:3306/uts3?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 111111
    uts4: 
      url: jdbc:mysql://192.168.99.100:3306/uts4?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 111111      

2. Configure multiple datasource bean s

@Bean("uts1")
@ConfigurationProperties(prefix = "spring.datasource.uts1")
@Primary
public DataSource uts1DataSource() throws SQLException {
    DruidDataSource ds = new DruidDataSource();
    logger.info(" druid datasource uts1DataSource : {} ", ds);
    return ds;
}

@Bean("uts2")
@ConfigurationProperties(prefix = "spring.datasource.uts2")
public DataSource uts2DataSource() throws SQLException {
    DruidDataSource ds = new DruidDataSource();
    logger.info(" druid datasource uts2DataSource : {} ", ds);
    return ds;
}

@Bean("uts3")
@ConfigurationProperties(prefix = "spring.datasource.uts3")
public DataSource uts3DataSource() throws SQLException {
    DruidDataSource ds = new DruidDataSource();
    logger.info(" druid datasource uts3DataSource : {} ", ds);
    return ds;
}

@Bean("uts4")
@ConfigurationProperties(prefix = "spring.datasource.uts4")
public DataSource uts4DataSource() throws SQLException {
    DruidDataSource ds = new DruidDataSource();
    logger.info(" druid datasource uts4DataSource : {} ", ds);
    return ds;
}      

3. Define data source enumeration class

public class DataBaseContextHolder {
    public enum DataBaseType {
        uts1("uts1"), uts2("uts2"), uts3("uts3"), uts4("uts4");
        private String code;

        DataBaseType(String dbName) {
            code = dbName;
        }

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            this.code = code;
        }

    }

    private static final ThreadLocal<DataBaseType> contextHolder = new ThreadLocal<>();

    public static Object getDataBaseType() {
        return contextHolder.get() == null ? DataBaseType.uts1 : contextHolder.get();
    }

    public static void setDataBaseType(DataBaseType dataBaseType) {
        if (dataBaseType == null)
            throw new NullPointerException("dataBaseType Cannot be empty.");
        contextHolder.set(dataBaseType);
    }

    public static void clearContextHolder() {
        contextHolder.remove();
    }
}

4. Define DynamicDataSource

This class inherits AbstractRoutingDataSource and overrides determineCurrentLookupKey

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataBaseContextHolder.getDataBaseType();
    }
}

5. Configure mybatis sqlSessionFactory


@Configuration
@EnableTransactionManagement
public class MybatisConfiguration {
    @Resource(name = "uts1")
    private DataSource duts1DataSource;
    @Resource(name = "uts2")
    private DataSource duts2DataSource;
    @Resource(name = "uts3")
    private DataSource duts3DataSource;
    @Resource(name = "uts4")
    private DataSource duts4DataSource;

    @Bean
    public DynamicDataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataBaseContextHolder.DataBaseType.uts1, duts1DataSource);
        targetDataSources.put(DataBaseContextHolder.DataBaseType.uts2, duts2DataSource);
        targetDataSources.put(DataBaseContextHolder.DataBaseType.uts3, duts3DataSource);
        targetDataSources.put(DataBaseContextHolder.DataBaseType.uts4, duts4DataSource);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(duts1DataSource);
        return dynamicDataSource;
    }

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean(DynamicDataSource dynamicDataSource) {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dynamicDataSource);
        // Add XML directory
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
            SqlSessionFactory sqlSessionFactory = bean.getObject();
            sqlSessionFactory.getConfiguration().setCacheEnabled(Boolean.TRUE);

            return sqlSessionFactory;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Bean
    public PlatformTransactionManager transactionManager(DynamicDataSource dynamicDataSource) throws Exception {
        DataSourceTransactionManager txManager = new DataSourceTransactionManager();
        txManager.setDataSource(dynamicDataSource);
        return txManager;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

6. Define data source notes

@Target({ ElementType.TYPE, ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface SelectConnection {
    String name() default "";
}

7. Define data source facets

@Aspect
@Component
public class SelectConnectionInterceptor implements Ordered {
    private static final String DB_PREFIX = "uts";

    @Around("@annotation(selectConnection)")
    public Object proceed(ProceedingJoinPoint joinPoint, SelectConnection selectConnection) throws Throwable {
        String dbName = "";
        if (!StringUtils.isBlank(selectConnection.name())) {
            dbName = selectConnection.name();
        } else {
            BaseEntity baseEntity = (BaseEntity) joinPoint.getArgs()[0];
            Pair<Integer, Integer> pair = SelectorUtil.getDataBaseAndTableNumber(baseEntity.getId());
            dbName = DB_PREFIX + pair.getObject1();
        }
        DataBaseContextHolder.setDataBaseType(DataBaseContextHolder.DataBaseType.valueOf(dbName));
        Object result = joinPoint.proceed();
        DataBaseContextHolder.clearContextHolder();
        return result;

    }

    @Override
    public int getOrder() {
        return 0;
    }

}

Above, the configuration of multiple data sources is completed. On the method of service layer, add @ SelectConnection annotation to specify the connected database.

Sub table implementation

Generally speaking, if there are 10 tables, the ID will be hashed through the hash algorithm, and the result will be mapped to 1-10.

1. Define hash util class

public class Pair<T1, T2> {
    private T1 object1;
    private T2 object2;

    public Pair(T1 object1, T2 object2) {
        this.object1 = object1;
        this.object2 = object2;
    }

    public T1 getObject1() {
        return object1;
    }

    public void setObject1(T1 object1) {
        this.object1 = object1;
    }

    public T2 getObject2() {
        return object2;
    }

    public void setObject2(T2 object2) {
        this.object2 = object2;
    }
}
public class SelectorUtil {
    public static Pair<Integer, Integer> getDataBaseAndTableNumber(String uid) {
        int hashCode = Math.abs(uid.hashCode());
        int dbNumber = hashCode / 10 % 4 + 1;
        int tableNumber = hashCode % 10;
        return new Pair<Integer, Integer>(dbNumber, tableNumber);
    }
}

2. Map to specific table according to hash algorithm

private static final String TABLE_NAME_PREFIX = "trade_detail_";

@SelectConnection
public int balanceInsert(TradeDetail td) {
    String uuid = td.getId();
    Pair<Integer, Integer> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
    Integer tableNumber = pair.getObject2();
    String tableName = TABLE_NAME_PREFIX + tableNumber;
    System.out.println("Current database:" + pair.getObject1());
    System.out.println("Current table:" + tableName);
    return tradeDetailMapper.balanceInsert(tableName, td);

}

Tags: JDBC MySQL Spring Druid

Posted on Thu, 19 Dec 2019 07:17:53 -0800 by my800stuff