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;
basePackages = "com.avaloninc.springmultidatasourceexample.mapper.prime", sqlSessionFactoryRef = "primeSsf")
(public class DataSourceConfigOne {
name = "primaryDs")
( prefix = "spring.datasource.primary")
(
public DataSource primaryDs() {
return DataSourceBuilder.create().build();
}
name = "primaryTxm")
(
public PlatformTransactionManager primaryTxm( ("primaryDs") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
name = "primeSsf")
(
public SqlSessionFactory primeSsf( ("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;
basePackages = "com.avaloninc.springmultidatasourceexample.mapper.secondary", sqlSessionFactoryRef = "secondarySsf")
(public class DataSourceConfigTwo {
name = "secondaryDs")
( prefix = "spring.datasource.secondary")
( public DataSource secondaryDs() {
return DruidDataSourceBuilder.create().build();
}
name = "secondaryTxm")
( public PlatformTransactionManager secondaryTxm( ("secondaryDs") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
name = "secondarySsf")
( public SqlSessionFactory secondarySsf( ("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;
public interface PersonMapper {
/**
* Insert int.
*
* @param person the person
* @return the int
*/
"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})")
( useGeneratedKeys = true, keyProperty = "p.id")
( int insert( ("p") Person person);
/**
* Gets person by id.
*
* @param id the id
* @return the person by id
*/
"select id, name, age, gender from person where id = #{id}")
( id = "person", value = {
( column = "gender", property = "gender", typeHandler = EnumOrdinalTypeHandler.class)
( })
Person getPersonById( ("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;
public interface UserMapper {
/**
* Insert int.
*
* @param user the user
* @return the int
*/
"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})")
( useGeneratedKeys = true, keyProperty = "p.id")
( int insert( ("p") User user);
/**
* Gets person by id.
*
* @param id the id
* @return the person by id
*/
"select id, name, age, gender from person where id = #{id}")
( id = "person", value = {
( column = "gender", property = "gender", typeHandler = EnumOrdinalTypeHandler.class)
( })
User getUserById( ("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;
public class PersonServiceImpl implements PersonService {
private PersonMapper personMapper;
public int insert(Person person) {
return this.personMapper.insert(person);
}
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;
public class UserServiceImpl implements UserService {
private UserMapper userMapper;
public int insert(User user) {
return this.userMapper.insert(user);
}
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;
SpringRunner.class)
(
public class MainTest extends TestCase {
private UserService userService;
private PersonService personService;
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)>
以上!