这里有新鲜出炉的MyBatis教程,程序狗速度看过来!
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis 。
这篇文章主要介绍了SpringMVC4+MyBatis+SQL Server2014实现读写分离,需要的朋友可以参考下
前言
基于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实现读写分离,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对PHPERZ网站的支持!
来源: http://www.phperz.com/article/17/1208/357400.html