面临问题
主体环境ssm框架,系统逐渐庞大,包含业务越来越多。
期间使用到多个数据库,使用mybatis编写sql语句越来越复杂。因为系统只配置了一个数据源,因此sql默认执行在那个数据库中,没法动态更换数据库。
所以写sql语句的时候,都要加上库名.表名才能执行业务。复杂业务这样可以接受,但是简单的单库操作,这样无法接受,效率太慢(无法使用mybatis的通用mapper方法,只能一句一句的编写sql语句)。
上网找方法,看到了他人博客,试着解决此困境。最后成功了,记录一下。
参照博客原文
设计总体思路
Spring-Boot+AOP方式实现多数据源切换,继承AbstractRoutingDataSource实现数据源动态的获取,在service层使用注解指定数据源。
数据源配置
- yml文件
# 加密借助于开源框架jasypt,生成方法参考test.java.hai.guo.dou.novel.PrivacyEncryption 类中方法
##主数据库url
master.spring.datasource.url : ENC(u53aH5NS1ag46O6JLg6tuAkqQp6ASgmADw9kC4wBaFyVjvXn/c56Ahn4U8OUfPwOS79jRz/zbgGZ+vxX8utJUzLvJVt90Hh0X49c/TvVsUhYyrYq+74zOuHBnhcibzsr76tLl26AaGGzIqY35/2N5A==)
#数据库用户名
master.spring.datasource.username : ENC(8ncxbxaQp8JP0iC1kiascA==)
#加密后数据库密码
master.spring.datasource.password : ENC(rOxlsSFxutzEbpCnFyMhCg==)
##数据库驱动
master.spring.datasource.driver-class-name : com.mysql.jdbc.Driver
##从数据库url
slave.spring.datasource.url : ENC(joIhIoU5umKwYDSEm/5fEiRm3skVOuTGX2yy0cM2cAmU5f3l66gZnHdPzMRUucYakciBjUAKuD3qNw+NdjWWrtqZEwjaAlYRgRc4Cj3H/U0p1Ugygogv5xKfCx0D46S4bKzYnMKxKtLHLBT141iXZA==)
#数据库用户名
slave.spring.datasource.username : ENC(8ncxbxaQp8JP0iC1kiascA==)
#加密后数据库密码
slave.spring.datasource.password : ENC(rOxlsSFxutzEbpCnFyMhCg==)
##数据库驱动
slave.spring.datasource.driver-class-name : com.mysql.jdbc.Driver
#使用druid的话 需要多配置一个属性spring.datasource.type
spring.datasource.type : com.alibaba.druid.pool.DruidDataSource
- 数据源配置类,使用了Druid数据库连接池,数据源DynamicDataSource类是继承spring提供的动态数据源(AbstractRoutingDataSource)的实现类
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* Druid连接池配置
*/
@Configuration
public class DruidConfig {
private Logger logger = Logger.getLogger(this.getClass());
//主数据库
@Value("${master.spring.datasource.url}")
private String masterDbUrl;
@Value("${master.spring.datasource.username}")
private String masterUsername;
@Value("${master.spring.datasource.password}")
private String masterPassword;
@Value("${master.spring.datasource.driver-class-name}")
private String masterDriverClassName;
//从数据库
@Value("${slave.spring.datasource.url}")
private String slaveDbUrl;
@Value("${master.spring.datasource.username}")
private String slaveUsername;
@Value("${master.spring.datasource.password}")
private String slavePassword;
@Value("${master.spring.datasource.driver-class-name}")
private String slaveDriverClassName;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters}")
private String filters;
@Value("${spring.datasource.connectionProperties}")
private String connectionProperties;
/**
* 主数据源
* @return
*/
@Bean("masterdb")
public DataSource masterdb() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.masterDbUrl);
datasource.setUsername(masterUsername);
datasource.setPassword(masterPassword);
datasource.setDriverClassName(masterDriverClassName);
//configuration
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration Exception", e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
/**
* 从数据源
* @return
*/
@Bean("slavedb")
public DataSource slavedb() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.slaveDbUrl);
datasource.setUsername(slaveUsername);
datasource.setPassword(slavePassword);
datasource.setDriverClassName(slaveDriverClassName);
//configuration
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration Exception", e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
@Bean// 指定使用 DynamicDataSource 来作为系统 dataSource 数据源
@DependsOn("masterdb")
@Primary
public DynamicDataSource createDataSource(DataSource masterdb) {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setDefaultTargetDataSource(masterdb);
dynamicDataSource.setTargetDataSources(getTargetDataSources());
return dynamicDataSource;
}
/* 初始化 targetDataSources */
public Map<Object, Object> getTargetDataSources() {
Map<Object, Object> targetDataSources = new HashMap();
targetDataSources.put("slavedb",slavedb());
return targetDataSources;
}
}
- 自定义数据源 DynamicDataSource 类源码 这个非常重要 是切换数据源的关键性代码
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* <p>
* ** **
* </p>
*
* @author douguohai
* @since 2019-03-03
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private Logger logger = LoggerFactory.getLogger(this.getClass());
@Override
protected Object determineCurrentLookupKey() {
String dataSource = JdbcContextHolder.getDataSource();
logger.info("数据源为{}",dataSource);
return dataSource;
}
}
注意:
看他实现了AbstractRoutingDataSource的一个方法,这个方法作用是在本地线程中获取当前数据源的名称,然后再根据数据源的名称,实现动态切换数据源,看关键性源码
image.png上面方法的targetDataSources集合就是我们在自定义数据源配置文件中定义的
image.png最终实现切换数据源的关键性代码
image-1.png- 数据源管理类(JdbcContextHolder源码,主要是为了在本地线程中记录当前数据源的名称)
/**
* <p>
* ** **
* </p>
*
* @author douguohai
* @since 2019-03-03
*/
public class JdbcContextHolder {
private final static ThreadLocal<String> local = new ThreadLocal<>();
public static void putDataSource(String name) {
local.set(name);
}
public static String getDataSource() {
return local.get();
}
public static void clearDataSource() {
local.remove();
}
}
回归正题
自定义切面和注解
上面说利用aop实现数据源切换,下面定义切面
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* <p>
* ** **
* </p>
*
* @author douguohai
* @since 2019-03-03
*/
@Order(1) //设置AOP执行顺序(需要在事务之前,否则事务只发生在默认库中)
@Aspect
@Component
public class DataSourceAspect {
private Logger logger = LoggerFactory.getLogger(this.getClass());
//切点
@Pointcut("execution(* hai.guo.novel.service.*.*(..))")
public void aspect() { }
@Before("aspect()")
private void before(JoinPoint point) {
Object target = point.getTarget();
String method = point.getSignature().getName();
Class<?> classz = target.getClass();// 获取目标类
Class<?>[] parameterTypes = ((MethodSignature) point.getSignature())
.getMethod().getParameterTypes();
try {
Method m = classz.getMethod(method, parameterTypes);
if (m != null && m.isAnnotationPresent(MyDataSource.class)) {
MyDataSource data = m.getAnnotation(MyDataSource.class);
logger.info("method :{},datasource:{}",m.getName() ,data.value().getName());
JdbcContextHolder.putDataSource(data.value().getName());// 数据源放到当前线程中
}
} catch (Exception e) {
logger.error("get datasource error ",e);
//默认选择master
JdbcContextHolder.putDataSource(DataSourceType.Master.getName());// 数据源放到当前线程中
}
}
@AfterReturning("aspect()")
public void after(JoinPoint point) {
JdbcContextHolder.clearDataSource();
}
}
- 自定义注解,加在service方法上面(这个自定义注解我还没研究,先就这么用吧,等我有时间再看看)
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* <p>
* ** **
* </p>
*
* @author douguohai
* @since 2019-03-03
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface MyDataSource {
DataSourceType value();
}
- 数据源枚举类,名称和自定义的数据源bean的名称要相同
public enum DataSourceType {
// 主表
Master("masterdb"),
// 从表
Slave("slavedb");
private String name;
private DataSourceType(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
切点注解(注意切点与你项目的位置是否相同)
由于我们的动态数据源配置了默认库,所以如果方法是操作默认库的可以不需要注解。
如果要操作非默认数据源,我们需要在方法上添加@MyDataSource("数据源名称")注解,这样就可以利用AOP实现动态切换了
@Service
public class xxxServiceImpl {
@Resource
private XxxMapperExt xxxMapperExt;
@MyDataSource(value= DataSourceType.Slave)
public List<Object> getAll(){
return xxxMapperExt.getAll();
}
}
网友评论