mybatics实现读写分离
开发
DynamicDataSource
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceHolder.getDbType();
}
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
}
- determineCurrentLookupKey(): 决定使用targetDataSources中的哪个DataSource
- defaultTargetDataSource: 默认Datasource
- targetDataSources: 目标Datasource,实际就是个Map,通过determineCurrentLookupKey()的返回值获取该Map中对应的DataSource
DynamicDataSourceHolder
@Slf4j
public class DynamicDataSourceHolder {
/**
* 线程安全的本地线程类
*/
private static ThreadLocal<String> contextHolder = new ThreadLocal<String>();
/**
* 主数据库
*/
public static final String DB_MASTER = "master";
/**
* 从库
*/
public static final String DB_SLAVE = "slave";
/**
* 获取数据源
* @return
*/
public static String getDbType(){
String db = contextHolder.get();
log.debug("getDbType方法中从线程安全的里面获取到：" + db);
if (db == null){
db = DB_MASTER;
}
return db;
}
/**
* 注入线程的数据源
* @param str
*/
public static void setDbType(String str){
log.debug("所注入使用的数据源：" + str);
contextHolder.set(str);
}
/**
* 清理连接
*/
public static void clearDBType(){
contextHolder.remove();
}
}
该类用于将targetDataSources key 保存到threadlocal中，以便DynamicDataSource获取对应的DataSource
DynamicDataSourceConfig
@Configuration
public class DynamicDataSourceConfig {
@Bean
@Primary
public DataSource dynamicDataSource() {
Map<Object, Object> targetDataSources = new HashMap<>();
DataSource masterDataSource = DataSourceBuilder.create().driverClassName("com.mysql.cj.jdbc.Driver").url("jdbc:mysql://www.baidu.com:3306/biz_order?useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai").username("root").password("pwd").build();
DataSource slaveDataSource = DataSourceBuilder.create().driverClassName("com.mysql.cj.jdbc.Driver").url("jdbc:mysql://localhost:3306/biz_order?useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai").username("root").password("").build();
targetDataSources.put("mapper master", masterDataSource);
targetDataSources.put("mapper slave", slaveDataSource);
return new DynamicDataSource(masterDataSource, targetDataSources);
}
}
初始化 Bean DynamicDataSource
Slave
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
public @interface Slave {
}
- 标注在对应的mapper方法上
SlaveIntercepter
@Component
@Intercepts({
@Signature(type = Executor.class, method = "query", args = {
MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class
})
})
@Slf4j
public class ExecutorIntercepter implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
try {
log.info("ExecutorIntercepter.intercept");
Executor executor = (Executor)invocation.getTarget();
MappedStatement mappedStatement = (MappedStatement)invocation.getArgs()[0];
log.info("class:" + mappedStatement.getId().substring(0, mappedStatement.getId().lastIndexOf(".")));
log.info("method:" + mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".")+1));
//todo
DynamicDataSourceHolder.setDbType(DynamicDataSourceHolder.DB_SLAVE);
}catch (Exception e){
e.printStackTrace();
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
log.info(">>>>>>>:plugin");
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
- invocation.target或argus即为Signature中的type及args
- 通过
DynamicDataSourceHolder.setDbType(DynamicDataSourceHolder.DB_SLAVE);设置使用的Datasource - 在todo的位置，根据invocation.target或argus进行一系列判断后，决定使用的DataSource
- 在该拦截器中可获取到执行的mapper方法，判断该方法上是否存在某个注解，如果有则通过slave datasource进行查询
原理
- 在通过DataSource获取Connection时，实际调用的是AbstractRoutingDataSource.getConnection()方法
- DynamicDataSource继承于AbstractRoutingDataSource
- AbstractRoutingDataSource间接实现了接口DataSource
- 在AbstractRoutingDataSource.getConnection()方法中会调用内部方法determineTargetDataSource()获取DataSource，然后调用该DataSource.getConnection()方法（此处采用装饰模式）
- 在方法determineTargetDataSource()中，会调用该抽象类的抽象方法determineCurrentLookupKey()，然后从targetDataSource中获取该key对应的DataSource（此处使用模板模式）
问题
同一接口中首次查库是master,第二次是slave
原因为设置datasource是在StatementHandler的拦截器中，而查询数据库时的执行过程是 Executor拦截器 -> 获取DataSource -> StatementHandler拦截器；故出现问题
报错-com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
2020-05-29 00:22:07 ERROR 25762001 --- [nio-8080-exec-9] c.f.order.controller.BaseController : function.validHandler(exception) http请求失败的异常处理,exception is :
org.springframework.dao.RecoverableDataAccessException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 6,966,382 milliseconds ago. The last packet sent successfully to the server was 6,160,376 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/o
r testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
### The error may exist in class path resource [mapper/collection/OrderCollectionMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM t_financial_order_collection WHERE create_user_id = ? AND collection_status IN (100, 300)
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 6,966,382 milliseconds ago. The last packet sent successfully to the server was 6,160,376 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection valid
ity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
; SQL []; The last packet successfully received from the server was 6,966,382 milliseconds ago. The last packet sent successfully to the server was 6,160,376 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server
configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 6,966,382 milliseconds ago. The last packet sent successfully to the server was 6,160,376 milliseco
nds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy132.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
解决方案添加&autoReconnect=true
url: jdbc:mysql://10.3.9.6:3306/finance_sales?useUnicode=true&characterEncoding=utf-8&autoReconnect=true