mybatis用spring的动态数据源实现读写分离
一、环境：
三个mysql数据库。一个master，两个slaver。master写数据，slaver读数据。
二、原理：
借助Spring的 AbstractRoutingDataSource 这个抽象实现。我们要实现 determineCurrentLookupKey()这个方法来动态的选择使用哪个数据源操着数据库
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean { protected abstract Object determineCurrentLookupKey(); }
三、实现步骤：
1、添加spring，mybatis，mysql相关的pom依赖。
2、写jdbc.properties，定义三个数据库。
jdbc.driver=com.mysql.jdbc.Driver
#master库
master.jdbc.url=jdbc:mysql://127.0.0.1:3306/master?characterEncoding=utf8
master.jdbc.user=root
master.jdbc.password=tiger
#slave 一 库
slave.one.jdbc.url=jdbc:mysql://127.0.0.1:3306/slave-one?characterEncoding=utf8
slave.one.jdbc.user=root
slave.one.jdbc.password=tiger
#slave 二 库
slave.two.jdbc.url=jdbc:mysql://127.0.0.1:3306/slave-two?characterEncoding=utf8
slave.two.jdbc.user=root
slave.two.jdbc.password=tiger
3、配置三个数据源，分别写到三个配置文件中。
datasources-master.xml、datasource-slave-one.xml和datasource-slave-two.xml三个文件都一样，这里就写一个
<!--master数据源，支持读写--> <bean id="dataSourceMaster" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <property name="driverClassName" value="${jdbc.driver}"/> <property name="url" value="${master.jdbc.url}"/> <property name="username" value="${master.jdbc.user}"/> <property name="password" value="${master.jdbc.password}"/> <property name="filters" value="stat"/> <property name="maxActive" value="20"/> <property name="initialSize" value="1"/> <property name="maxWait" value="60000"/> <property name="minIdle" value="1"/> <property name="timeBetweenEvictionRunsMillis" value="60000"/> <property name="minEvictableIdleTimeMillis" value="300000"/> <property name="validationQuery" value="SELECT 'x'"/> <property name="testWhileIdle" value="true"/> <property name="testOnBorrow" value="false"/> <property name="testOnReturn" value="false"/> </bean>
4、写spring的配置文件applicationContext.xml。
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath*:jdbc.properties</value> </list> </property> <property name="fileEncoding" value="UTF-8" /> <property name= "ignoreResourceNotFound" value="false"/> </bean> <context:component-scan base-package="org.hope.lee"/> <aop:aspectj-autoproxy/> <!--spring的路由来管理数据源--> <bean id="dynamicDataSource" class="org.hope.lee.utils.DynamicDataSource"> <property name="targetDataSources"> <map> <entry value-ref="dataSourceMaster" key="db_master"/> <entry value-ref="dataSourceSlaveOne" key="db_slave_one"/> <entry value-ref="dataSourceSlaveTwo" key="db_slave_two"/> </map> </property> </bean> <!--spring-mybatis整合--> <bean id="dynamicsqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dynamicDataSource"/> <property name="mapperLocations"> <array> <value>classpath:mappers/*Mapper.xml</value> </array> </property> <property name="configLocation" value="classpath:mybatis-config.xml"/> <property name="typeAliasesPackage" value="org.hope.lee.model"/> </bean> <!--自动扫描所有的Mapper接口与文件--> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="org.hope.lee.dao"></property> <property name="sqlSessionFactoryBeanName" value="dynamicsqlSessionFactory"></property> </bean> <!--配置事务--> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dynamicDataSource"/> </bean> <!--开启注解事务--> <tx:annotation-driven transaction-manager="transactionManager"/> <import resource="classpath:datasource-master.xml"/> <import resource="classpath:datasource-slave-one.xml"/> <import resource="classpath:datasource-slave-two.xml"/>
5、新建DBContextHolder，DBType为动态设置数据库的util
package org.hope.lee.utils; public class DBType { public final static String DB_TYPE_MASTER = "db_master"; public final static String DB_TYPE_SLAVE_ONE = "db_slave_one"; public final static String DB_TYPE_SLAVE_TWO = "db_slave_two"; }
package org.hope.lee.utils; public class DBContextHolder { private static ThreadLocal<String> contextHolder = new ThreadLocal<String>(); public static String getDBType() { String db = contextHolder.get(); if(db == null) { db = DBType.DB_TYPE_MASTER; //默认是master库 } return db; } public static void setDBType(String dbType) { contextHolder.set(dbType); } public static void clearDBType() { contextHolder.remove(); } }
6、继承Spring的 AbstractRoutingDataSource 来动态的进行数据库路由
package org.hope.lee.utils; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DBContextHolder.getDBType(); } }
7、创建三个数据库master、slave-one、slave-two。三个库建同一张user表进行测试。
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
8、写mybatis的dao层,service层,model层
package org.hope.lee.model; public class User { private int id; private String name; setters()&getters() }
package org.hope.lee.dao; import org.hope.lee.model.User; import org.springframework.stereotype.Repository; @Repository public interface UserMapper { void insert(User user); User selectOne(int id); }
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="org.hope.lee.dao.UserMapper"> <resultMap id="usersResultMap" type="User"> <id column="id" property="id" javaType="Integer" /> <result column="name" property="name" /> </resultMap> <insert id="insert" useGeneratedKeys="true" parameterType="User"> INSERT INTO `user`(name) VALUES(#{name, jdbcType=VARCHAR}); </insert> <select id="selectOne" resultMap="usersResultMap" parameterType="int" > SELECT id, name FROM `user` WHERE id=#{id, jdbcType=INTEGER} </select> </mapper>
package org.hope.lee.service; import org.hope.lee.dao.UserMapper; import org.hope.lee.model.User; import org.hope.lee.utils.DBContextHolder; import org.hope.lee.utils.DBType; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; @Service("userService") public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; public void addUser(User user) { //设置数据库 DBContextHolder.setDBType(DBType.DB_TYPE_MASTER); userMapper.insert(user); } public User getUserById(int id) { //设置数据库，单元测试的时候自己手动修改一下，看看效果
DBContextHolder.setDBType(DBType.DB_TYPE_SLAVE_ONE); return userMapper.selectOne(id); } }
9、单元测试。
import org.hope.lee.model.User; import org.hope.lee.service.UserService; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = {"classpath*:applicationContext.xml"}) public class UserServiceTest { @Autowired private UserService userService; @Test public void addUserTest() { User user = new User(); user.setName("马云"); userService.addUser(user); } @Test public void getUserOneTest() { int id = 1; User u = userService.getUserById(id); System.out.println(u.getName()); } }
10、在service层修改 DBContextHolder.setDBType()来看看效果。
四、遇到的问题：
1、遇到Spring的PropertyPlaceholderConfigurer不起效果，在数据源配置的${jdbc.driver}中获取不到jdbc.properties中的值。
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath*:jdbc.properties</value> </list> </property> <property name="fileEncoding" value="UTF-8" /> <property name= "ignoreResourceNotFound" value="false"/> </bean>
解决：
<!--
原来id的名称是sqlSessionFactory,但是在spring里使用org.mybatis.spring.mapper.MapperScannerConfigurer 进行自动扫描的时候，设置了sqlSessionFactory 的话，他会优先于PropertyPlaceholderConfigurer执行。 从而导致PropertyPlaceholderConfigurer失效， 这时在xml中用${url}、${username}、${password}等这样之类的表达式， 将无法获取到properties文件里的内容。
--> <bean id="dynamicsqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dynamicDataSource"/> <property name="mapperLocations"> <array> <value>classpath:mappers/*Mapper.xml</value> </array> </property> <property name="configLocation" value="classpath:mybatis-config.xml"/> <property name="typeAliasesPackage" value="org.hope.lee.model"/> </bean>
六、还有一种方式是使用mysql自带的replicationDriver来实现读写分离。大家自己也可以试试
http://blog.csdn.net/lixiucheng005/article/details/17391857
https://gitee.com/huayicompany/mybatis-learn/tree/master/separated-read-write
参考：
[1] 博客，http://blog.csdn.net/xtj332/article/details/43953699
[2] 博客，http://blog.csdn.net/keda8997110/article/details/16827215
