数据库准备
1、准备2个数据库,本例以mysql为例
在第一个数据库新建表user
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES (1, '张三');
INSERT INTO `user` VALUES (2, '李四');
INSERT INTO `user` VALUES (3, '王五');
COMMIT;
在第二个数据库中,新建表dog
-- ----------------------------
-- Table structure for dog
-- ----------------------------
DROP TABLE IF EXISTS `dog`;
CREATE TABLE `dog` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`dog_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '狗名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ----------------------------
-- Records of dog
-- ----------------------------
BEGIN;
INSERT INTO `dog` VALUES (1, '旺财');
INSERT INTO `dog` VALUES (2, '二哈');
INSERT INTO `dog` VALUES (3, '大黑');
COMMIT;
项目搭建
1、搭建一个Springmvc + Spring + Mybatis maven项目,
POM文件中引入AOP相关依赖
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0modelVersion>
<groupId>com.examplegroupId>
<artifactId>muldatasourceartifactId>
<version>1.0-SNAPSHOTversion>
<properties>
<spring.version>4.3.7.RELEASEspring.version>
<mybatis.version>3.5.0mybatis.version>
<mybatis-spring.version>2.0.0mybatis-spring.version>
<mysql.version>5.1.32mysql.version>
<c3p0.version>0.9.5.4c3p0.version>
<slf4j-api.version>1.7.5slf4j-api.version>
<logback.version>0.9.30logback.version>
<servlet.version>3.0.1servlet.version>
<jsp-api.version>2.2jsp-api.version>
<jstl.version>1.2jstl.version>
<standard.version>1.1.2standard.version>
<junit.version>3.8.1junit.version>
<jdk.version>1.8jdk.version>
<maven.compiler.plugin.version>2.3.2maven.compiler.plugin.version>
<jackson.version>2.8.8jackson.version>
properties>
<dependencies>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-coreartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-beansartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-contextartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-expressionartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>com.fasterxml.jackson.coregroupId>
<artifactId>jackson-databindartifactId>
<version>${jackson.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-aopartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.aspectjgroupId>
<artifactId>aspectjrtartifactId>
<version>1.9.2version>
dependency>
<dependency>
<groupId>org.aspectjgroupId>
<artifactId>aspectjweaverartifactId>
<version>1.9.2version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-webartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-webmvcartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-txartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-ormartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.springframeworkgroupId>
<artifactId>spring-jdbcartifactId>
<version>${spring.version}version>
dependency>
<dependency>
<groupId>org.mybatisgroupId>
<artifactId>mybatisartifactId>
<version>${mybatis.version}version>
dependency>
<dependency>
<groupId>org.mybatisgroupId>
<artifactId>mybatis-springartifactId>
<version>${mybatis-spring.version}version>
dependency>
<dependency>
<groupId>mysqlgroupId>
<artifactId>mysql-connector-javaartifactId>
<version>${mysql.version}version>
dependency>
<dependency>
<groupId>com.mchangegroupId>
<artifactId>c3p0artifactId>
<version>${c3p0.version}version>
dependency>
<dependency>
<groupId>org.slf4jgroupId>
<artifactId>slf4j-apiartifactId>
<version>${slf4j-api.version}version>
<type>jartype>
<scope>compilescope>
dependency>
<dependency>
<groupId>ch.qos.logbackgroupId>
<artifactId>logback-coreartifactId>
<version>${logback.version}version>
<type>jartype>
dependency>
<dependency>
<groupId>ch.qos.logbackgroupId>
<artifactId>logback-classicartifactId>
<version>${logback.version}version>
<type>jartype>
dependency>
<dependency>
<groupId>ch.qos.logbackgroupId>
<artifactId>logback-accessartifactId>
<version>${logback.version}version>
dependency>
<dependency>
<groupId>javax.servletgroupId>
<artifactId>javax.servlet-apiartifactId>
<version>${servlet.version}version>
<scope>providedscope>
dependency>
<dependency>
<groupId>javax.servlet.jspgroupId>
<artifactId>jsp-apiartifactId>
<version>${jsp-api.version}version>
<scope>providedscope>
dependency>
<dependency>
<groupId>javax.servletgroupId>
<artifactId>jstlartifactId>
<version>${jstl.version}version>
dependency>
<dependency>
<groupId>taglibsgroupId>
<artifactId>standardartifactId>
<version>${standard.version}version>
dependency>
<dependency>
<groupId>junitgroupId>
<artifactId>junitartifactId>
<version>${junit.version}version>
<scope>testscope>
dependency>
dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.pluginsgroupId>
<artifactId>maven-compiler-pluginartifactId>
<version>${maven.compiler.plugin.version}version>
<configuration>
<source>${jdk.version}source>
<target>${jdk.version}target>
configuration>
plugin>
plugins>
<finalName>test_spring_mybatisfinalName>
build>
project>
项目目录:
配置文件jdbc.properties
datasource1.jdbc.driver=com.mysql.jdbc.Driver
datasource1.jdbc.url=jdbc:mysql://127.0.0.1:3306/test_mybatis?useUnicode=true&characterEncoding=utf8
datasource1.jdbc.username=root
datasource1.jdbc.password=root
datasource2.jdbc.driver=com.mysql.jdbc.Driver
datasource2.jdbc.url=jdbc:mysql://127.0.0.1:3306/test_mybatis2?useUnicode=true&characterEncoding=utf8
datasource2.jdbc.username=root
datasource2.jdbc.password=root
编辑一个扩展AbstractRoutingDataSource类,DynamicDataSource.java
package com.test.datasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 动态数据源(依赖于spring)
* @author chenheng
* @date 2019-08-03 17:27:35
*
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceHolder.getDataSource();
}
}
封装一个的对数据源进行操作的类,DataSourceHolder.java
package com.test.datasource;
public class DataSourceHolder {
// 线程本地环境
private static final ThreadLocal<String> dataSources = new ThreadLocal<String>();
// 设置数据源
public static void setDataSource(String customerType) {
dataSources.set(customerType);
}
// 获取数据源
public static String getDataSource() {
return (String) dataSources.get();
}
// 清除数据源
public static void clearDataSource() {
dataSources.remove();
}
}
我们可以应用spring aop来设置,把配置的数据源类型都设置成为注解标签,在service层中需要切换数据源的方法上,写上注解标签,调用相应方法切换数据源咯(就跟你设置事务一样)
@TargetDataSource(name= TargetDataSource.SLAVE)
public List<Dog> getAll(){
return dogDao.getAll();
}
编辑注解标签TargetDataSource.java
package com.test.annotation;
import java.lang.annotation.*;
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
String name() default TargetDataSource.MASTER;
public static String MASTER = "dataSource1";
public static String SLAVE = "dataSource2";
}
编辑切面的Bean,DataSourceExchange.java
package com.test.datasource;
import java.lang.reflect.Method;
import org.springframework.aop.AfterReturningAdvice;
import org.springframework.aop.MethodBeforeAdvice;
import com.test.annotation.TargetDataSource;
public class DataSourceExchange implements MethodBeforeAdvice, AfterReturningAdvice {
@Override
public void afterReturning(Object returnValue, Method method, Object[] args, Object target) throws Throwable {
DataSourceHolder.clearDataSource();
}
@Override
public void before(Method method, Object[] args, Object target) throws Throwable {
// 这里TargetDataSource是自定义的注解
if (method.isAnnotationPresent(TargetDataSource.class)) {
TargetDataSource datasource = method.getAnnotation(TargetDataSource.class);
DataSourceHolder.setDataSource(datasource.name());
} else {
if(target.getClass().isAnnotationPresent(TargetDataSource.class))
{
TargetDataSource datasource = target.getClass().getAnnotation(TargetDataSource.class);
DataSourceHolder.setDataSource(datasource.name());
}
}
}
}
配置文件spring-config.xml
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://mybatis.org/schema/mybatis-spring
http://mybatis.org/schema/mybatis-spring.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd">
<context:component-scan base-package="com.test">
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
context:component-scan>
<context:property-placeholder location="classpath:jdbc.properties" />
<bean id="dataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="${datasource1.jdbc.url}">property>
<property name="driverClass" value="${datasource1.jdbc.driver}">property>
<property name="user" value="${datasource1.jdbc.username}">property>
<property name="password" value="${datasource1.jdbc.password}">property>
bean>
<bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="${datasource2.jdbc.url}">property>
<property name="driverClass" value="${datasource2.jdbc.driver}">property>
<property name="user" value="${datasource2.jdbc.username}">property>
<property name="password" value="${datasource2.jdbc.password}">property>
bean>
<bean id="dataSource" class="com.test.datasource.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="dataSource1" value-ref="dataSource1">entry>
<entry key="dataSource2" value-ref="dataSource2">entry>
map>
property>
<property name="defaultTargetDataSource" ref="dataSource1"/>
bean>
<bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource">property>
bean>
<tx:annotation-driven transaction-manager="dataSourceTransactionManager" order="2"/>
<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource">property>
<property name="configLocation" value="classpath:mybatis-config.xml">property>
<property name="mapperLocations" value="classpath:mybatis/mapper/*.xml">property>
bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactoryBean">constructor-arg>
<constructor-arg name="executorType" value="BATCH">constructor-arg>
bean>
<mybatis-spring:scan base-package="com.test.dao"/>
<bean id="dataSourceExchange" class="com.test.datasource.DataSourceExchange"/>
<aop:config>
<aop:pointcut id="servicePointcut" expression="execution(* com.test.service..*(..))"/>
<aop:advisor advice-ref="dataSourceExchange" pointcut-ref="servicePointcut" order="1"/>
aop:config>
beans>
注意:Spring中的事务是通过aop来实现的,当我们自己写aop拦截的时候,会遇到跟spring的事务aop执行的先后顺序问题,比如说动态切换数据源的问题,如果事务在前,数据源切换在后,会导致数据源切换失效,所以就用到了Order(排序)这个关键字
<aop:advisor advice-ref="dataSourceExchange" pointcut-ref="servicePointcut" order="1"/>
<tx:annotation-driven transaction-manager="dataSourceTransactionManager" order="2"/>
在service上加上注解即可使用
@TargetDataSource(name= TargetDataSource.SLAVE)
public List<Dog> getAll(){
return dogDao.getAll();
}
Dao层
UserDao.java
package com.test.dao;
import com.test.bean.User;
import java.util.List;
public interface UserDao {
List<User> getAll();
}
DogDao.java
package com.test.dao;
import com.test.annotation.TargetDataSource;
import com.test.bean.Dog;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface DogDao {
List<Dog> getAll();
@Select("INSERT INTO dog (dog_name) VALUES (#{dogName})")
void save(Dog dog);
}
Xml文件
mybatis-config.xml,路径classpath:mybatis/mybatis-config.xml
DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
settings>
configuration>
UserMapper.xml,文件路径classpath:mybatis/mapper/UserMapper.xml
DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.dao.UserDao">
<resultMap type="com.test.bean.User" id="user">
<id property="id" column="id"/>
<result property="name" column="name"/>
resultMap>
<select id="getAll" resultMap="user">
select * from user
select>
mapper>
DogMapper.xml,文件路径classpath:mybatis/mapper/DogMapper.xml
DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.dao.DogDao">
<select id="getAll" resultType="com.test.bean.Dog">
select * from dog
select>
mapper>
Servcie层
DogService.java
package com.test.service;
import com.test.annotation.TargetDataSource;
import com.test.bean.User;
import com.test.dao.DogDao;
import com.test.bean.Dog;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class DogService {
@Autowired
DogDao dogDao;
/**
* 在springboot中已经默认对jpa、jdbc、mybatis开启了事事务,引入它们依赖的时候,事物就默认开启。
* springboot开启事务很简单,只需要一个注解@Transactional 就可以了。
* @Transactional可以在在方法上和类上使用。
* @return
*/
@Transactional(value = "dataSourceTransactionManager")
@TargetDataSource(name= TargetDataSource.SLAVE)
public Integer save() {
Dog dog = new Dog();
dog.setDogName("大黄");
dogDao.save(dog);
return 1/0;
}
@TargetDataSource(name= TargetDataSource.SLAVE)
public List<Dog> getAll(){
return dogDao.getAll();
}
}
UserService.java
package com.test.service;
import com.test.annotation.TargetDataSource;
import com.test.bean.User;
import com.test.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
UserDao userDao;
@TargetDataSource(name= TargetDataSource.MASTER)
public List<User> getAll(){
return userDao.getAll();
}
}
package com.test.controller;
import com.test.bean.Dog;
import com.test.bean.User;
import com.test.service.DogService;
import com.test.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class TestController {
@Autowired
UserService userService;
@Autowired
DogService dogService;
@RequestMapping("users.html")
public List<User> users() {
return userService.getAll();
}
@RequestMapping("dogs.html")
public List<Dog> dogs() {
return dogService.getAll();
}
@RequestMapping("/dog/save.html")
public Integer save() {
return dogService.save();
}
}
实例
User.java
package com.test.bean;
public class User {
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
Dog.java
package com.test.bean;
public class Dog {
private Integer id;
private String dogName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDogName() {
return dogName;
}
public void setDogName(String dogName) {
this.dogName = dogName;
}
}
项目测试
1、启动项使用 http://localhost:8080/users.html地址访问获取所有用户,由此说明masterDataSource能正常使用
2、使用地址 http://localhost:8080/dogs.html 访问获取所有狗,由此说明slaveDataSource能正常使用
3、使用地址 http://localhost:8080/dog/save.html,新增数据,查看数据库中数据是否增加,未增加,判断说明事务已生效。