Spring+MyBatis实现数据库读写分离方案

推荐第四种：https://github.com/shawntime/shawn-rwdb

方案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}"/> <!-- 配置初始化大小、最小、最大 --> <