基于Spring和Mybatis拦截器实现数据库操作读写分离
首先需要配置好数据库的主从同步:
上一篇文章中有写到:https://www.cnblogs.com/xuyiqing/p/10647133.html
为什么要进行读写分离呢?
通常的Web应用大多数读操作次数远大于写操作
为了提高效率,我们可以考虑读写分离
读写分离可以采用DBProxy中间件,这里不谈这种方式
而是使用Mabatis拦截器
大体思路是:
得到SQL语句进行分析,如果是select那么分配从数据库数据源
反之,分配主数据库数据源
Spring重写路由:
package org.dreamtech.o2o.dao.split; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; /** * 数据源路由重写,用于读写分离 * * @author Xu Yiqing * */ public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceHolder.getDbType(); } }
自定义工具类:
package org.dreamtech.o2o.dao.split; /** * 辅助进行读写分离操作 * * @author Xu Yiqing * */ public class DynamicDataSourceHolder { private static ThreadLocal<String> contextHolder = new ThreadLocal<String>(); public static final String DB_MASTER = "master"; public static final String DB_SLAVE = "slave"; /** * 获取dbType * * @return dbType */ public static String getDbType() { String db = contextHolder.get(); if (db == null) { db = DB_MASTER; } return db; } /** * 设置线程的dbType * * @param str * 类型 */ public static void setDbType(String str) { contextHolder.set(str); } /** * 清理连接类型 */ public static void clearDBType() { contextHolder.remove(); } }
Mybatis过滤器:
package org.dreamtech.o2o.dao.split; import java.util.Locale; import java.util.Properties; 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.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; import org.springframework.transaction.support.TransactionSynchronizationManager; /** * Mybatis拦截器,实现读写分离 * * @author Xu Yiqing * */ @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 DynamicDataSourceInterceptor implements Interceptor { private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*"; /** * 核心方法 */ @Override public Object intercept(Invocation invocation) throws Throwable { boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive(); Object[] objects = invocation.getArgs(); MappedStatement ms = (MappedStatement) objects[0]; String lookupKey = DynamicDataSourceHolder.DB_MASTER; if (synchronizationActive != true) { if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) { if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) { lookupKey = DynamicDataSourceHolder.DB_MASTER; } else { BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]); String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " "); if (sql.matches(REGEX)) { lookupKey = DynamicDataSourceHolder.DB_MASTER; } else { lookupKey = DynamicDataSourceHolder.DB_SLAVE; } } } } else { lookupKey = DynamicDataSourceHolder.DB_MASTER; } DynamicDataSourceHolder.setDbType(lookupKey); 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 arg0) { } }
配置主从服务器的地址:
为了方便,他们的用户名和密码都是一样的
jdbc.properties:
jdbc.driver=com.mysql.jdbc.Driver jdbc.master.url=jdbc:mysql://192.168.111.134:3306/o2o?useUnicode=true&characterEncoding=utf8 jdbc.slave.url=jdbc:mysql://192.168.111.135:3306/o2o?useUnicode=true&characterEncoding=utf8 jdbc.username=work jdbc.password=xuyiqing
mybatis-config.xml:Mybatis配置文件:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> ...... <!-- 自定义拦截器 --> <plugins> <plugin interceptor="org.dreamtech.o2o.dao.split.DynamicDataSourceInterceptor" /> </plugins> ...... </configuration>
spring-dao.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:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd"> ...... <context:property-placeholder location="classpath:jdbc.properties" /> <!-- 数据源 --> <bean id="abstractDataSource" abstract="true" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="maxPoolSize" value="30" /> <property name="minPoolSize" value="10" /> <property name="autoCommitOnClose" value="false" /> <property name="checkoutTimeout" value="10000" /> <property name="acquireRetryAttempts" value="2" /> </bean> <!-- 主数据库 --> <bean id="master" parent="abstractDataSource"> <property name="driverClass" value="${jdbc.driver}" /> <property name="jdbcUrl" value="${jdbc.master.url}" /> <property name="user" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </bean> <!-- 从数据库 --> <bean id="slave" parent="abstractDataSource"> <property name="driverClass" value="${jdbc.driver}" /> <property name="jdbcUrl" value="${jdbc.slave.url}" /> <property name="user" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </bean> <!-- 配置动态数据源 --> <bean id="dynamicDataSource" class="org.dreamtech.o2o.dao.split.DynamicDataSource"> <property name="targetDataSources"> <map> <entry value-ref="master" key="master"></entry> <entry value-ref="slave" key="slave"></entry> </map> </property> </bean> <!-- 懒加载数据源 --> <bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy"> <property name="targetDataSource"> <ref bean="dynamicDataSource" /> </property> </bean> ...... </beans>