Spring+MyBatis实现数据库读写分离方案
方案1
通过MyBatis配置文件创建读写分离两个DataSource，每个SqlSessionFactoryBean对象的mapperLocations属性制定两个读写数据源的配置文件。将所有读的操作配置在读文件中，所有写的操作配置在写文件中。
- 优点：实现简单
- 缺点：维护麻烦，需要对原有的xml文件进行重新修改，不支持多读，不易扩展
- 实现方式
<bean id="abstractDataSource" abstract="true" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
destroy-method="close">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000"/>
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000"/>
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000"/>
<property name="validationQuery" value="SELECT 'x'"/>
<property name="testWhileIdle" value="true"/>
<property name="testOnBorrow" value="false"/>
<property name="testOnReturn" value="false"/>
<!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="true"/>
<property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
<property name="filters" value="config"/>
<property name="connectionProperties" value="config.decrypt=true" />
</bean>
<bean id="readDataSource" parent="abstractDataSource">
<!-- 基本属性 url、user、password -->
<property name="url" value="${read.jdbc.url}"/>
<property name="username" value="${read.jdbc.user}"/>
<property name="password" value="${read.jdbc.password}"/>
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${read.jdbc.initPoolSize}"/>
<property name="minIdle" value="10"/>
<property name="maxActive" value="${read.jdbc.maxPoolSize}"/>
</bean>
<bean id="writeDataSource" parent="abstractDataSource">
<!-- 基本属性 url、user、password -->
<property name="url" value="${write.jdbc.url}"/>
<property name="username" value="${write.jdbc.user}"/>
<property name="password" value="${write.jdbc.password}"/>
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${write.jdbc.initPoolSize}"/>
<property name="minIdle" value="10"/>
<property name="maxActive" value="${write.jdbc.maxPoolSize}"/>
</bean>
<bean id="readSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 实例化sqlSessionFactory时需要使用上述配置好的数据源以及SQL映射文件 -->
<property name="dataSource" ref="readDataSource"/>
<property name="mapperLocations" value="classpath:mapper/read/*.xml"/>
</bean>
<bean id="writeSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 实例化sqlSessionFactory时需要使用上述配置好的数据源以及SQL映射文件 -->
<property name="dataSource" ref="writeDataSource"/>
<property name="mapperLocations" value="classpath:mapper/write/*.xml"/>
</bean>
方案2
通过Spring AOP在业务层实现读写分离，在DAO层调用前定义切面，利用Spring的AbstractRoutingDataSource解决多数据源的问题，实现动态选择数据源
- 优点：通过注解的方法在DAO每个方法上配置数据源，原有代码改动量少，易扩展，支持多读
- 缺点：需要在DAO每个方法上配置注解，人工管理，容易出错
- 实现方式
//定义枚举类型，读写
public enum DynamicDataSourceGlobal {
READ, WRITE;
}
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* RUNTIME
* 定义注解
* 编译器将把注释记录在类文件中，在运行时 VM 将保留注释，因此可以反射性地读取。
* @author shma1664
*
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DataSource {
public DynamicDataSourceGlobal value() default DynamicDataSourceGlobal.READ;
}
/**
* Created by IDEA
* 本地线程设置和获取数据源信息
* User: mashaohua
* Date: 2016-07-07 13:35
* Desc:
*/
public class DynamicDataSourceHolder {
private static final ThreadLocal<DynamicDataSourceGlobal> holder = new ThreadLocal<DynamicDataSourceGlobal>();
public static void putDataSource(DynamicDataSourceGlobal dataSource){
holder.set(dataSource);
}
public static DynamicDataSourceGlobal getDataSource(){
return holder.get();
}
public static void clearDataSource() {
holder.remove();
}
}
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.atomic.AtomicLong;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
/**
* Created by IDEA
* User: mashaohua
* Date: 2016-07-14 10:56
* Desc: 动态数据源实现读写分离
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private Object writeDataSource; //写数据源
private List<Object> readDataSources; //多个读数据源
private int readDataSourceSize; //读数据源个数
private int readDataSourcePollPattern = 0; //获取读数据源方式，0：随机，1：轮询
private AtomicLong counter = new AtomicLong(0);
private static final Long MAX_POOL = Long.MAX_VALUE;
private final Lock lock = new ReentrantLock();
@Override
public void afterPropertiesSet() {
if (this.writeDataSource == null) {
throw new IllegalArgumentException("Property 'writeDataSource' is required");
}
setDefaultTargetDataSource(writeDataSource);
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DynamicDataSourceGlobal.WRITE.name(), writeDataSource);
if (this.readDataSources == null) {
readDataSourceSize = 0;
} else {
for(int i=0; i<readDataSources.size(); i++) {
targetDataSources.put(DynamicDataSourceGlobal.READ.name() + i, readDataSources.get(i));
}
readDataSourceSize = readDataSources.size();
}
setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
DynamicDataSourceGlobal dynamicDataSourceGlobal = DynamicDataSourceHolder.getDataSource();
if(dynamicDataSourceGlobal == null
|| dynamicDataSourceGlobal == DynamicDataSourceGlobal.WRITE
|| readDataSourceSize <= 0) {
return DynamicDataSourceGlobal.WRITE.name();
}
int index = 1;
if(readDataSourcePollPattern == 1) {
//轮询方式
long currValue = counter.incrementAndGet();
if((currValue + 1) >= MAX_POOL) {
try {
lock.lock();
if((currValue + 1) >= MAX_POOL) {
counter.set(0);
}
} finally {
lock.unlock();
}
}
index = (int) (currValue % readDataSourceSize);
} else {
//随机方式
index = ThreadLocalRandom.current().nextInt(0, readDataSourceSize);
}
return dynamicDataSourceGlobal.name() + index;
}
public void setWriteDataSource(Object writeDataSource) {
this.writeDataSource = writeDataSource;
}
public void setReadDataSources(List<Object> readDataSources) {
this.readDataSources = readDataSources;
}
public void setReadDataSourcePollPattern(int readDataSourcePollPattern) {
this.readDataSourcePollPattern = readDataSourcePollPattern;
}
}
import org.apache.log4j.Logger;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;
import java.lang.reflect.Method;
/**
* Created by IDEA
* User: mashaohua
* Date: 2016-07-07 13:39
* Desc: 定义选择数据源切面
*/
public class DynamicDataSourceAspect {
private static final Logger logger = Logger.getLogger(DynamicDataSourceAspect.class);
public void pointCut(){};
public void before(JoinPoint point)
{
Object target = point.getTarget();
String methodName = point.getSignature().getName();
Class<?>[] clazz = target.getClass().getInterfaces();
Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();
try {
Method method = clazz[0].getMethod(methodName, parameterTypes);
if (method != null && method.isAnnotationPresent(DataSource.class)) {
DataSource data = method.getAnnotation(DataSource.class);
DynamicDataSourceHolder.putDataSource(data.value());
}
} catch (Exception e) {
logger.error(String.format("Choose DataSource error, method:%s, msg:%s", methodName, e.getMessage()));
}
}
public void after(JoinPoint point) {
DynamicDataSourceHolder.clearDataSource();
}
}
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.1.xsd">
<bean id="abstractDataSource" abstract="true" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000"/>
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000"/>
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000"/>
<property name="validationQuery" value="SELECT 'x'"/>
<property name="testWhileIdle" value="true"/>
<property name="testOnBorrow" value="false"/>
<property name="testOnReturn" value="false"/>
<!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="true"/>
<property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
<property name="filters" value="config"/>
<property name="connectionProperties" value="config.decrypt=true" />
</bean>
<bean id="dataSourceRead1" parent="abstractDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<!-- 基本属性 url、user、password -->
<property name="url" value="${read1.jdbc.url}"/>
<property name="username" value="${read1.jdbc.user}"/>
<property name="password" value="${read1.jdbc.password}"/>
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${read1.jdbc.initPoolSize}"/>
<property name="minIdle" value="${read1.jdbc.minPoolSize}"/>
<property name="maxActive" value="${read1.jdbc.maxPoolSize}"/>
</bean>
<bean id="dataSourceRead2" parent="abstractDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<!-- 基本属性 url、user、password -->
<property name="url" value="${read2.jdbc.url}"/>
<property name="username" value="${read2.jdbc.user}"/>
<property name="password" value="${read2.jdbc.password}"/>
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${read2.jdbc.initPoolSize}"/>
<property name="minIdle" value="${read2.jdbc.minPoolSize}"/>
<property name="maxActive" value="${read2.jdbc.maxPoolSize}"/>
</bean>
<bean id="dataSourceWrite" parent="abstractDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<!-- 基本属性 url、user、password -->
<property name="url" value="${write.jdbc.url}"/>
<property name="username" value="${write.jdbc.user}"/>
<property name="password" value="${write.jdbc.password}"/>
<!-- 配置初始化大小、最小、最大 -->