主从数据源配置
分库分表数据源配置
利用动态数据源和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);
}
转播
