SpringBoot和Mybatis配置多数据源连接多个数据库

pom.xml

<!-- Spring Boot Mybatis 依赖 -->
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>1.2.0</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.18</version>
</dependency>

application.properties 配置两个数据源配置

# 多数据源配置
#  #以下为新增
#  druid:
#    # 指明是否在从池中取出连接前进行检验,如果检验失败, 则从池中去除连接并尝试取出另一个，
#    #注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串
#    test-on-borrow: false
#    # 指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.
#    #注意: 设置为true后如果要生效,validationQuery参数必须设置为非空字符串
#    test-while-idle: true
#    # 指明是否在归还到池中前进行检验，注意: 设置为true后如果要生效,
#    #validationQuery参数必须设置为非空字符串
#    test-on-return: false
#    # SQL查询,用来验证从连接池取出的连接,在将连接返回给调用者之前.
#    #如果指定,则查询必须是一个SQL SELECT并且必须返回至少一行记录
#    validation-query: select 1

spring:
  datasource:
    druid:
      druid:
        est-on-borrow: true
        test-while-idle: true
        type: com.zaxxer.hikari.HikariDataSource
        hikari:
          auto-commit: false
          connection-timeout: 30000
          idle-timeout: 25000
          login-timeout: 5
          max-lifetime: 30000
          read-only: false
          validation-timeout: 3000
          maximum-pool-size: 15
          minimum-idle: 10
          data-source-properties:
            cachePrepStmts: true
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            useServerPrepStmts: true
            useLocalSessionState: true
            rewriteBatchedStatements: true
            cacheResultSetMetadata: true
            cacheServerConfiguration: true
            elideSetAutoCommits: true
            maintainTimeStats: false



master:
  datasource:
    url: jdbc:mysql://xxxxxx:3306/xxxxx?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
    username: xxx
    password: xxx
    driverClassName: com.mysql.cj.jdbc.Driver

second:
  datasource:
    url: jdbc:mysql://xxxx:3306/xxxxxx?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8&
    username: xxxxx
    password: xxxx
    driverClassName: com.mysql.cj.jdbc.Driver

# mybatis:
  # mapper-locations: classpath:mapper/*/*.xml

数据源配置

• 多数据源配置的时候注意，必须要有一个主数据源，即 MasterDataSourceConfig 配置
  – @Primary 标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean
  优先被考虑。「多数据源配置的时候注意，必须要有一个主数据源，用 @Primary 标志该 Bean
  – @MapperScan 扫描 Mapper 接口并容器管理，包路径精确到 master，为了和下面 cluster 数据源做到精确区分
  – @Value 获取全局配置文件 application.properties 的 kv
  配置,并自动装配sqlSessionFactoryRef 表示定义了 key ，表示一个唯一 SqlSessionFactory 实例

MasterDataSourceConfig的代码：

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
 
    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "org.spring.springboot.dao.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
 
    @Value("${master.datasource.url}")
    private String url;
 
    @Value("${master.datasource.username}")
    private String user;
 
    @Value("${master.datasource.password}")
    private String password;
 
    @Value("${master.datasource.driverClassName}")
    private String driverClass;
 
    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }
 
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }
 
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

第二个数据源SecondDataSourceConfig的配置如下:

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {

    // 精确到 cluster 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.springboot.dao.second";
    static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";

    @Value("${second.datasource.url}")
    private String url;

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

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

    @Value("${second.datasource.driverClassName}")
    private String driverClass;

    @Bean(name = "secondDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("secondDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SecondDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

到此，不同的数据源配置就已经完成，剩下的只需要将将Mybatis的xml文件和DAO层的接口写好，并在Service层注入，直接使用就行。

Service层的代码：

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;
    @Autowired
    private SchoolDao schoolDao;

    public UserVo getUser(Long id) {
        UserVo userVo = userDao.findById(id);
        SchoolVo schoolVo = schoolDao.findByName("清华");
        userVo.setSchoolVo(schoolVo);
        return userVo;
    }

}

Mybatis的xml文件UserDao.xml和SchoolDao.xml的内容：

UserDao.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="com.springboot.dao.master.UserDao">
    <resultMap id="BaseResultMap" type="com.springboot.vo.UserVo">
        <result column="id" property="id" />
        <result column="user_name" property="userName" />
    </resultMap>

    <sql id="Base_Column_List">
        id, user_name
    </sql>

    <select id="findById" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
            <include refid="Base_Column_List" />
        from
            user
        where
            id = #{id}
    </select>
</mapper>

SchoolDao.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="com.springboot.dao.second.SchoolDao">
    <resultMap id="BaseResultMap" type="com.springboot.vo.SchoolVo">
        <result column="id" property="id" />
        <result column="school_name" property="schoolName" />
        <result column="school_describe" property="schoolDescribe" />
    </resultMap>

    <sql id="Base_Column_List">
        id, school_name, school_describe
    </sql>

    <select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
        select
            <include refid="Base_Column_List" />
        from
            school
        where
            school_name = #{schoolName}
    </select>

</mapper>

TestController

@Controller
@RequestMapping(value = "/api/v1/test")
public class TestController {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "/getUser")
    @ResponseBody
    public UserVo getUser(@RequestParam(value = "id") Long id) {
        return userService.getUser(id);
    }

}

创建数据库和表SQL：

CREATE DATABASE springbootdb;

CREATE DATABASE springbootdb_second;

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `user_name` varchar(25) DEFAULT NULL COMMENT '用户名称',
  `description` varchar(25) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CREATE TABLE `school` (
  `id` bigint(20) NOT NULL,
  `school_name` varchar(64) DEFAULT NULL COMMENT '学校名',
  `school_describe` varchar(128) DEFAULT NULL COMMENT '学校描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into `springbootdb`.`user` ( `user_name`, `description`) values ( 'shuai', 'so handsome');

insert into `springbootdb_second`.`school` ( `id`, `school_name`, `school_describe`) values ( '1', '清华', '自强不息，厚德载物');

创建好数据后，整个工程的机构如下：

启动程序，在浏览器输入：http://localhost:8080/api/v1/test/getUser?id=1即可返回结果。