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默認(rèn)-->
<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)站的支持！
您可能感興趣的文章:
- Spring+Mybatis 實現(xiàn)aop數(shù)據(jù)庫讀寫分離與多數(shù)據(jù)庫源配置操作
- spring集成mybatis實現(xiàn)mysql數(shù)據(jù)庫讀寫分離
- springboot配置內(nèi)存數(shù)據(jù)庫H2教程詳解
- 詳解spring整合shiro權(quán)限管理與數(shù)據(jù)庫設(shè)計
- Spring MVC配置雙數(shù)據(jù)源實現(xiàn)一個java項目同時連接兩個數(shù)據(jù)庫的方法
- Spring Boot集成MyBatis訪問數(shù)據(jù)庫的方法
- Spring jdbc中數(shù)據(jù)庫操作對象化模型的實例詳解
相關(guān)文章
這篇文章主要介紹了SpringBoot Tomcat啟動實例代碼詳解,需要的朋友可以參考下2017-09-09
在開發(fā)Java應(yīng)用程序時,有時需要從程序中啟動默認(rèn)的Web瀏覽器并訪問特定的網(wǎng)頁,這在實現(xiàn)幫助文檔鏈接、用戶指南或在線資源導(dǎo)航等功能時非常有用,本文將介紹如何使用Java代碼來實現(xiàn)這一功能,需要的朋友可以參考下2025-01-01
如何高效的使用這些資源就是程序員在平時寫代碼時候的一個努力的方向。本文要說的線程池就是一種對 CPU 利用的優(yōu)化手段。對Java 線程池的實現(xiàn)原理相關(guān)知識感興趣的朋友一起看看吧2021-05-05
springboot啟動時如何指定spring.profiles.active
這篇文章主要介紹了springboot啟動時如何指定spring.profiles.active問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-04-04
這篇文章主要介紹Java并發(fā)之同步器設(shè)計，本文以記錄方式并發(fā)編程中同步器設(shè)計的一些共性特征。并簡單介紹了Java中的AQS,需要的朋友可以參考一下文章的詳細(xì)內(nèi)容2021-10-10
IntelliJ IDEA2019實現(xiàn)Web項目創(chuàng)建示例
這篇文章主要介紹了IntelliJ IDEA2019實現(xiàn)Web項目創(chuàng)建示例，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-04-04
許多學(xué)編程專業(yè)的同學(xué)面試的時候,考官都會問到Java如何打印數(shù)組這樣的問題,下面這篇文章主要給大家介紹了關(guān)于Java打印數(shù)組的三種方法,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06
Java傳值調(diào)用和傳引用調(diào)用方式(參數(shù)引用為null問題)
這篇文章主要介紹了Java傳值調(diào)用和傳引用調(diào)用方式(參數(shù)引用為null問題),具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-09-09
這篇文章主要介紹了Java中常見的4種限流算法詳解,FixedWindowRateLimiter 類表示一個固定窗口限流器,使用 limit 和 interval 參數(shù)分別表示限制請求數(shù)量和時間間隔,缺點是短時間內(nèi)可能會流量翻倍,需要的朋友可以參考下2023-12-12
java基礎(chǔ)之Integer與int類型輸出示例解析
這篇文章主要為大家介紹了java基礎(chǔ)之Integer與int類型輸出示例解析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-06-06