欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SpringMVC4+MyBatis+SQL Server2014實現(xiàn)數(shù)據(jù)庫讀寫分離

 更新時間：2017年04月24日 15:30:41   作者：garfieldzf  
這篇文章主要介紹了SpringMVC4+MyBatis+SQL Server2014實現(xiàn)讀寫分離,需要的朋友可以參考下

前言

      基于mybatis的AbstractRoutingDataSource和Interceptor用攔截器的方式實現(xiàn)讀寫分離，根據(jù)MappedStatement的boundsql，查詢sql的select、insert、update、delete，根據(jù)起判斷使用讀寫連接串。

開發(fā)環(huán)境

    SpringMVC4、mybatis3

項目結(jié)構(gòu)

讀寫分離實現(xiàn)

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
    ">
  <!--從配置文件加載數(shù)據(jù)庫信息-->
  <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="locations" value="classpath:config/jdbc.properties"/>
    <property name="fileEncoding" value="UTF-8"/>
  </bean>
  <!--配置數(shù)據(jù)源，這里使用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。實現(xiàn)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; //寫數(shù)據(jù)源
  private Object readDataSource; //讀數(shù)據(jù)源
  @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 {
  /**
   * 只讀事務(wù)到讀庫，讀寫事務(wù)到寫庫
   * @param transaction
   * @param definition
   */
  @Override
  protected void doBegin(Object transaction, TransactionDefinition definition) {
    //設(shè)置數(shù)據(jù)源
    boolean readOnly = definition.isReadOnly();
    if(readOnly) {
      DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.READ);
    } else {
      DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.WRITE);
    }
    super.doBegin(transaction, definition);
  }
  /**
   * 清理本地線程的數(shù)據(jù)源
   * @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("設(shè)置方法["+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) {
  }
}　

測試分離是否實現(xiàn)

    運行UserController.index方法，然后從控制臺看打印結(jié)果

以上所述是小編給大家介紹的SpringMVC4+MyBatis+SQL Server2014實現(xiàn)讀寫分離，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！

相關(guān)文章

  • Java文件(io)編程_文件字節(jié)流的使用方法

    Java文件(io)編程_文件字節(jié)流的使用方法

    下面小編就為大家?guī)硪黄狫ava文件(io)編程_文件字節(jié)流的使用方法。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
    2017-08-08
  • Spring七大事務(wù)傳遞機制深入分析實現(xiàn)原理

    Spring七大事務(wù)傳遞機制深入分析實現(xiàn)原理

    實際項目開發(fā)中,如果涉及到多張表操作時,為了保證業(yè)務(wù)數(shù)據(jù)的一致性,大家一般都會采用事務(wù)機制,好多小伙伴可能只是簡單了解一下,遇到事務(wù)失效的情況,便會無從下手,下面這篇文章主要給大家介紹了關(guān)于Spring事務(wù)傳遞機制的相關(guān)資料,需要的朋友可以參考下
    2023-03-03
  • Java中RocketMQ的延遲消息詳解

    Java中RocketMQ的延遲消息詳解

    這篇文章主要介紹了Java中RocketMQ的延遲消息詳解,RocketMQ是一款開源的分布式消息系統(tǒng),基于高可用分布式集群技術(shù),提供低延時的、高可靠、萬億級容量、靈活可伸縮的消息發(fā)布與訂閱服務(wù),需要的朋友可以參考下
    2023-09-09
  • 在Android系統(tǒng)中使用WebViewClient處理跳轉(zhuǎn)URL的方法

    在Android系統(tǒng)中使用WebViewClient處理跳轉(zhuǎn)URL的方法

    這篇文章主要介紹了在Android系統(tǒng)中使用WebViewClient處理跳轉(zhuǎn)URL的方法,實現(xiàn)代碼為Java語言編寫,是需要的朋友可以參考下
    2015-07-07
  • 帶你了解Java Maven的打包操作

    帶你了解Java Maven的打包操作

    這篇文章主要介紹了Maven打包的相關(guān)知識，本文給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2021-09-09
  • Java2 JDK安裝和配置教程

    Java2 JDK安裝和配置教程

    這篇文章主要為大家詳細介紹了Java2 JDK安裝和配置教程,具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2016-11-11
  • java異步寫日志到文件中實現(xiàn)代碼

    java異步寫日志到文件中實現(xiàn)代碼

    這篇文章主要介紹了java異步寫日志到文件中實現(xiàn)代碼的相關(guān)資料,需要的朋友可以參考下
    2017-04-04
  • SpringBoot集成Swagger2實現(xiàn)Restful(類型轉(zhuǎn)換錯誤解決辦法)

    SpringBoot集成Swagger2實現(xiàn)Restful(類型轉(zhuǎn)換錯誤解決辦法)

    這篇文章主要介紹了SpringBoot集成Swagger2實現(xiàn)Restful(類型轉(zhuǎn)換錯誤解決辦法),需要的朋友可以參考下
    2017-07-07
  • 五分鐘手擼一個Spring容器(萌芽版)

    五分鐘手擼一個Spring容器(萌芽版)

    Spring的兩大內(nèi)核分別是IOC和AOP，其中最最核心的是IOC。這篇文章主要介紹了五分鐘，手擼一個Spring容器的相關(guān)知識,需要的朋友可以參考下
    2022-03-03
  • Spring Security基本配置方法解析

    Spring Security基本配置方法解析

    這篇文章主要介紹了Spring Security基本配置方法解析,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
    2019-09-09

最新評論