[Java知识库]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.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

<?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/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">

    <!-- 自动扫描，将类自动注册为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 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

<?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 {

    @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();
    }
}

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 {

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

下载源码