SpringMVC4+MyBatis+SQL Server2014实现数据库读写分离

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，主要配置都在这里

xmlns:xsi="http://w3.org/2001/XMLSchema-instance"

xmlns:mvc="http://springframework.org/schema/mvc"

xmlns:context="http://springframework.org/schema/context"

xmlns:aop="http://springframework.org/schema/aop"

xsi:schemaLocation="http://springframework.org/schema/beans

http://springframework.org/schema/beans/spring-beans.xsd

http://springframework.org/schema/context

http://springframework.org/schema/context/spring-context.xsd

http://springframework.org/schema/mvc

http://springframework.org/schema/mvc/spring-mvc.xsd

http://springframework.org/schema/aop

http://springframework.org/schema/aop/spring-aop.xsd

">

xmlns:xsi="http://w3.org/2001/XMLSchema-instance"

xmlns:mvc="http://springframework.org/schema/mvc"

xmlns:context="http://springframework.org/schema/context"

xmlns:aop="http://springframework.org/schema/aop"

xsi:schemaLocation="http://springframework.org/schema/beans

http://springframework.org/schema/beans/spring-beans.xsd

http://springframework.org/schema/context

http://springframework.org/schema/context/spring-context.xsd

http://springframework.org/schema/mvc

http://springframework.org/schema/mvc/spring-mvc.xsd

http://springframework.org/schema/aop

http://springframework.org/schema/aop/spring-aop.xsd

">

4、DynamicDataSource。实现AbstractRoutingDataSource

package com.autohome.rwdb;

import java.util.HashMap;

import java.util.Map;

import http://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

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 holder = new ThreadLocal();

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 cacheMap = new ConcurrentHashMap();

@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实现读写分离，希望对大家有所帮助，如果大家有任何疑问请给我留言，会及时回复大家的。在此也非常感谢大家对我们网站的支持！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。