推荐第四种
方案1
通过MyBatis配置文件创建读写分离两个DataSource，每个SqlSessionFactoryBean对象的mapperLocations属性制定两个读写数据源的配置文件。将所有读的操作配置在读文件中，所有写的操作配置在写文件中。
优点：实现简单
缺点：维护麻烦，需要对原有的xml文件进行重新修改，不支持多读，不易扩展
实现方式
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
<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每个方法上配置注解，人工管理，容易出错
实现方式
?
|
1
2
3
4
|
//定义枚举类型，读写
public enum DynamicDataSourceGlobal {
READ, WRITE;
}
|
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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;
}
|
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
/**
* 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();
}
}
|
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
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;
}
}
|
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
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();
}
}
|
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
|
<?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}"/>
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${write.jdbc.initPoolSize}"/>
<property name="minIdle" value="${write.jdbc.minPoolSize}"/>
<property name="maxActive" value="${write.jdbc.maxPoolSize}"/>
</bean>
<bean id="dataSource" class="com.test.api.dao.datasource.DynamicDataSource">
<property name="writeDataSource" ref="dataSourceWrite" />
<property name="readDataSources">
<list>
<ref bean="dataSourceRead1" />
<ref bean="dataSourceRead2" />
</list>
</property>
<!--轮询方式-->
<property name="readDataSourcePollPattern" value="1" />
<property name="defaultTargetDataSource" ref="dataSourceWrite"/>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"/>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 针对myBatis的配置项 -->
<!-- 配置sqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 实例化sqlSessionFactory时需要使用上述配置好的数据源以及SQL映射文件 -->
<property name="dataSource" ref="dataSource"/>
<property name="mapperLocations" value="classpath:mapper/*.xml"/>
</bean>
<!-- 配置扫描器 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- 扫描包以及它的子包下的所有映射接口类 -->
<property name="basePackage" value="com.test.api.dao.inte"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- 配置数据库注解aop -->
<bean id="dynamicDataSourceAspect" class="com.test.api.dao.datasource.DynamicDataSourceAspect" />
<aop:config>
<aop:aspect id="c" ref="dynamicDataSourceAspect">
<aop:pointcut id="tx" expression="execution(* com.test.api.dao.inte..*.*(..))"/>
<aop:before pointcut-ref="tx" method="before"/>
<aop:after pointcut-ref="tx" method="after"/>
</aop:aspect>
</aop:config>
<!-- 配置数据库注解aop -->
</beans>
|
方案3
通过Mybatis的Plugin在业务层实现数据库读写分离，在MyBatis创建Statement对象前通过拦截器选择真正的数据源，在拦截器中根据方法名称不同（select、update、insert、delete）选择数据源。
优点：原有代码不变，支持多读，易扩展
缺点：
实现方式
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
/**
* Created by IDEA
* User: mashaohua
* Date: 2016-07-19 15:40
* Desc: 创建Connection代理接口
*/
public interface ConnectionProxy extends Connection {
/**
* 根据传入的读写分离需要的key路由到正确的connection
* @param key 数据源标识
* @return
*/
Connection getTargetConnection(String key);
}
|
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|