SpringMVC4+MyBatis+SQL Server2014实现数据库读写分离
作者：garfieldzf 发布时间：2024-01-23 05:07:22
标签：springmvc4,mybatis,sql2014
前言
基于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实现读写分离网站的支持！
来源：http://www.cnblogs.com/sword-successful/archive/2017/04/24/6756886.html
0
投稿猜你喜欢
本文实例讲述了Python设计模式之命令模式原理与用法。分享给大家供大家参考，具体如下：命令模式(Command Pattern):将请求封- 1、Python数据存储（压缩）（1）numpy.save , numpy.savez , scipy.io.savematnumpy和sc
- Deferred对象结构Deferred由一系列成对的回调链组成，每一对都包含一个用于处理成功的回调（callbacks）和一个用于处理错误
- 本文实例讲述了Python multiprocess pool模块报错pickling error问题解决方法。分享给大家供大家参考，具体如
- 在程序实际应用中，少不了要进行字符串拼接的操作。下面介绍一下Python语言中四种字符串拼接的方式。1. 算术运算符拼接在Python中算术
- 我就废话不多说了，大家还是直接看代码吧~import tensorflow as tffrom sklearn.metrics import
- 1.基本信息Pandas 的 apply() 方法是用来调用一个函数(Python method)，让此函数对数据对象进行批量处理。Pand
- 下面代码的功能是先训练一个简单的模型，然后保存模型，同时保存到一个pb文件当中，后续可以从pd文件里读取权重值。import tensorf
- 迄今为止，导出/导入工具集仍是跨多个平台转移数据所需劳动强度最小的首选实用工具，尽管人们常常抱怨它速度太慢。导入只是将每条记录从导出转储文件
- 1. 引言Python目前是世界上使用最多的编程语言之一。它能够以更少的工作量和更少的代码行数来完成许多事情。它还可以使用很少的代码行来方便
- 使用叹号（！）禁止用户终端进入的一个方法。mysql> grant all privileges on wubx.* to ‘wubx
- 如果想读取用 open() 函数打开的文件中的内容，除了可以使用 read() 函数，还可以使用 readline() 和 readline
- Django RBAC权限管理概述RBAC(Role-Based Access Control,基于角色的访问控制)，通过角色绑定权限，然后
- 1、使用专用网站获取的是公网IP网址：http://myip.ipip.net代码：import requestsres = request
- 引言什么是数据结构？数据结构是指相互之间存在着一种或多种关系的数据元素的集合和该集合中数据元素之间的关系组成。简单来说，数据结构就是设计数据
- 引言昨天把系分的项目又加了一点逻辑判断，比如已经下单的菜品不能减少，不能重复下单，应该先下单再支付等…弄完购物车的逻辑之后，发现一直没有管备
- 首先介绍下怎么发现的吧, 线上的项目日志是通过 logging 模块打到 syslog 里, 跑了一段时间后发现 syslog 的 UDP
- QL Server事件探查器(Profiler)可以帮助数据库管理员跟踪SQL Server数据库所执行的特定事件，监视数据库的行为;并将这
- 问题描述 为了程序的正常运行，进行异常处理是有必要的，甚至于有时候，我们会主动的抛出异常，然后让程序进行异常捕获，再进行进一步的处理。但是，
- 目录楔子faker使用方法基本使用地理信息类基础信息类计算机基础、Internet信息类网络基础信息类浏览器信息类数字类文本、加密类时间信息
