SpringMVC4+MyBatis+SQL Server2014实现数据库读写分离
程序员文章站
2024-03-31 12:59:28
前言
基于mybatis的abstractroutingdatasource和interceptor用拦截器...
前言
基于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实现读写分离，希望对大家有所帮助
推荐阅读
-
SpringMVC4+MyBatis+SQL Server2014实现数据库读写分离
-
MySQL的使用中实现读写分离的教程
-
spring集成mybatis实现mysql数据库读写分离
-
CentOS6中MySql5.6数据库主从复制/读写分离（二）
-
MySQL主从复制（Master-Slave）与读写分离（MySQL-Proxy）实践 博客分类： 数据库集群
-
Linux MySQL数据库集群实战 读写分离
-
Linux运维之HAProxy实现web页面的动静分离、读写分离
-
Mycat中间件实现一主一从和双主双从的读写分离
-
Windows中使用MyCat实现读写分离
-
MySQL数据库的主从同步和读写分离