Spring Boot中配置多个数据源

配置数据源实际上就是配置多个数据库，在一个配置文件中配置多个数据库，这样做主要的好处有以下几点：

1. 数据库隔离：通过配置多个数据源，可以将不同的业务数据存储在不同的数据库中，实现数据的隔离。这样可以提高系统的安全性和稳定性，避免不同业务之间的数据相互干扰。
2. 性能优化：通过配置多个数据源，可以将读写操作分离到不同的数据库中，从而提高系统的并发性能。例如，将读操作集中在一个主数据库中，将写操作分散到多个从数据库中，可以有效地减轻数据库的读写压力，提高系统的响应速度。
3. 扩展性：当系统需要扩展到多个地理位置或多个数据中心时，配置多个数据源可以更好地支持分布式部署。每个地理位置或数据中心可以配置一个独立的数据源，使得数据访问更加高效和可靠。
4. 多租户支持：对于多租户的系统，配置多个数据源可以实现不同租户的数据隔离。每个租户可以拥有自己独立的数据库，从而保证数据的安全性和隐私性。
5. 数据库版本升级：在进行数据库版本升级时，可以通过配置多个数据源，将新版本的数据库与旧版本的数据库并行使用。这样可以在升级过程中保证系统的正常运行，减少升级带来的风险。

我们目前常用的三种数据访问方法方式：

1. JDBCTemplate
2. Spring Data JPA
3. MyBatis

接下来我们将围绕这种数据访问方法进行配置：

JDBCTemplate

实际上这种方式，主要还是在application.properties中设置我们需要链接的数据库配置，比如我A数据库用来存储用户信息，B数据库用来存储业务数据。

spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.datasource.primary.username=root
spring.datasource.primary.password=123456
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.datasource.secondary.username=root
spring.datasource.secondary.password=123456
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

ok，我们继续配置完毕后数据源后，我们就通过配置类来完成加载这些配置信息，初始话数据源，以及初始化每个数据源的时候要用的JDBCTemplate。你只需要在你的Spring Boot中应用下面的配置类就可以完成。

@Configuration
public class DataSourceConfiguration {@Primary@Bean@ConfigurationProperties(prefix = "spring.datasource.primary")public DataSource primaryDataSource() {return DataSourceBuilder.create().build();}@Bean@ConfigurationProperties(prefix = "spring.datasource.secondary")public DataSource secondaryDataSource() {return DataSourceBuilder.create().build();}@Beanpublic JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource primaryDataSource) {return new JdbcTemplate(primaryDataSource);}@Beanpublic JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {return new JdbcTemplate(secondaryDataSource);}}

	<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies>

单元测试类：

@RunWith(SpringRunner.class)
@SpringBootTest
public class ApplicationTests {@Autowiredprotected JdbcTemplate primaryJdbcTemplate;@Autowiredprotected JdbcTemplate secondaryJdbcTemplate;@Beforepublic void setUp() {primaryJdbcTemplate.update("DELETE  FROM  USER ");secondaryJdbcTemplate.update("DELETE  FROM  USER ");}@Testpublic void test() throws Exception {// 往第一个数据源中插入 2 条数据primaryJdbcTemplate.update("insert into user(name,age) values(?, ?)", "aaa", 20);primaryJdbcTemplate.update("insert into user(name,age) values(?, ?)", "bbb", 30);// 往第二个数据源中插入 1 条数据，若插入的是第一个数据源，则会主键冲突报错secondaryJdbcTemplate.update("insert into user(name,age) values(?, ?)", "ccc", 20);// 查一下第一个数据源中是否有 2 条数据，验证插入是否成功Assert.assertEquals("2", primaryJdbcTemplate.queryForObject("select count(1) from user", String.class));// 查一下第一个数据源中是否有 1 条数据，验证插入是否成功Assert.assertEquals("1", secondaryJdbcTemplate.queryForObject("select count(1) from user", String.class));}
}

有两个JdbcTemplate，为什么不用@Qualifier指定？这里顺带说个小知识点，当我们不指定的时候，会采用参数的名字来查找Bean，存在的话就注入。

这两个JdbcTemplate创建的时候，我们也没指定名字，它们是如何匹配上的？这里也是一个小知识点，当我们创建Bean的时候，默认会使用方法名称来作为Bean的名称，所以这里就对应上了。读者不妨回头看看两个名称是不是一致的。

Spring Data JPA

和上边那种方式基本上类似，所做的操作会有细微的区别。

		<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-actuator</artifactId></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><scope>provided</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency>

配置application.properties文件

# pring boot 1.x的配置：spring.datasource.primary.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/test1
spring.datasource.primary.username=root
spring.datasource.primary.password=123456
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver# spring boot 1.x的配置：spring.datasource.secondary.url=jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/test2
spring.datasource.secondary.username=root
spring.datasource.secondary.password=123456
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver# 日志打印执行的SQL
spring.jpa.show-sql=true
# Hibernate的DDL策略
spring.jpa.hibernate.ddl-auto=create-drop

创建实体类：

@Entity
//@Data
//@NoArgsConstructor
public class User {@Id@GeneratedValueprivate Long id;private String name;private Integer age;public User(String name, Integer age) {this.name = name;this.age = age;}public Long getId() {return id;}public void setId(Long 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;}public User() {}
}

public interface UserRepository extends JpaRepository<User, Long> {}

@Entity
//@Data
//@NoArgsConstructor
public class Message {@Id@GeneratedValueprivate Long id;private String title;private String message;public Message(String title, String message) {this.title = title;this.message = message;}public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public String getMessage() {return message;}public void setMessage(String message) {this.message = message;}public Message() {}
}

public interface MessageRepository extends JpaRepository<Message, Long> {}

多数据源配置类：

@Configuration
public class DataSourceConfiguration {@Primary@Bean@ConfigurationProperties(prefix = "spring.datasource.primary")public DataSource primaryDataSource() {return DataSourceBuilder.create().build();}@Bean@ConfigurationProperties(prefix = "spring.datasource.secondary")public DataSource secondaryDataSource() {return DataSourceBuilder.create().build();}}

主数据源配置类：

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef="entityManagerFactoryPrimary",transactionManagerRef="transactionManagerPrimary",basePackages= { "com.miaow.demo.p" }) //设置Repository所在位置
public class PrimaryConfig {@Autowired@Qualifier("primaryDataSource")private DataSource primaryDataSource;@Autowiredprivate JpaProperties jpaProperties;@Autowiredprivate HibernateProperties hibernateProperties;private Map<String, Object> getVendorProperties() {return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());}@Primary@Bean(name = "entityManagerPrimary")public EntityManager entityManager(EntityManagerFactoryBuilder builder) {return entityManagerFactoryPrimary(builder).getObject().createEntityManager();}@Primary@Bean(name = "entityManagerFactoryPrimary")public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
//        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
//        jpaVendorAdapter.setGenerateDdl(true);return builder.dataSource(primaryDataSource).packages("com.miaow.demo.p") //设置实体类所在位置.persistenceUnit("primaryPersistenceUnit").properties(getVendorProperties()).build();}@Primary@Bean(name = "transactionManagerPrimary")public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());}
}

设置从数据库配置类：

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef="entityManagerFactorySecondary",transactionManagerRef="transactionManagerSecondary",basePackages= { "com.miaow.demo.s" }) //设置Repository所在位置
public class SecondaryConfig {@Autowired@Qualifier("secondaryDataSource")private DataSource secondaryDataSource;@Autowiredprivate JpaProperties jpaProperties;@Autowiredprivate HibernateProperties hibernateProperties;private Map<String, Object> getVendorProperties() {return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());}@Bean(name = "entityManagerSecondary")public EntityManager entityManager(EntityManagerFactoryBuilder builder) {return entityManagerFactorySecondary(builder).getObject().createEntityManager();}@Bean(name = "entityManagerFactorySecondary")public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary (EntityManagerFactoryBuilder builder) {return builder.dataSource(secondaryDataSource).packages("com.miaow.demo.s") //设置实体类所在位置.persistenceUnit("secondaryPersistenceUnit").properties(getVendorProperties()).build();}@Bean(name = "transactionManagerSecondary")PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());}}

创建的单元测试：

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class ApplicationTests {@Autowiredprivate UserRepository userRepository;@Autowiredprivate MessageRepository messageRepository;@Testpublic void test() throws Exception {userRepository.save(new User("aaa", 10));userRepository.save(new User("bbb", 20));userRepository.save(new User("ccc", 30));userRepository.save(new User("ddd", 40));userRepository.save(new User("eee", 50));Assert.assertEquals(5, userRepository.findAll().size());messageRepository.save(new Message("o1", "aaaaaaaaaa"));messageRepository.save(new Message("o2", "bbbbbbbbbb"));messageRepository.save(new Message("o3", "cccccccccc"));Assert.assertEquals(3, messageRepository.findAll().size());}}

MyBatis

在Spring Boot的配置文件application.properties中设置我们需要的两个连接的数据库配置：

# pring boot 1.x的配置：spring.datasource.primary.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/test1
spring.datasource.primary.username=root
spring.datasource.primary.password=123456
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver# spring boot 1.x的配置：spring.datasource.secondary.url=jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/test2
spring.datasource.secondary.username=root
spring.datasource.secondary.password=123456
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver#mybatis.mapper-locations=classpath:mapper/*.xml

创建一个初始化多数据源和MyBatis配置

@Configuration
public class DataSourceConfiguration {@Primary@Bean@ConfigurationProperties(prefix = "spring.datasource.primary")public DataSource primaryDataSource() {return DataSourceBuilder.create().build();}@Bean@ConfigurationProperties(prefix = "spring.datasource.secondary")public DataSource secondaryDataSource() {return DataSourceBuilder.create().build();}}

可以看到内容跟JdbcTemplate、Spring Data JPA的时候是一模一样的。通过@ConfigurationProperties可以知道这两个数据源分别加载了spring.datasource.primary.*和spring.datasource.secondary.*的配置。@Primary注解指定了主数据源，就是当我们不特别指定哪个数据源的时候，就会使用这个Bean真正差异部分在下面的JPA配置上。

分别创建这两个数据源的MyBatis配置文件：
Private 数据源的JPA的配置文件：

@Configuration
@MapperScan(basePackages = "com.miaow.demo.p",sqlSessionFactoryRef = "sqlSessionFactoryPrimary",sqlSessionTemplateRef = "sqlSessionTemplatePrimary")
public class PrimaryConfig {private DataSource primaryDataSource;public PrimaryConfig(@Qualifier("primaryDataSource") DataSource primaryDataSource) {this.primaryDataSource = primaryDataSource;}@Beanpublic SqlSessionFactory sqlSessionFactoryPrimary() throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(primaryDataSource);return bean.getObject();}@Beanpublic SqlSessionTemplate sqlSessionTemplatePrimary() throws Exception {return new SqlSessionTemplate(sqlSessionFactoryPrimary());}}

Secondary的数据源JPA配置：

@Configuration
@MapperScan(basePackages = "com.miaow.demo.s",sqlSessionFactoryRef = "sqlSessionFactorySecondary",sqlSessionTemplateRef = "sqlSessionTemplateSecondary")
public class SecondaryConfig {private DataSource secondaryDataSource;public SecondaryConfig(@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {this.secondaryDataSource = secondaryDataSource;}@Beanpublic SqlSessionFactory sqlSessionFactorySecondary() throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(secondaryDataSource);return bean.getObject();}@Beanpublic SqlSessionTemplate sqlSessionTemplateSecondary() throws Exception {return new SqlSessionTemplate(sqlSessionFactorySecondary());}}

注意在此说明一下，配置类上使用@MapperScan注解来指定当前数据源下定义的Entity和Mapper的包路径；
另外需要指定sqlSessionFactory和sqlSessionTemplate，这两个具体实现在该配置类中类中初始化。
配置类的构造函数中，通过@Qualifier注解来指定具体要用哪个数据源，其名字对应在DataSourceConfiguration配置类中的数据源定义的函数名。
配置类中定义SqlSessionFactory和SqlSessionTemplate的实现，注意具体使用的数据源正确。

创建一个UserPrimary实体类:

public class UserPrimary {private Long id;private String name;private Integer age;public UserPrimary(String name, Integer age) {this.name = name;this.age = age;}public Long getId() {return id;}public void setId(Long 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;}public UserPrimary() {}
}

创建一个UserMapperPrimary用来作为填写SQL语句的接口：

public interface UserMapperPrimary {@Select("SELECT * FROM USER WHERE NAME = #{name}")UserPrimary findByName(@Param("name") String name);@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")int insert(@Param("name") String name, @Param("age") Integer age);@Delete("DELETE FROM USER")int deleteAll();}

创建一个UserSecondary实体类：

public class UserSecondary {private Long id;private String name;private Integer age;public Long getId() {return id;}public void setId(Long 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;}public UserSecondary() {}public UserSecondary(String name, Integer age) {this.name = name;this.age = age;}
}

public interface UserMapperSecondary {@Select("SELECT * FROM USER WHERE NAME = #{name}")UserSecondary findByName(@Param("name") String name);@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")int insert(@Param("name") String name, @Param("age") Integer age);@Delete("DELETE FROM USER")int deleteAll();
}

之后，也是最关键的一步，我们需要在MyBatis的配置文件中使用Mapper.xml
在Resources目录下创建一个mapper文件夹，之后再mapper文件夹下边分别创建primary和secondary文件夹：

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.miaow.demo.p.mapper.UserMapperPrimary"><select id="findByName" resultType="com.miaow.demo.p.entity.UserPrimary">SELECT * FROM USER WHERE NAME = #{name}</select><insert id="insert">INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})</insert></mapper>

与上边这个几乎一样，只是两者指向的接口位置不一样：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.miaow.demo.s.mapper.UserMapperSecondary"><select id="findByName" resultType="com.miaow.demo.s.entity.UserSecondary">SELECT * FROM USER WHERE NAME = #{name}</select><insert id="insert">INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})</insert></mapper>

最后我们可以通过一个测试类进行测试：

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional
public class ApplicationTests {//第一数据库配置类@Autowiredprivate UserMapperPrimary userMapperPrimary;//第二数据库配置文件@Autowiredprivate UserMapperSecondary userMapperSecondary;@Beforepublic void setUp() {// 清空测试表，保证每次结果一样userMapperPrimary.deleteAll();userMapperSecondary.deleteAll();}@Testpublic void test() throws Exception {// 往Primary数据源插入一条数据userMapperPrimary.insert("AAA", 20);// 从Primary数据源查询刚才插入的数据，配置正确就可以查询到UserPrimary userPrimary = userMapperPrimary.findByName("AAA");Assert.assertEquals(20, userPrimary.getAge().intValue());// 从Secondary数据源查询刚才插入的数据，配置正确应该是查询不到的UserSecondary userSecondary = userMapperSecondary.findByName("AAA");Assert.assertNull(userSecondary);// 往Secondary数据源插入一条数据userMapperSecondary.insert("BBB", 20);// 从Primary数据源查询刚才插入的数据，配置正确应该是查询不到的userPrimary = userMapperPrimary.findByName("BBB");Assert.assertNull(userPrimary);// 从Secondary数据源查询刚才插入的数据，配置正确就可以查询到userSecondary = userMapperSecondary.findByName("BBB");Assert.assertEquals(20, userSecondary.getAge().intValue());}}