使用Mybatis拦截器实现读写分离（代码层面）

优点：使用灵活，可以根据实际的业务需求对读写分离的规则进行调整。

缺点：主从库不能动态扩充，且如果主库宕机，又没有实现容灾逻辑，则系统会丧失写功能。（可以考虑使用mycat中间件解决上述问题）

1. 修改application.yml文件

在application.yml中配置多数据源：

spring: 
datasource: 
master: 
driver-class-name: com.mysql.cj.jdbc.Driver 
url: jdbc:mysql://192.168.0.1:3306/db_test?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8 
username: root 
password: root 
slave: 
driver-class-name: com.mysql.cj.jdbc.Driver 
url: jdbc:mysql://127.0.0.1:3306/db_test?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8 
username: root 
password: root

2. 配置DataSource

可以选择手动创建对应的ConfigurationProperties用于获取yml配置信息，也可以直接添加注解到DataSourceConfig的DataSource上。

• MasterProperties

@ConfigurationProperties(prefix = "spring.datasource.master") 
@PropertySource(value = {"application.yml"}) 
@Component 
public class MasterProperties { 
private String url; 
private String username; 
private String password; 
private String driverClassName; 
public String getUrl() { return url; } 
public void setUrl(String url) { this.url = url; } 
public String getUsername() { return username; } 
public void setUsername(String username) { this.username = username; } 
public String getPassword() { return password; } 
public void setPassword(String password) { this.password = password; } 
public String getDriverClassName() { return driverClassName; } 
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName; } 
}

• SlaveProperties

@ConfigurationProperties(prefix = "spring.datasource.slave") 
@PropertySource(value = {"application.yml"}) 
@Component 
public class SlaveProperties { private String url; 
private String username; private String password; 
private String driverClassName; public String getUrl() { return url; } 
public void setUrl(String url) { this.url = url; } 
public String getUsername() { return username; } 
public void setUsername(String username) { this.username = username; } 
public String getPassword() { return password; } 
public void setPassword(String password) { this.password = password; } 
public String getDriverClassName() { return driverClassName; } 
public void setDriverClassName(String driverClassName) { 
this.driverClassName = driverClassName; }
}

• DynamicDataSourceHolder

用于对数据源类型的赋值和获取。

public class DynamicDataSourceHolder { 
private static ThreadLocal<String> contextHolder = new ThreadLocal<>(); 
public static final String DB_MASTER = "master"; 
public static final String DB_SLAVE = "slave";
public static String getDbType() { 
String db = contextHolder.get();
if (db == null) { db = "master"; } 
return db; } 
public static void setDbType(String str) 
{ contextHolder.set(str); 
} 
public static void clearDbType() { 
contextHolder.remove();
}

• 动态数据源的切换

spring提供了AbstractRoutingDataSource，支持动态选择数据源的功能，替换原有的单一数据源后，即可实现读写分离。

public class DynamicDataSource extends AbstractRoutingDataSource {
@Override 
protected Object determineCurrentLookupKey() { 
// 方法的返回值决定具体从哪个数据源中获取连接 
return DynamicDataSourceHolder.getDbType(); } 
}

• DataSourceConfig

/** * 对主从数据源进行配置 */ 
@Configuration 
public class DataSourceConfig {

@Autowired 
private MasterProperties masterProperties; 

@Autowired 
private SlaveProperties slaveProperties; 

@Bean(name = "masterDataSource") 
public DataSource masterDataSource() { 
HikariDataSource dataSource = new HikariDataSource(); dataSource.setJdbcUrl(masterProperties.getUrl()); dataSource.setUsername(masterProperties.getUsername()); dataSource.setPassword(masterProperties.getPassword()); dataSource.setDriverClassName(masterProperties.getDriverClassName());
return dataSource; }

@Bean(name = "slaveDataSource") 
public DataSource slaveDataSource() { HikariDataSource dataSource = new HikariDataSource(); dataSource.setJdbcUrl(slaveProperties.getUrl()); dataSource.setUsername(slaveProperties.getUsername()); dataSource.setPassword(slaveProperties.getPassword()); dataSource.setDriverClassName(slaveProperties.getDriverClassName()); return dataSource; } 
@Primary 
@Bean(name = "dynamicDataSource") 
public DynamicDataSource dynamicDataSource() { 
Map<Object, Object> dataSourceMap = new HashMap<>(); 
dataSourceMap.put("master", masterDataSource()); 
dataSourceMap.put("slave", slaveDataSource()); 
DynamicDataSource routingDataSource = new DynamicDataSource(); 
// AbstractRoutingDataSource的内部维护了一个名为targetDataSources的Map，并提供setter方法用于设置数据源关键字与数据源的关系 
routingDataSource.setTargetDataSources(dataSourceMap);
routingDataSource.setDefaultTargetDataSource(masterDataSource());
return routingDataSource; }
}

3. Mybatis拦截器

通过拦截器确定对数据库的操作类型，再进行数据源的选择，最后通过DynamicDataSourceHolder的setDbType方法，将数据源类型传入。

@Intercepts({
@Signature(
type = Executor.class, 
method = "update", 
args = {MappedStatement.class, Object.class}), 
@Signature( 
type = Executor.class, 
method = "query", 
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class} )}) 
public class DynamicDataSourceInterceptor implements Interceptor { 
// 验证是否为写SQL的正则表达式 
private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*"; @Override 
public Object intercept(Invocation invocation) throws Throwable {
// 判断当前是否被事务管理 b
oolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive(); String lookupKey = DynamicDataSourceHolder.DB_MASTER; 
if (!synchronizationActive) {
// 如果是非事务的，则再判断是读或者写。 
// 获取SQL中的参数 
Object[] objects = invocation.getArgs(); 
// object[0]会携带增删改查的信息，可以判断是读或者是写 
MappedStatement ms = (MappedStatement) objects[0]; 
// 如果为读，且为自增id查询主键，则使用主库 
// 这种判断主要用于插入时返回ID的操作，由于日志同步到从库有延时 
// 所以如果插入时需要返回id，则不适用于到从库查询数据，有可能查询不到 
if (ms.getSqlCommandType().equals(SqlCommandType.SELECT) && ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) { 
lookupKey = DynamicDataSourceHolder.DB_MASTER; } 
else { 
BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]); String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " "); 
// 正则验证 
if (sql.matches(REGEX)) { 
// 如果是写语句 
lookupKey = DynamicDataSourceHolder.DB_MASTER; } 
else { lookupKey = DynamicDataSourceHolder.DB_SLAVE; } } } 
else { 
// 如果是通过事务管理的，一般都是写语句,直接通过主库 
lookupKey = DynamicDataSourceHolder.DB_MASTER; } DynamicDataSourceHolder.setDbType(lookupKey); 
// 最后直接执行SQL 
return invocation.proceed(); }
@Override 
public Object plugin(Object target) { 
// 如果存在增删改查，则直接拦截下来，否则直接返回 
if (target instanceof Executor) return Plugin.wrap(target, this); else return target; } 
/** * 类初始化的时候做一些相关的设置 */ 
@Override 
public void setProperties(Properties properties) {
}

4. 扩展

除了使用mybatis拦截器，还可以通过AOP切面实现对数据源的切换。

@Target({ElementType.TYPE, ElementType.METHOD}) 
@Retention(RetentionPolicy.RUNTIME) 
public @interface DataSource { 
String name() default "";
}

@Aspect 
@Component 
public class HandleDatasourceAspect { 
@Pointcut("@annotation(com.demo.config.DataSource)") 
public void pointcut() { } 
@Before("pointcut()") 
public void beforeExecute(JoinPoint joinPoint) { 
Method method = ((MethodSignature) joinPoint.getSignature()).getMethod(); 
DataSource annotation = method.getAnnotation(DataSource.class); 
if (null == annotation) { 
annotation = joinPoint.getTarget().getClass().getAnnotation(DataSource.class); 
} 
if (null != annotation) { 
// 切换数据源 
DynamicDataSourceHolder.setDbType(annotation.name()); } } 
@After("pointcut()") 
public void afterExecute() { 
DynamicDataSourceHolder.clearDbType(); } }