Spring+MyBatis 多数据源配置和切换

环境

两台 MySQL 数据库（属于 master-slave 主从关系），基于 Java8，Spring4，MyBatis3.2 环境。

maven 依赖配置

<dependencies>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>4.1.5.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-orm</artifactId>
		<version>4.1.5.RELEASE</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-test</artifactId>
		<version>4.1.5.RELEASE</version>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.17</version>
	</dependency>
	<dependency>
		<groupId>org.mybatis</groupId>
		<artifactId>mybatis</artifactId>
		<version>3.2.4</version>
	</dependency>
	<dependency>
		<groupId>org.mybatis</groupId>
		<artifactId>mybatis-spring</artifactId>
		<version>1.2.2</version>
	</dependency>
	<dependency>
		<groupId>c3p0</groupId>
		<artifactId>c3p0</artifactId>
		<version>0.9.1.2</version>
	</dependency>
	<dependency>
		<groupId>org.aspectj</groupId>
		<artifactId>aspectjlib</artifactId>
		<version>1.6.2</version>
	</dependency>
	<dependency>
		<groupId>org.aspectj</groupId>
		<artifactId>aspectjweaver</artifactId>
		<version>1.5.4</version>
	</dependency>
	<dependency>
		<groupId>junit</groupId>
		<artifactId>junit</artifactId>
		<version>4.12</version>
	</dependency>
</dependencies>

jdbc.properties 配置

jdbc.driver = com.mysql.jdbc.Driver

jdbc.key.slave = slave
jdbc.username.slave = root
jdbc.password.slave = root
jdbc.url.slave = jdbc:mysql://192.168.1.104:3306/test_db?autoReconnect=true&useUnicode=true&characterEncoding=utf-8

jdbc.key.master = master
jdbc.username.master = root
jdbc.password.master = root
jdbc.url.master = jdbc:mysql://192.168.1.102:3306/test_db?autoReconnect=true&useUnicode=true&characterEncoding=utf-8

c3p0.initialPoolSize = 10
c3p0.maxIdleTime = 1800
c3p0.maxPoolSize = 60
c3p0.acquireIncrement = 5
c3p0.acquireRetryAttempts = 60
c3p0.acquireRetryDelay = 2000
c3p0.breakAfterAcquireFailure = false
c3p0.autoCommitOnClose = false
c3p0.checkoutTimeout = 30000
c3p0.idleConnectionTestPeriod = 900

spring-datasources.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:tx="http://www.springframework.org/schema/tx" 
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:util="http://www.springframework.org/schema/util"
	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/tx 
	http://www.springframework.org/schema/tx/spring-tx.xsd
	http://www.springframework.org/schema/aop 
	http://www.springframework.org/schema/aop/spring-aop.xsd
	http://www.springframework.org/schema/util
	http://www.springframework.org/schema/util/spring-util.xsd">

	<!-- aspectj 注解 -->
	<aop:aspectj-autoproxy />

	<context:annotation-config />

	<context:component-scan base-package="org.fanlychie" />

	<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>classpath:jdbc.properties</value>
			</list>
		</property>
	</bean>

	<bean id="baseDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass" value="${jdbc.driver}" />
		<property name="initialPoolSize" value="${c3p0.initialPoolSize}" />
		<property name="maxIdleTime" value="${c3p0.maxIdleTime}" />
		<property name="maxPoolSize" value="${c3p0.maxPoolSize}" />
		<property name="acquireIncrement" value="${c3p0.acquireIncrement}" />
		<property name="acquireRetryAttempts" value="${c3p0.acquireRetryAttempts}" />
		<property name="acquireRetryDelay" value="${c3p0.acquireRetryDelay}" />
		<property name="breakAfterAcquireFailure" value="${c3p0.breakAfterAcquireFailure}" />
		<property name="autoCommitOnClose" value="${c3p0.autoCommitOnClose}" />
		<property name="checkoutTimeout" value="${c3p0.checkoutTimeout}" />
		<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}" />
	</bean>

	<!-- slave 从库只读数据源 -->
	<bean id="slaveDataSource" parent="baseDataSource">
		<property name="jdbcUrl" value="${jdbc.url.slave}" />
		<property name="user" value="${jdbc.username.slave}" />
		<property name="password" value="${jdbc.password.slave}" />
	</bean>

	<!-- master 主库读写数据源(主要负责写操作) -->
	<bean id="masterDataSource" parent="baseDataSource">
		<property name="jdbcUrl" value="${jdbc.url.master}" />
		<property name="user" value="${jdbc.username.master}" />
		<property name="password" value="${jdbc.password.master}" />
	</bean>

	<!-- 多数据源自动切换 -->
	<bean id="dataSource" class="org.fanlychie.core.MultipleDataSource">
		<property name="targetDataSources">
			<map key-type="java.lang.String">
				<entry key="${jdbc.key.slave}" value-ref="slaveDataSource" />
				<entry key="${jdbc.key.master}" value-ref="masterDataSource" />
			</map>
		</property>
		<!-- 默认使用的数据源 -->
		<!-- <property name="defaultTargetDataSource" ref="masterDataSource"/> -->
	</bean>

	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="typeAliasesPackage" value="org.fanlychie.entity" />
	</bean>

	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="org.fanlychie.mapper" />
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
	</bean>

	<!-- 配置使用 slave 数据源的业务方法前缀列表 -->
	<util:list id="useSlaveDataSourceMethodPrefix">
		<value>select</value>
	</util:list>

	<!-- 配置使用 master 数据源的业务方法前缀列表 -->
	<util:list id="useMasterDataSourceMethodPrefix">
		<value>insert</value>
		<value>update</value>
		<value>delete</value>
	</util:list>

	<bean class="org.fanlychie.core.MultipleDataSourceAspect" />

	<bean class="org.fanlychie.core.MultipleDataSourceContext" />

</beans>

基于 AbstractRoutingDataSource 和 AOP 实现多数据源自动切换

import org.springframework.beans.factory.annotation.Value;

public class MultipleDataSourceContext {

	public static String slave;
	
	public static String master;

	private static final ThreadLocal<String> context = new ThreadLocal<>();
	
	public static void setKey(String name) {
		context.set(name);
	}
	
	public static String getKey() {
		return context.get();
	}
	
	@Value("${jdbc.key.slave}")
	void setSlave(String slave) {
		MultipleDataSourceContext.slave = slave;
	}

	@Value("${jdbc.key.master}")
	void setMaster(String master) {
		MultipleDataSourceContext.master = master;
	}
	
}

import java.util.List;
import javax.annotation.Resource;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before; 

@Aspect
public class MultipleDataSourceAspect {
	
	@Resource
	private List<String> useSlaveDataSourceMethodPrefix;
	
	@Resource
	private List<String> useMasterDataSourceMethodPrefix;

	@Before("execution(* org.fanlychie.service.*.*(..))")
	public void before(JoinPoint point) {
		String method = point.getSignature().getName();
		boolean useSlave = useSlaveDataSourceMethodPrefix.stream().anyMatch(i -> method.startsWith(i));
		if (useSlave) {
			MultipleDataSourceContext.setKey(MultipleDataSourceContext.slave);
			System.out.println("--- slave ---");
			return ;
		}
		boolean useMaster = useMasterDataSourceMethodPrefix.stream().anyMatch(i -> method.startsWith(i));
		if (useMaster) {
			MultipleDataSourceContext.setKey(MultipleDataSourceContext.master);
			System.out.println("--- master ---");
			return ;
		}
		throw new UnsupportedOperationException("can not match datasource for method '" + method + "'");
	}

}

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class MultipleDataSource extends AbstractRoutingDataSource {

	@Override
	protected Object determineCurrentLookupKey() {
		return MultipleDataSourceContext.getKey();
	}

}

实体类

import java.util.Date;

public class User {

	private Integer id;
	
	private String sex;
	
	private String name;
	
	private Date createTime;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Date getCreateTime() {
		return createTime;
	}

	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", sex=" + sex + ", name=" + name + ", createTime=" + createTime + "]";
	}
	
}

mapper 配置文件

<?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.fanlychie.mapper.UserMapper">

	<resultMap id="UserResultMap" type="org.fanlychie.entity.User">
		<id property="id" column="ID" />
		<result property="sex" column="SEX" />
		<result property="name" column="NAME" />
		<result property="createTime" column="CREATE_TIME" />
	</resultMap>

	<select id="selectList" resultMap="UserResultMap">
		SELECT * FROM USER ORDER BY ID
	</select>
	
	<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="User">
		INSERT INTO USER (ID, NAME, SEX, CREATE_TIME) VALUES (#{id}, #{name}, #{sex}, #{createTime})
	</insert>

</mapper>

mapper 接口

package org.fanlychie.mapper;

import java.util.List;
import org.fanlychie.entity.User;

public interface UserMapper {

	public int insert(User user);
	
	public List<User> selectList();
	
}

service 接口

import java.util.List;
import org.fanlychie.entity.User;

public interface UserService {

	boolean insert(User user);
	
	List<User> selectList();
	
}

service 实现类

import java.util.List;
import org.fanlychie.entity.User;
import org.fanlychie.mapper.UserMapper;
import org.fanlychie.service.UserService;
import org.springframework.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;

@Service
public class UserServiceImpl implements UserService {
	
	@Autowired
	private UserMapper mapper;

	@Override
	public boolean insert(User user) {
		return mapper.insert(user) == 1;
	}

	@Override
	public List<User> selectList() {
		return mapper.selectList();
	}

}

Junit 测试

import java.util.Date;
import org.fanlychie.entity.User;
import org.fanlychie.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("/spring-datasources.xml")
public class JTest {

	@Autowired
	private UserService userService;
	
	@Test
	public void doTest() {
		User user = new User();
		user.setName("李四");
		user.setSex("男");
		user.setCreateTime(new Date());
		boolean success = userService.insert(user);
		if (success) {
			System.out.println("保存数据成功");
		}
		userService.selectList().forEach(u -> System.out.println(u));
	}
	
}

测试结果

--- master ---
保存数据成功
--- slave ---
User [id=1, sex=男, name=张三, createTime=Thu Oct 20 05:04:59 CST 2016]
User [id=2, sex=男, name=李四, createTime=Sat Oct 22 21:27:30 CST 2016]