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<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不允許為空.");
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<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);
// 添加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<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;
}
}

以上，多數據源的配置就完成了。 在service層的方法上，加上@SelectConnection註解，就可以指定連接的資料庫。

分表實現

一般來說，如果有10張表，那麼會通過hash演算法對ID進行hash,將結果映射到1-10。

1.定義hash util類

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.根據hash演算法映射到具體的表

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("當前資料庫:" + pair.getObject1());
System.out.println("當前表:" + tableName);
return tradeDetailMapper.balanceInsert(tableName, td);
}

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！