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

<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>

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

<?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>

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;  }}

package com.autohome.rwdb;public enum DynamicDataSourceGlobal {  READ, WRITE;}

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();  }}

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();  }}

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) {  }}