java+++多数据源配置,spring+mybatis_多数据源配置

主从数据源配置

分库分表数据源配置

利用动态数据源和AOP实现分库

1.application.yml文件中配置多个数据源

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

# 多数据源配置

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.配置多个 datasource bean

@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.定义数据源枚举类

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 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不允许为空.");

contextHolder.set(dataBaseType);

}

public static void clearContextHolder() {

contextHolder.remove();

}

}

4.定义DynamicDataSource

该类继承AbstractRoutingDataSource，重写determineCurrentLookupKey.

public class DynamicDataSource extends AbstractRoutingDataSource {

@Override

protected Object determineCurrentLookupKey() {

return DataBaseContextHolder.getDataBaseType();

}

}

5.配置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 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);

// 添加XML目录

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. 定义数据源注解

@Target({ ElementType.TYPE, ElementType.METHOD })

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface SelectConnection {

String name() default "";

}

7. 定义数据源切面

@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 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;

}

}

以上，多数据源的配置就完成了。 在service层的方法上，加上@SelectConnection注解，就可以指定连接的数据库。

分表实现

一般来说，如果有10张表，那么会通过hash算法对ID进行hash,将结果映射到1-10。

1.定义hash util类

public class Pair {

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 getDataBaseAndTableNumber(String uid) {

int hashCode = Math.abs(uid.hashCode());

int dbNumber = hashCode / 10 % 4 + 1;

int tableNumber = hashCode % 10;

return new Pair(dbNumber, tableNumber);

}

}

2.根据hash算法映射到具体的表

private static final String TABLE_NAME_PREFIX = "trade_detail_";

@SelectConnection

public int balanceInsert(TradeDetail td) {

String uuid = td.getId();

Pair pair = SelectorUtil.getDataBaseAndTableNumber(uuid);

Integer tableNumber = pair.getObject2();

String tableName = TABLE_NAME_PREFIX + tableNumber;

System.out.println("当前数据库:" + pair.getObject1());

System.out.println("当前表:" + tableName);

return tradeDetailMapper.balanceInsert(tableName, td);

}