标签： Druid

1. 前言

平时开发的时候偶尔会遇到多数据库读写的情况（非分库分表），本文会给出一个简单的配置和使用两个数据库的示例。

2. 依赖与属性配置

首先给出 pom.xml 中引入的依赖：

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>LATEST</version>
</dependency>

然后我们手动创建两个数据库。在尝试使用多数据源的时候发现的一个问题是 spring.datasource.schema 不生效了，意味着不能使用 Spring 启动时自动创建库表的特性，所以只能手动创建数据库：

mysql root@localhost:(none)> DROP DATABASE IF EXISTS `prime`;
                          -> CREATE DATABASE `prime`;
                          -> use prime;
                          ->
                          -> DROP TABLE IF EXISTS person;
                          -> CREATE TABLE `person` (
                          ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                          ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '
                          -> 名字',
                          ->   `age` int(11) NOT NULL COMMENT '年龄',
                          ->   `gender` int(11) NOT NULL COMMENT '性别',
                          ->   PRIMARY KEY (`id`)
                          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
                          ->
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.006s
​
Query OK, 1 row affected
Time: 0.012s
​
You are now connected to database "prime" as user "root"
Time: 0.002s
​
Query OK, 0 rows affected
Time: 0.007s
​
Query OK, 0 rows affected
Time: 0.021s
mysql root@localhost:(none)> DROP DATABASE IF EXISTS `secondary`;
                          -> CREATE DATABASE `secondary`;
                          -> use secondary;
                          ->
                          -> DROP TABLE IF EXISTS person;
                          -> CREATE TABLE `person` (
                          ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                          ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '
                          -> 名字',
                          ->   `age` int(11) NOT NULL COMMENT '年龄',
                          ->   `gender` int(11) NOT NULL COMMENT '性别',
                          ->   PRIMARY KEY (`id`)
                          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.004s
​
Query OK, 1 row affected
Time: 0.001s
​
You are now connected to database "secondary" as user "root"
Time: 0.002s
​
Query OK, 0 rows affected
Time: 0.001s
​
Query OK, 0 rows affected
Time: 0.028s

下面是 application.properties，注明了两个数据库的基本信息：

spring.datasource.primary.url=jdbc:mysql://127.0.0.1:3306/prime?charset=utf8
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver
​
spring.datasource.secondary.url=jdbc:mysql://127.0.0.1:3306/secondary?charset=utf8
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver

3. 配置数据源

针对两个数据库我们要分别创建对应的 DataSource、PlatformTransactionManager 和 SqlSessionFactory。

第一个 DataSource 我们使用 Spring 中的 DataSourceBuilder 来构建，并用 @Primary 注解来标记：

package com.avaloninc.springmultidatasourceexample.conf;
​
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.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
​
import javax.sql.DataSource;
​
@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = "com.avaloninc.springmultidatasourceexample.mapper.prime", sqlSessionFactoryRef = "primeSsf")
public class DataSourceConfigOne {
​
  @Bean(name = "primaryDs")
  @ConfigurationProperties(prefix = "spring.datasource.primary")
  @Primary
  public DataSource primaryDs() {
    return DataSourceBuilder.create().build();
  }
​
​
  @Bean(name = "primaryTxm")
  @Primary
  public PlatformTransactionManager primaryTxm(@Qualifier("primaryDs") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }
​
  @Bean(name = "primeSsf")
  @Primary
  public SqlSessionFactory primeSsf(@Qualifier("primaryDs") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(dataSource);
    return sqlSessionFactoryBean.getObject();
  }
}

第二个数据源我们使用 druid 作为连接池，除了基本参数外其他都采用默认配置：

package com.avaloninc.springmultidatasourceexample.conf;
​
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
​
import javax.sql.DataSource;
​
@Configuration
@EnableTransactionManagement
@MapperScan(basePackages = "com.avaloninc.springmultidatasourceexample.mapper.secondary", sqlSessionFactoryRef = "secondarySsf")
public class DataSourceConfigTwo {
​
  @Bean(name = "secondaryDs")
  @ConfigurationProperties(prefix = "spring.datasource.secondary")
  public DataSource secondaryDs() {
    return DruidDataSourceBuilder.create().build();
  }
​
  @Bean(name = "secondaryTxm")
  public PlatformTransactionManager secondaryTxm(@Qualifier("secondaryDs") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }
​
​
  @Bean(name = "secondarySsf")
  public SqlSessionFactory secondarySsf(@Qualifier("secondaryDs") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(dataSource);
    return sqlSessionFactoryBean.getObject();
  }
}

注意，在配置两个不同的数据源时都各自加了 @MapperSacn 注解，并各自给定了 basePackages 和 sqlSessionFactoryRef。不同数据源的 Mapper 在不同的 package 里面定义，并使用不同的 SqlSessionFactory 来创建。

下面是 Mapper 接口的定义：

package com.avaloninc.springmultidatasourceexample.mapper.prime;
​
import com.avaloninc.springmultidatasourceexample.domain.Person;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.type.EnumOrdinalTypeHandler;
​
@Mapper
public interface PersonMapper {
  /**
   * Insert int.
   *
   * @param person the person
   * @return the int
   */
  @Insert("insert into person (name, age, gender) values (#{p.name}, #{p.age}, #{p.gender, typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler, javaType=com.avaloninc.springmultidatasourceexample.domain.Person$Gender})")
  @Options(useGeneratedKeys = true, keyProperty = "p.id")
  int insert(@Param("p") Person person);
​
  /**
   * Gets person by id.
   *
   * @param id the id
   * @return the person by id
   */
  @Select("select id, name, age, gender from person where id = #{id}")
  @Results(id = "person", value = {
      @Result(column = "gender", property = "gender", typeHandler = EnumOrdinalTypeHandler.class)
  })
  Person getPersonById(@Param("id") int id);
}

以及：

package com.avaloninc.springmultidatasourceexample.mapper.secondary;
​
import com.avaloninc.springmultidatasourceexample.domain.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.type.EnumOrdinalTypeHandler;
​
@Mapper
public interface UserMapper {
  /**
   * Insert int.
   *
   * @param user the user
   * @return the int
   */
  @Insert("insert into person (name, age, gender) values (#{p.name}, #{p.age}, #{p.gender, typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler, javaType=com.avaloninc.springmultidatasourceexample.domain.User$Gender})")
  @Options(useGeneratedKeys = true, keyProperty = "p.id")
  int insert(@Param("p") User user);
​
  /**
   * Gets person by id.
   *
   * @param id the id
   * @return the person by id
   */
  @Select("select id, name, age, gender from person where id = #{id}")
  @Results(id = "person", value = {
      @Result(column = "gender", property = "gender", typeHandler = EnumOrdinalTypeHandler.class)
  })
  User getUserById(@Param("id") int id);
}

4. 简单的 Service 调用

下面是针对这两个 Mapper 的写的简单的读写接口：

package com.avaloninc.springmultidatasourceexample.service;
​
​
import com.avaloninc.springmultidatasourceexample.domain.Person;
​
public interface PersonService {
  /**
   * Insert int.
   *
   * @param person the person
   * @return the int
   */
  int insert(Person person);
​
  /**
   * Gets person by id.
   *
   * @param id the id
   * @return the person by id
   */
  Person getPersonById(int id);
}

package com.avaloninc.springmultidatasourceexample.service;
​
import com.avaloninc.springmultidatasourceexample.domain.Person;
import com.avaloninc.springmultidatasourceexample.mapper.prime.PersonMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
​
@Service
public class PersonServiceImpl implements PersonService {
​
  @Autowired
  private PersonMapper personMapper;
​
  @Override
  public int insert(Person person) {
    return this.personMapper.insert(person);
  }
​
  @Override
  public Person getPersonById(int id) {
    return this.personMapper.getPersonById(id);
  }
}

以及：

package com.avaloninc.springmultidatasourceexample.service;
​
import com.avaloninc.springmultidatasourceexample.domain.User;
​
public interface UserService {
  /**
   * Insert int.
   *
   * @param user the user
   * @return the int
   */
  int insert(User user);
​
  /**
   * Gets user by id.
   *
   * @param id the id
   * @return the user by id
   */
  User getUserById(int id);
}

package com.avaloninc.springmultidatasourceexample.service;
​
import com.avaloninc.springmultidatasourceexample.domain.User;
import com.avaloninc.springmultidatasourceexample.mapper.secondary.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
​
@Service
public class UserServiceImpl implements UserService {
​
  @Autowired
  private UserMapper userMapper;
​
  @Override
  public int insert(User user) {
    return this.userMapper.insert(user);
  }
​
  @Override
  public User getUserById(int id) {
    return this.userMapper.getUserById(id);
  }
}

5. 单元测试

单元测试一发入魂：

package com.avaloninc.springmultidatasourceexample;
​
import static org.junit.Assert.*;
​
import com.avaloninc.springmultidatasourceexample.domain.Person;
import com.avaloninc.springmultidatasourceexample.domain.User;
import com.avaloninc.springmultidatasourceexample.domain.User.Gender;
import com.avaloninc.springmultidatasourceexample.service.PersonService;
import com.avaloninc.springmultidatasourceexample.service.UserService;
import junit.framework.TestCase;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
​
@RunWith(SpringRunner.class)
@SpringBootTest
public class MainTest extends TestCase {
​
  @Autowired
  private UserService userService;
  @Autowired
  private PersonService personService;
​
  @Test
  public void test() {
    User user = new User();
    user.setAge(28);
    user.setGender(Gender.MALE);
    user.setName("John");
​
    int count = this.userService.insert(user);
    assertEquals(1, count);
​
    User userById = this.userService.getUserById(user.getId());
    assertEquals(user, userById);
​
    Person person = new Person();
    person.setAge(27);
    person.setName("Doe");
    person.setGender(Person.Gender.MALE);
​
    int insertCount = this.personService.insert(person);
    assertEquals(1, insertCount);
​
    Person personById = this.personService.getPersonById(person.getId());
    assertEquals(person, personById);
  }
}

查看数据库确认一下：

mysql root@localhost:(none)> select * from prime.person;
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1  | Doe  | 27  | 0      |
+----+------+-----+--------+
1 row in set
Time: 0.006s
mysql root@localhost:(none)> select * from secondary.person;
+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
| 1  | John | 28  | 0      |
+----+------+-----+--------+
1 row in set
Time: 0.006s
mysql root@localhost:(none)>

以上！