spring+SpringMVC+MyBatis之配置多数据源

数据库准备
　　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相关依赖

<?xml version="1.0" encoding="UTF-8"?>
<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.0</modelVersion><groupId>com.example</groupId>
<artifactId>muldatasource</artifactId>
<version>1.0-SNAPSHOT</version><!-- 定义maven变量 -->
<properties><!-- spring --><spring.version>4.3.7.RELEASE</spring.version><!-- Mybatis --><mybatis.version>3.5.0</mybatis.version><!-- Mybatis 整合 Spring --><mybatis-spring.version>2.0.0</mybatis-spring.version><!-- mysql --><mysql.version>5.1.32</mysql.version><!-- c3p0 连接池 --><c3p0.version>0.9.5.4</c3p0.version><!-- logback --><slf4j-api.version>1.7.5</slf4j-api.version><logback.version>0.9.30</logback.version><!-- Servlet --><servlet.version>3.0.1</servlet.version><jsp-api.version>2.2</jsp-api.version><!-- jstl --><jstl.version>1.2</jstl.version><standard.version>1.1.2</standard.version><!-- test junit --><junit.version>3.8.1</junit.version><!-- jdk --><jdk.version>1.8</jdk.version><maven.compiler.plugin.version>2.3.2</maven.compiler.plugin.version><jackson.version>2.8.8</jackson.version>
</properties><dependencies><!-- Spring IOC 核心容器 --><dependency><groupId>org.springframework</groupId><artifactId>spring-core</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-beans</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-expression</artifactId><version>${spring.version}</version></dependency><dependency><groupId>com.fasterxml.jackson.core</groupId><artifactId>jackson-databind</artifactId><version>${jackson.version}</version></dependency><!-- Spring AOP 切面 模块 --><dependency><groupId>org.springframework</groupId><artifactId>spring-aop</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.aspectj</groupId><artifactId>aspectjrt</artifactId><version>1.9.2</version></dependency><dependency><groupId>org.aspectj</groupId><artifactId>aspectjweaver</artifactId><version>1.9.2</version></dependency><!-- Spring WEB MVC 模块 --><dependency><groupId>org.springframework</groupId><artifactId>spring-web</artifactId><version>${spring.version}</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-webmvc</artifactId><version>${spring.version}</version></dependency><!-- Spring 事物 模块 --><dependency><groupId>org.springframework</groupId><artifactId>spring-tx</artifactId><version>${spring.version}</version></dependency><!-- Spring ORM 对象关系映射 模块 --><dependency><groupId>org.springframework</groupId><artifactId>spring-orm</artifactId><version>${spring.version}</version></dependency><!-- Spring JDBC 模块 --><dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>${spring.version}</version></dependency><!-- Mybatis --><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>${mybatis.version}</version></dependency><!-- Mybatis 整合 Spring --><dependency><groupId>org.mybatis</groupId><artifactId>mybatis-spring</artifactId><version>${mybatis-spring.version}</version></dependency><!-- mysql --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>${mysql.version}</version></dependency><!-- c3p0 连接池 --><!-- https://mvnrepository.com/artifact/c3p0/c3p0 --><dependency><groupId>com.mchange</groupId><artifactId>c3p0</artifactId><version>${c3p0.version}</version></dependency><!-- logback --><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>${slf4j-api.version}</version><type>jar</type><scope>compile</scope></dependency><dependency><groupId>ch.qos.logback</groupId><artifactId>logback-core</artifactId><version>${logback.version}</version><type>jar</type></dependency><dependency><groupId>ch.qos.logback</groupId><artifactId>logback-classic</artifactId><version>${logback.version}</version><type>jar</type></dependency><dependency><groupId>ch.qos.logback</groupId><artifactId>logback-access</artifactId><version>${logback.version}</version></dependency><!-- Servlet --><dependency><groupId>javax.servlet</groupId><artifactId>javax.servlet-api</artifactId><version>${servlet.version}</version><scope>provided</scope></dependency><dependency><groupId>javax.servlet.jsp</groupId><artifactId>jsp-api</artifactId><version>${jsp-api.version}</version><scope>provided</scope></dependency><!-- jstl --><dependency><groupId>javax.servlet</groupId><artifactId>jstl</artifactId><version>${jstl.version}</version></dependency><dependency><groupId>taglibs</groupId><artifactId>standard</artifactId><version>${standard.version}</version></dependency><!-- test --><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>${junit.version}</version><scope>test</scope></dependency></dependencies><build><plugins><!-- define the project compile level --><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>${maven.compiler.plugin.version}</version><configuration><source>${jdk.version}</source><target>${jdk.version}</target></configuration></plugin></plugins><finalName>test_spring_mybatis</finalName>
</build>
</project>

项目目录：
在这里插入图片描述
配置文件jdbc.properties

datasource1.jdbc.driver=com.mysql.jdbc.Driverdatasource1.jdbc.url=jdbc:mysql://127.0.0.1:3306/test_mybatis?useUnicode=true&characterEncoding=utf8datasource1.jdbc.username=rootdatasource1.jdbc.password=rootdatasource2.jdbc.driver=com.mysql.jdbc.Driverdatasource2.jdbc.url=jdbc:mysql://127.0.0.1:3306/test_mybatis2?useUnicode=true&characterEncoding=utf8datasource2.jdbc.username=rootdatasource2.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 {@Overrideprotected 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 {@Overridepublic void afterReturning(Object returnValue, Method method, Object[] args, Object target) throws Throwable {DataSourceHolder.clearDataSource();}@Overridepublic 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

<?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: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/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://mybatis.org/schema/mybatis-springhttp://mybatis.org/schema/mybatis-spring.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-4.0.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context-4.0.xsd"><!-- 自动扫描，将类自动注册为bean，@Controller注解在spring mvc中扫描 --><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><!-- 数据源：Spring用来控制业务逻辑。数据源、事务控制、aop --><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><!-- spring事务管理 --><bean id="dataSourceTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"><property name="dataSource" ref="dataSource"></property></bean><!-- 开启基于注解的事务 --><tx:annotation-driven transaction-manager="dataSourceTransactionManager" order="2"/><!--整合mybatis目的：1、spring管理所有组件。mapper的实现类。service==>dao   @Autowired:自动注入mapper；2、spring用来管理事务，spring声明式事务--><!--创建出SqlSessionFactory对象  --><bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="dataSource"></property><!-- configLocation指定全局配置文件的位置 --><property name="configLocation" value="classpath:mybatis-config.xml"></property><!--mapperLocations: 指定mapper文件的位置--><property name="mapperLocations" value="classpath:mybatis/mapper/*.xml"></property></bean><!--配置一个可以进行批量执行的sqlSession  --><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><!-- 扫描所有的mapper接口的实现，让这些mapper能够自动注入；base-package：指定mapper接口的包名--><mybatis-spring:scan base-package="com.test.dao"/><!-- 配置切面的Bean --><bean id="dataSourceExchange" class="com.test.datasource.DataSourceExchange"/><!-- 配置AOP --><aop:config><!-- 配置切点表达式  --><aop:pointcut id="servicePointcut" expression="execution(* com.test.service..*(..))"/><!-- 关键配置，切换数据源一定要比持久层代码更先执行（事务也算持久层代码） <aop:advisor advice-ref="txAdvice" pointcut-ref="service" order="2"/> --><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

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//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

<?xml version="1.0" encoding="UTF-8"?>
<!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

<?xml version="1.0" encoding="UTF-8"?>
<!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 {@AutowiredDogDao 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 {@AutowiredUserDao userDao;@TargetDataSource(name= TargetDataSource.MASTER)public List<User> getAll(){return userDao.getAll();}
}

Controller层

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 {@AutowiredUserService userService;@AutowiredDogService 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，新增数据，查看数据库中数据是否增加，未增加，判断说明事务已生效。