Spring Boot 应用系列 3 -- Spring Boot 2 整合MyBatis和Druid，多数据源

本文演示多数据源（MySQL+SQL Server）的配置，并且我引入了分页插件pagehelper。

1. 项目结构

（1）db.properties存储数据源和连接池配置。

（2）两个数据源的mapper配置分别在src/main/resources下面的datasource1和datasource2里面。

2. pom.xml

需要在dependencies节点中添加：

<!-- MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.12</version>
</dependency>
<!-- MySQL end -->

<!-- SQL Server -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
</dependency>
<!-- SQL Server end -->

<!-- Connection Pool -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>
<!-- Connection Pool end -->

<!-- MyBatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<!-- MyBatis end -->

<!-- Page Helper -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.5</version>
</dependency>
<!-- Page Helper end -->

3. properties配置文件

我们把主程序配置文件application.properties和数据库配置文件分开，这样可使application.properties不至于臃肿。

(1) application.properties

server.port=9008
spring.application.name=devutility-test-database-mybatis-springboot

#Configuration for druid
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin

Druid ui的配置也放在里面，可通过http://localhost:9008/druid来访问。

(2) db.properties

#Data source 1
db1.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
db1.sqlserver.url=${DB1_URL:jdbc:sqlserver://127.0.0.1:1433;DatabaseName=MyTestDb1}
db1.sqlserver.username=${DB1_UID:tester}
db1.sqlserver.password=${DB1_PWD:tester}
db1.sqlserver.initial-size=1
db1.sqlserver.min-idle=1
db1.sqlserver.max-active=20
db1.sqlserver.max-wait=60000
db1.sqlserver.time-between-eviction-runs-millis=60000
db1.sqlserver.min-evictable-idle-time-millis=300000
db1.sqlserver.validation-query=select 1
db1.sqlserver.test-on-borrow=true
db1.sqlserver.test-While-Idle=true
db1.sqlserver.test-on-return=false
db1.sqlserver.pool-prepared-statements=false
db1.sqlserver.max-pool-prepared-statement-per-connection-size=20

db1.sqlserver.mybatis.config-location=classpath:datasource1/mybatis-config.xml

db1.sqlserver.filter.stat.enabled=true
db1.sqlserver.filter.stat.db-type=mssql
db1.sqlserver.filter.stat.log-slow-sql=true
db1.sqlserver.filter.stat.slow-sql-millis=200

#Data source 2
db2.mysql.driver-class-name=com.mysql.cj.jdbc.Driver
db2.mysql.url=${DB2_URL:jdbc:mysql://127.0.0.1:3306/Test}?useUnicode=true&useSSL=false
db2.mysql.username=${DB2_UID:tester}
db2.mysql.password=${DB2_PWD:tester}
db2.mysql.initial-size=1
db2.mysql.min-idle=1
db2.mysql.max-active=20
db2.mysql.max-wait=60000
db2.mysql.time-between-eviction-runs-millis=60000
db2.mysql.min-evictable-idle-time-millis=300000
db2.mysql.validation-query=select 1
db2.mysql.test-on-borrow=true
db2.mysql.test-While-Idle=true
db2.mysql.test-on-return=false
db2.mysql.pool-prepared-statements=false
db2.mysql.max-pool-prepared-statement-per-connection-size=20

db2.mysql.mybatis.config-location=classpath:datasource2/mybatis-config.xml

db2.mysql.filter.stat.enabled=true
db2.mysql.filter.stat.db-type=mysql
db2.mysql.filter.stat.log-slow-sql=true
db2.mysql.filter.stat.slow-sql-millis=1000

注意19和44行，我们为两个数据源分别使用mybatis-config.xml来管理它们所属的mapper xml和其他一些配置。

4. JavaConfig

(1) DataSource1

package devutility.test.database.mybatis.springboot.multi.source.config;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.github.pagehelper.PageInterceptor;

@Configuration
@PropertySource("classpath:db.properties")
@MapperScan(basePackages = { "devutility.test.database.mybatis.springboot.multi.source.ds1" }, sqlSessionFactoryRef = "sqlSessionFactory1", sqlSessionTemplateRef = "sqlSessionTemplate1")
public class DataSource1Configuration {
    @Bean
    @ConfigurationProperties("db1.sqlserver")
    public DataSource dataSource1() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("db1.sqlserver")
    public Properties ormProperties1() {
        return new Properties();
    }

    @Bean
    public Interceptor pageHelperInterceptor1() {
        Properties properties = new Properties();
        properties.setProperty("helperDialect", "sqlserver2012");

        Interceptor interceptor = new PageInterceptor();
        interceptor.setProperties(properties);
        return interceptor;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory1(DataSource dataSource1, Properties ormProperties1) throws Exception {
        String configLocation = ormProperties1.getProperty("mybatis.config-location");
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources(configLocation);

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource1);
        sqlSessionFactoryBean.setConfigLocation(resources[0]);
        sqlSessionFactoryBean.setPlugins(new Interceptor[] { pageHelperInterceptor1() });
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate1(SqlSessionFactory sqlSessionFactory1) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory1);
    }

    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager1(DataSource dataSource1) {
        return new DataSourceTransactionManager(dataSource1);
    }
}

(2) DataSource2

package devutility.test.database.mybatis.springboot.multi.source.config;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.github.pagehelper.PageInterceptor;

@Configuration
@PropertySource("classpath:db.properties")
@MapperScan(basePackages = { "devutility.test.database.mybatis.springboot.multi.source.ds2" }, sqlSessionFactoryRef = "sqlSessionFactory2", sqlSessionTemplateRef = "sqlSessionTemplate2")
public class DataSource2Configuration {
    @Bean
    @ConfigurationProperties("db2.mysql")
    public DataSource dataSource2() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("db2.mysql")
    public Properties ormProperties2() {
        return new Properties();
    }

    @Bean
    public Interceptor pageHelperInterceptor2() {
        Properties properties = new Properties();
        properties.setProperty("helperDialect", "mysql");

        Interceptor interceptor = new PageInterceptor();
        interceptor.setProperties(properties);
        return interceptor;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory2(DataSource dataSource2, Properties ormProperties2) throws Exception {
        String configLocation = ormProperties2.getProperty("mybatis.config-location");
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources(configLocation);

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource2);
        sqlSessionFactoryBean.setConfigLocation(resources[0]);
        sqlSessionFactoryBean.setPlugins(new Interceptor[] { pageHelperInterceptor2() });
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate2(SqlSessionFactory sqlSessionFactory2) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory2);
    }

    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager2(DataSource dataSource2) {
        return new DataSourceTransactionManager(dataSource2);
    }
}

注意，两个配置文件均有一个Properties类型的bean，该bean存储着db.properties中的所有配置，但是真正用到的配置只有一个"mybatis.config-location"，它的值就是mybatis-config.xml的存储地址。当然，你也可以删掉ormProperties 这个bean，删掉db.properties的"mybatis.config-location"属性，然后以hardcode的方式在Configuration中配ConfigLocation.

5. MyBatis相关配置

MyBatis的配置主要包括mybatis-config.xml，mapper的xml文件，mapper的接口文件，和实体类，比单数据源多了一个mybatis-config.xml文件的配置。

(1) mybatis-config.xml

在本文中，该文件用来配置数据源相关的xml映射文件和实体类的包，其他配置项可参考MyBatis官网。

<?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="logImpl" value="STDOUT_LOGGING" />
    </settings>
    <typeAliases>
        <package name="devutility.test.database.mybatis.springboot.multi.source.ds2.entities" />
    </typeAliases>
    <mappers>
        <mapper resource="datasource2/mappers/CustomerMapper.xml" />
    </mappers>
</configuration>

datasource1的配置类似，不再赘述。

(2) mapper的xml文件

<?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="devutility.test.database.mybatis.springboot.multi.source.ds2.mappers.CustomerMapper">
    <resultMap id="CustomerMapping" type="Customer">
        <id column="Name1" property="name" />
        <id column="Address1" property="address" />
    </resultMap>
    <select id="get" resultMap="CustomerMapping">
        select * from Customer where id = #{id};
    </select>
    <select id="list" resultMap="CustomerMapping">
        select * from Customer where Name1 is not null order by Created desc
    </select>
</mapper>

(3) mapper的接口文件

package devutility.test.database.mybatis.springboot.multi.source.ds2.mappers;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import devutility.test.database.mybatis.springboot.multi.source.ds2.entities.Customer;

@Mapper
public interface CustomerMapper {
    Customer get(long id);

    List<Customer> list();
}

(4) 实体类

package devutility.test.database.mybatis.springboot.multi.source.ds2.entities;

public class Customer extends BaseEntity {
    private long id;
    private String name;
    private String address;
    private String city;
    private String state;
    private int zip;
    private String phone;
    private String email;

6. 应用

package devutility.test.database.mybatis.springboot.multi.source.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.github.pagehelper.PageHelper;

import devutility.test.database.mybatis.springboot.multi.source.ds2.entities.Customer;
import devutility.test.database.mybatis.springboot.multi.source.ds2.mappers.CustomerMapper;

@RestController
@RequestMapping("/ds2")
public class Ds2Controller {
    @Autowired
    private CustomerMapper customerMapper;

    @RequestMapping("/customer")
    public Customer customer(long id) {
        return customerMapper.get(id);
    }

    @RequestMapping("customers-page")
    public List<Customer> customersPage(int page) {
        return PageHelper.startPage(page, 10).doSelectPage(() -> customerMapper.list());
    }
}

Demo代码