MyBatis 本是 apache 的一个开源项目 iBatis, 2010 年这个项目由 apache software foundation 迁移到了 google code,并且改名为 MyBatis 。
这篇文章主要介绍了 SpringMVC4+MyBatis+SQL Server2014 实现读写分离, 需要的朋友可以参考下
前言
基于 mybatis 的 AbstractRoutingDataSource 和 Interceptor 用拦截器的方式实现读写分离,根据 MappedStatement 的 boundsql,查询 sql 的 select、insert、update、delete,根据起判断使用读写连接串。
开发环境
SpringMVC4、mybatis3
项目结构
读写分离实现
1、pom.xml
junit
junit
4.10
org.springframework
spring-core
4.3.6.RELEASE
org.springframework
spring-beans
4.3.6.RELEASE
org.springframework
spring-context
4.3.6.RELEASE
org.springframework
spring-web
4.3.6.RELEASE
org.springframework
spring-context-support
4.3.6.RELEASE
org.springframework
spring-webmvc
4.3.6.RELEASE
org.springframework
spring-jdbc
4.3.6.RELEASE
org.apache.velocity
velocity
1.6.2
org.apache.velocity
velocity-tools
2.0
org.mybatis
mybatis
3.4.2
org.mybatis
mybatis-spring
1.3.0
com.microsoft.sqlserver
sqljdbc4
4.0
commons-dbcp
commons-dbcp
1.4
javax.servlet
javax.servlet-api
3.1.0
org.slf4j
slf4j-log4j12
1.7.25
2、jdbc.properties
sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sqlserver.url=jdbc:sqlserver://127.0.0.1:1433;databaseName=test
sqlserver.read.username=sa
sqlserver.read.password=000000
sqlserver.writer.username=sa
sqlserver.writer.password=000000
3、springmvc-serlvet.xml,主要配置都在这里
xml version="1.0" encoding="UTF-8" ?>
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
">
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
name="locations" value="classpath:config/jdbc.properties" />
name="fileEncoding" value="UTF-8" />
id="abstractDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
name="driverClassName" value="${sqlserver.driver}" />
name="url" value="${sqlserver.url}" />
id="shawnTimeDataSourceRead" parent="abstractDataSource">
name="username" value="${sqlserver.read.username}" />
name="password" value="${sqlserver.read.password}" />
id="shawnTimeDataSourceWiter" parent="abstractDataSource">
name="username" value="${sqlserver.writer.username}" />
name="password" value="${sqlserver.writer.password}" />
id="shawnTimeDataSource" class="com.autohome.rwdb.DynamicDataSource">
name="readDataSource" ref="shawnTimeDataSourceRead" />
name="writeDataSource" ref="shawnTimeDataSourceRead" />
id="shawnTimeTransactionManager" class="com.autohome.rwdb.DynamicDataSourceTransactionManager">
name="dataSource" ref="shawnTimeDataSource" />
id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
name="configLocation" value="classpath:springmvc-mybatis.xml"
/>
name="dataSource" ref="shawnTimeDataSource" />
name="plugins">
class="com.autohome.rwdb.DynamicPlugin" />
class="org.mybatis.spring.mapper.MapperScannerConfigurer">
name="basePackage" value="com.autohome.dao" />
base-package="com.autohome.*" />
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
name="prefix" value="/WEB-INF/views/" />
name="suffix" value=".jsp" />
4、DynamicDataSource。实现 AbstractRoutingDataSource
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
package com.autohome.rwdb;
public enum DynamicDataSourceGlobal {
READ, WRITE;
}
6、DynamicDataSourceHolder
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
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
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 实现读写分离,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 PHPERZ 网站的支持!
来源: http://www.phperz.com/article/17/1208/357400.html