前言
基于mybatis的AbstractRoutingDataSource和Interceptor用攔截器的方式實現讀寫分離，根據MappedStatement的boundsql，查詢sql的select、insert、update、delete，根據起判斷使用讀寫連接串。
開發環境
SpringMVC4、mybatis3
項目結構
讀寫分離實現
1、pom.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
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
|
<dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.10</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.3.6.RELEASE</version> </dependency> <dependency> <groupId>org.apache.velocity</groupId> <artifactId>velocity</artifactId> <version>1.6.2</version> </dependency> <dependency> <groupId>org.apache.velocity</groupId> <artifactId>velocity-tools</artifactId> <version>2.0</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.2</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.3.0</version> </dependency> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>3.1.0</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.25</version> </dependency> </dependencies> |
2、jdbc.properties
|
1
2
3
4
5
6
|
sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriversqlserver.url=jdbc:sqlserver://127.0.0.1:1433;databaseName=testsqlserver.read.username=sasqlserver.read.password=000000sqlserver.writer.username=sasqlserver.writer.password=000000 |
3、springmvc-serlvet.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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
<?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:mvc="http://www.springframework.org/schema/mvc" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd "> <!--從配置文件加載數據庫信息--> <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations" value="classpath:config/jdbc.properties"/> <property name="fileEncoding" value="UTF-8"/> </bean> <!--配置數據源，這里使用Spring默認--> <bean id="abstractDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="${sqlserver.driver}"/> <property name="url" value="${sqlserver.url}"/> </bean> <!--讀--> <bean id="shawnTimeDataSourceRead" parent="abstractDataSource"> <property name="username" value="${sqlserver.read.username}"/> <property name="password" value="${sqlserver.read.password}"/> </bean> <!--寫--> <bean id="shawnTimeDataSourceWiter" parent="abstractDataSource"> <property name="username" value="${sqlserver.writer.username}"/> <property name="password" value="${sqlserver.writer.password}"/> </bean> <bean id="shawnTimeDataSource" class="com.autohome.rwdb.DynamicDataSource"> <property name="readDataSource" ref="shawnTimeDataSourceRead"/> <property name="writeDataSource" ref="shawnTimeDataSourceRead"/> </bean> <bean id="shawnTimeTransactionManager" class="com.autohome.rwdb.DynamicDataSourceTransactionManager"> <property name="dataSource" ref="shawnTimeDataSource"/> </bean> <!--配置sqlSessionFactory--> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="configLocation" value="classpath:springmvc-mybatis.xml"/> <property name="dataSource" ref="shawnTimeDataSource"/> <property name="plugins"> <array> <bean class="com.autohome.rwdb.DynamicPlugin"/> </array> </property> </bean> <!--掃描Mapper--> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.autohome.dao"/> </bean> <!--啟用最新的注解器、映射器--> <mvc:annotation-driven/> <context:component-scan base-package="com.autohome.*"/> <!--jsp視圖解析器--> <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="prefix" value="/WEB-INF/views/"/> <property name="suffix" value=".jsp"/> </bean></beans> |
4、DynamicDataSource。實現AbstractRoutingDataSource
|
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
|
package com.autohome.rwdb;import java.util.HashMap;import java.util.Map;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;public class DynamicDataSource extends AbstractRoutingDataSource { private Object writeDataSource; //寫數據源 private Object readDataSource; //讀數據源 @Override public void afterPropertiesSet() { if (this.writeDataSource == null) { throw new IllegalArgumentException("Property 'writeDataSource' is required"); } setDefaultTargetDataSource(writeDataSource); Map<Object, Object> targetDataSources = new HashMap<Object, Object>(); targetDataSources.put(DynamicDataSourceGlobal.WRITE.name(), writeDataSource); if(readDataSource != null) { targetDataSources.put(DynamicDataSourceGlobal.READ.name(), readDataSource); } setTargetDataSources(targetDataSources); super.afterPropertiesSet(); } @Override protected Object determineCurrentLookupKey() { DynamicDataSourceGlobal dynamicDataSourceGlobal = DynamicDataSourceHolder.getDataSource(); if(dynamicDataSourceGlobal == null || dynamicDataSourceGlobal == DynamicDataSourceGlobal.WRITE) { return DynamicDataSourceGlobal.WRITE.name(); } return DynamicDataSourceGlobal.READ.name(); } public void setWriteDataSource(Object writeDataSource) { this.writeDataSource = writeDataSource; } public Object getWriteDataSource() { return writeDataSource; } public Object getReadDataSource() { return readDataSource; } public void setReadDataSource(Object readDataSource) { this.readDataSource = readDataSource; }} |
5、DynamicDataSourceGlobal
|
1
2
3
4
|
package com.autohome.rwdb;public enum DynamicDataSourceGlobal { READ, WRITE;} |
6、DynamicDataSourceHolder
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
package com.autohome.rwdb;public final class DynamicDataSourceHolder { private static final ThreadLocal<DynamicDataSourceGlobal> holder = new ThreadLocal<DynamicDataSourceGlobal>(); private DynamicDataSourceHolder() { // } public static void putDataSource(DynamicDataSourceGlobal dataSource){ holder.set(dataSource); } public static DynamicDataSourceGlobal getDataSource(){ return holder.get(); } public static void clearDataSource() { holder.remove(); }} |
7、DynamicDataSourceTransactionManager
|
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
|
package com.autohome.rwdb;import org.springframework.jdbc.datasource.DataSourceTransactionManager;import org.springframework.transaction.TransactionDefinition;public class DynamicDataSourceTransactionManager extends DataSourceTransactionManager { /** * 只讀事務到讀庫，讀寫事務到寫庫 * @param transaction * @param definition */ @Override protected void doBegin(Object transaction, TransactionDefinition definition) { //設置數據源 boolean readOnly = definition.isReadOnly(); if(readOnly) { DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.READ); } else { DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.WRITE); } super.doBegin(transaction, definition); } /** * 清理本地線程的數據源 * @param transaction */ @Override protected void doCleanupAfterCompletion(Object transaction) { super.doCleanupAfterCompletion(transaction); DynamicDataSourceHolder.clearDataSource(); }} |
8、DynamicPlugin
|
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
|
package com.autohome.rwdb;import java.util.Locale;import java.util.Map;import java.util.Properties;import java.util.concurrent.ConcurrentHashMap;import org.apache.ibatis.executor.Executor;import org.apache.ibatis.executor.keygen.SelectKeyGenerator;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.mapping.SqlCommandType;import org.apache.ibatis.plugin.*;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.session.RowBounds;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.transaction.support.TransactionSynchronizationManager;@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 }) })public class DynamicPlugin implements Interceptor { protected static final Logger logger = LoggerFactory.getLogger(DynamicPlugin.class); private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*"; private static final Map<String, DynamicDataSourceGlobal> cacheMap = new ConcurrentHashMap<String, DynamicDataSourceGlobal>(); @Override public Object intercept(Invocation invocation) throws Throwable { boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive(); if(!synchronizationActive) { Object[] objects = invocation.getArgs(); MappedStatement ms = (MappedStatement) objects[0]; DynamicDataSourceGlobal dynamicDataSourceGlobal = null; if((dynamicDataSourceGlobal = cacheMap.get(ms.getId())) == null) { //讀方法 if(ms.getSqlCommandType().equals(SqlCommandType.SELECT)) { //!selectKey 為自增id查詢主鍵(SELECT LAST_INSERT_ID() )方法，使用主庫 if(ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) { dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE; } else { BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]); //獲取MappedStatement 的sql語句，select update delete insert String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " "); if(sql.matches(REGEX)) { dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE; } else { dynamicDataSourceGlobal = DynamicDataSourceGlobal.READ; } } }else{ dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE; } System.out.println("設置方法["+ms.getId()+"] use ["+ dynamicDataSourceGlobal.name()+"] Strategy, SqlCommandType ["+ms.getSqlCommandType().name()+"].."); cacheMap.put(ms.getId(), dynamicDataSourceGlobal); } DynamicDataSourceHolder.putDataSource(dynamicDataSourceGlobal); } 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) { }} |
測試分離是否實現
運行UserController.index方法，然后從控制臺看打印結果
以上所述是小編給大家介紹的SpringMVC4+MyBatis+SQL Server2014實現讀寫分離，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
原文鏈接：http://www.cnblogs.com/sword-successful/archive/2017/04/24/6756886.html
