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

前言

      基于mybatis的abstractroutingdatasource和interceptor用拦截器的方式实现读写分离，根据mappedstatement的boundsql，查询sql的select、insert、update、delete，根据起判断使用读写连接串。

开发环境

    springmvc4、mybatis3

项目结构

读写分离实现

1、pom.xml

<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

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

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实现读写分离，希望对大家有所帮助