SpringBoot-整合Mybatis-Druid-多数据源

有的时候我们的项目可能需要从不同的数据源获取或者操作数据，那么这个时候就需要配置多数据源，接下来我们整合下Mybatis+Druid实现多数据源，本实例代码08-MultipleDataSource，废话不多说，开搞...

pom文件

<?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/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.perfree</groupId>
    <artifactId>multipledatasource</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>multipledatasource</name>
    <description>SpringBoot-MultipleDataSource-Demo</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.10</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

关键依赖：

<!-- Mybatis依赖 -->
<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>1.3.2</version>
</dependency>
<!-- Mysql驱动 -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.39</version>
</dependency>
<!-- Druid依赖 -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.0.10</version>
</dependency>

项目一览

其中datasource和mapper(包括映射文件)包下分别都配置了两个数据源，其他地方基本没啥变化，接着往下看

准备两个数据库

因为就是用来进行测试的，就随便准备了两个数据库，test1数据库有一张user1表，test2有一张user2表

下边是sql语句，数据库自己建吧~ user1：

CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user1
-- ----------------------------
INSERT INTO `user1` VALUES ('1', '张三', '22');

user2:

CREATE TABLE `user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user2
-- ----------------------------
INSERT INTO `user2` VALUES ('1', 'lucy', '21');

实体类

User：

package com.perfree.multipledatasource.pojo;

/**
 * @ClassName User
 * @Description TODO(user实体类)
 * @Author Perfree
 * @Date 2018/10/3 21:19
 * @Version 1.0
 */
public class User {
    private Integer id;
    private String name;
    private Integer age;

    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;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

Mapper接口

这里要注意了，我在这里进行了分包，结构如下：

User1Mapper代码：

package com.perfree.multipledatasource.mapper.test1;

import com.perfree.multipledatasource.pojo.User;
import org.apache.ibatis.annotations.Mapper;

/**
 * User1 Mapper接口
 */
@Mapper
public interface User1Mapper {
    //根据用户id查询用户信息
    User getUserById(int id);
}

User2Mapper代码:

package com.perfree.multipledatasource.mapper.test2;

import com.perfree.multipledatasource.pojo.User;
import org.apache.ibatis.annotations.Mapper;

/**
 * User2 Mapper接口
 */
@Mapper
public interface User2Mapper {

    //根据用户id查询用户信息
    User getUserById(int id);
}

Mapper映射文件

注意结构：

User1Mapper代码：

<?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="com.perfree.multipledatasource.mapper.test1.User1Mapper">
    <!-- 根据用户id查询用户信息 -->
    <select id="getUserById" parameterType="int" resultType="com.perfree.multipledatasource.pojo.User">
        SELECT
            id,
            NAME,
            age
        FROM
            USER1
        WHERE
            id = #{id}
    </select>
</mapper>

User2Mapper代码：

<?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="com.perfree.multipledatasource.mapper.test2.User2Mapper">
    <!-- 根据用户id查询用户信息 -->
    <select id="getUserById" parameterType="int" resultType="com.perfree.multipledatasource.pojo.User">
        SELECT
            id,
            NAME,
            age
        FROM
            USER2
        WHERE
            id = #{id}
    </select>
</mapper>

Service接口

UserService代码：

package com.perfree.multipledatasource.service;

import com.perfree.multipledatasource.pojo.User;

/**
 * UserService接口
 */
public interface UserService {

    //从test1数据库中根据id查找用户信息
    User getUserByIdAndTest1(int id);

    //从test2数据库中根据id查找用户信息
    User getUserByIdAndTest2(int id);
}

Service实现类

UserServiceImpl代码:

package com.perfree.multipledatasource.service.impl;

import com.perfree.multipledatasource.mapper.test1.User1Mapper;
import com.perfree.multipledatasource.mapper.test2.User2Mapper;
import com.perfree.multipledatasource.pojo.User;
import com.perfree.multipledatasource.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * UserService实现类
 */
@Service
public class UserServiceImpl implements UserService {

    //注入User1Mapper
    @Autowired
    private User1Mapper user1Mapper;

    //注入User2Mapper
    @Autowired
    private User2Mapper user2Mapper;

    @Override
    public User getUserByIdAndTest1(int id) {
        User user = user1Mapper.getUserById(id);
        return user;
    }

    @Override
    public User getUserByIdAndTest2(int id) {
        User user = user2Mapper.getUserById(id);
        return user;
    }
}

Controller

MultipleDataSourceController代码：

package com.perfree.multipledatasource.controller;

import com.perfree.multipledatasource.pojo.User;
import com.perfree.multipledatasource.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * MultipleDataSourceController
 */
@RestController
public class MultipleDataSourceController {

    @Autowired
    private UserService userService;

    //从数据库test1根据用户id查询用户信息
    @RequestMapping("/test1/getUser")
    public String getUserByIdAndTest1(){
        User user = userService.getUserByIdAndTest1(1);
        return user.getName();
    }

    //从数据库test2根据用户id查询用户信息
    @RequestMapping("/test2/getUser")
    public String getUserByIdAndTest2(){
        User user = userService.getUserByIdAndTest2(1);
        return user.getName();
    }

}

application配置文件

配置数据源，前缀无特定格式，随意写

## 数据源1对应test1数据库
test1.datasource.url=jdbc:mysql://localhost:3306/test1?characterEncoding=utf8
test1.datasource.username=root
test1.datasource.password=215521
test1.datasource.driver-class-name=com.mysql.jdbc.Driver

## 数据源2对应test2数据库
test2.datasource.url=jdbc:mysql://localhost:3306/test2?characterEncoding=utf8
test2.datasource.username=root
test2.datasource.password=215521
test2.datasource.driver-class-name=com.mysql.jdbc.Driver

datasource

结构： 这一步也是最关键的一步，对两条数据源进行了配置，解释基本都写到注释里了，所以直接上代码 DataSource1Config代码：

package com.perfree.multipledatasource.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * 配置test1数据源
 */
@Configuration// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = DataSource1Config.PACKAGE, sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSource1Config {

    // 配置范围，精确到 test1 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.perfree.multipledatasource.mapper.test1";
    static final String MAPPER_LOCATION = "classpath:mapper/test1/*.xml";

    //下面这些@Value都是从application.properties读取设置好的数据源
    @Value("${test1.datasource.url}")
    private String url;

    @Value("${test1.datasource.username}")
    private String user;

    @Value("${test1.datasource.password}")
    private String password;

    @Value("${test1.datasource.driver-class-name}")
    private String driverClass;

    //Druid连接池
    @Bean(name = "test1DataSource")
    @Primary
    public DataSource test1DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    //TransactionManager
    @Bean(name = "test1TransactionManager")
    @Primary
    public DataSourceTransactionManager test1TransactionManager() {
        return new DataSourceTransactionManager(test1DataSource());
    }

    //SqlSessionFactory
    @Bean(name = "test1SqlSessionFactory")
    @Primary
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource test1DataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(test1DataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(DataSource1Config.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

DataSource2Config代码：

package com.perfree.multipledatasource.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * Test2数据源配置
 */
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = DataSource2Config.PACKAGE, sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSource2Config {

    // 配置范围，精确到 test2 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.perfree.multipledatasource.mapper.test2";
    static final String MAPPER_LOCATION = "classpath:mapper/test2/*.xml";

    //下面这些@Value都是从application.properties读取设置好的数据源
    @Value("${test2.datasource.url}")
    private String url;

    @Value("${test2.datasource.username}")
    private String user;

    @Value("${test2.datasource.password}")
    private String password;

    @Value("${test2.datasource.driver-class-name}")
    private String driverClass;

    //Druid连接池
    @Bean(name = "test2DataSource")
    public DataSource test2DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    //TransactionManager
    @Bean(name = "test2TransactionManager")
    public DataSourceTransactionManager test2TransactionManager() {
        return new DataSourceTransactionManager(test2DataSource());
    }

    //SqlSessionFactory
    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource test2DataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(test2DataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(DataSource2Config.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

其实mybatis原生xml写多了，这些一看就明白了，其中特别注意DataSource1Config加入了@Primary而DataSource2Config没有，这是因为必须要有一个默认配置，否则运行会报错！

运行

先看数据库数据 Test1.user1：

Test2.user2：

接下来运行项目，启动成功后我们先测试取test1数据库中的值：

ok，没问题，接着测试取test2数据库中的值：

大功告成~