一般项目都会区分读写库或者多数据源
这就导致不同的查询需要去不同的库查询
我们公司的多数据源采用的是注解的形式实现的
举例:
@ReadDataSource注解去读库
@WriteDataSource注解去写库
(注解的实现类里做的切换数据源的操作)
使用spring的jdbctemplete再持久层方法上加注解是可以实现数据源切换的
例如:
@ReadDataSource
public List<Map<String, Object>> get() {
String SQL ="SELECT * FROM Demo";
List<Map<String, Object>> retMap = null;
try {
retMap = jdbcTemplate.queryForList(SQL);
} catch (DataAccessException e) {
return new ArrayList<Map<String, Object>>();
}
return retMap;
}
当再服务处调用这个方法是可以实现(本类中调用的话 需要拿到spring的对象调用才可以,直接调用是不行的,因为本类中调用方法不会走代理)
但是使用mybatis的话 再mapper上加注解是无效的,原因是调用mapper的方法时,mybatis会通过代理实现这个方法,但是并不会实现方法上的注解(除了mybatis自带的@select等注解)这种情况下切换数据源是无效的
举例:
public interface DemoMapper {
@ReadDataSource
List<Demo> get();
@WriteDataSource
void insert(Demo demo);
}
最终的实现方案是通过mybatis拦截器实现的,拦截器如下:
//拦截Executor的三个方法
@Intercepts({@Signature(type = Executor.class,method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class }),
@Signature(type = Executor.class,method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
@Signature(type = Executor.class,method = "update", args = { MappedStatement.class, Object.class})})
@Component
public class DataSourceInterceptor implements Interceptor {
DataSourceEntity entity;
public void init(DataSourceEntity entity) {
//项目启动的时候调用会把非默认数据源放这里
this.entity = entity;
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement)args[0];
//拿到方法全名 全类名.方法名
String fullMethodName = ms.getId();
//拿到全类名
String className = getClassName(fullMethodName);
Class<?> mapper = Class.forName(className);
//因为我没有拿到方法的参数,所以要保证方法名不同(重载的方法数据源也应该是一样的)
Method methodByMethodName = getMethodByMethodName(getMethodName(fullMethodName), mapper.getMethods());
//拿到方法上的数据源切换注解
WriteDataSource writeDataSource = methodByMethodName.getAnnotation(WriteDataSource.class);
ReadDataSource readDataSource = methodByMethodName.getAnnotation(ReadDataSource.class);
boolean flag = false;
if(readDataSource!=null){
readBefore();
flag = true;
}
if(writeDataSource!=null){
writeBefore();
flag = true;
}
//执行数据查询
Object proceed = invocation.proceed();
if(flag){
after();
}
//返回数据
return proceed;
}
private String getClassName(String fullMethodName){
return fullMethodName.substring(0,fullMethodName.lastIndexOf("."));
}
private String getMethodName(String fullMethodName){
return fullMethodName.substring(fullMethodName.lastIndexOf(".")+1);
}
private Method getMethodByMethodName(String methodName,Method[] methods){
for (Method method : methods) {
if(method.getName().equals(methodName)){
return method;
}
}
return null;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target,this);
}
@Override
public void setProperties(Properties properties) {
}
public void writeBefore() {
//先清空数据源
DataSourceHolder.clearDataSource();
//设置写库
DataSourceBeanBuilder builder = new DataSourceBeanBuilder(entity.getDbName() + DynamicDataSourceGlobal.W, entity.getIpMaster(), entity.getPort(), entity.getDbName(), entity.getUsername(), entity.getPassword());
DataSourceHolder.setDataSource(builder);
}
public void readBefore() {
//先清空数据源
DataSourceHolder.clearDataSource();
//设置读库
DataSourceBeanBuilder builder = new DataSourceBeanBuilder(entity.getDbName() + DynamicDataSourceGlobal.R, entity.getIpMaster(), entity.getPort(), entity.getDbName(), entity.getUsername(), entity.getPassword());
DataSourceHolder.setDataSource(builder);
}
public void after(){
//清空数据源
DataSourceHolder.clearDataSource();
DynamicDataSourceHolder.clearDataSource();
}
}
//设置mybatis的SqlSessionFactory的时候注入拦截器
@Bean
public SqlSessionFactory sqlSessionFactory(DynamicDataSource dataSource,DataSourceInterceptor dataSourceInterceptor) throws Exception {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource);
sqlSessionFactory.setPlugins(new Interceptor[]{dataSourceInterceptor});
return sqlSessionFactory.getObject();
}
//项目启动最后将数据源放到拦截器里
@Bean
public CommandLineRunner initData(DataSourceDao dataSourceDao,DataSourceInterceptor dataSourceInterceptor) {
return strings -> {
try {
dataSourceInterceptor.init(dataSourceDao.getDataSourceByTenantName("1"));
} catch (Exception e) {
e.printStackTrace();
}
};
}