前言
在实际的开发中,大多数时候一个项目用到的数据库源都只有一个,但是难免遇到特例,如果遇到了同个项目需要连接两个或更多数据源我们该怎么呢?
本文带你了解 Spring Boot 配置多数据源。
准备工作
- 准备数据库,我这里使用同一个
Mysql
中的两个不同的数据库,并且分别在两个数据库中创建了一张数据表
- primary_table表结构
-
secondary_table表结构
主要代码
引入依赖
<dependencies>
<!-- JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- MVC -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Mysql Driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- Test -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
</dependencies>
实体类
-
PrimaryTable
package com.imxushuai.entity.primary; import lombok.Data; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; @Data @Entity public class PrimaryTable { @Id private Long id; @Column private String username; @Column private String password; @Column private String p; }
-
SecondaryTable
package com.imxushuai.entity.secondary; import lombok.Data; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; @Data @Entity public class SecondaryTable { @Id private Long id; @Column private String username; @Column private String password; @Column private String s; }
我这里使用lombok
,不需要写get
和set
方法,使用@Data
注解在编译时会自动生成。
编写配置文件
由于有多个数据源,所以这里在配置文件里,我们需要配置多个数据源信息。(本例中采用的是两个Mysql数据库,若你想连接不同的数据库只需要修改jdbc-url
以及驱动类)
spring:
datasource:
# primary数据库
primary:
jdbc-url: jdbc:mysql:///primary?characterEncoding=utf-8&serverTimezone=GMT
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
# secondary数据库
secondary:
jdbc-url: jdbc:mysql:///secondary?characterEncoding=utf-8&serverTimezone=GMT
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
注意:
Spring Boot 2.x
中数据库URL的配置字段为:jdbc-url
Spring Boot 1.x
中数据库URL的配置字段为:url
配置双数据源
package com.imxushuai.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Primary
@Bean(name = "primaryDataSource")
@Qualifier("primaryDataSource")
@ConfigurationProperties(prefix="spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondaryDataSource")
@Qualifier("secondaryDataSource")
@ConfigurationProperties(prefix="spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
}
- 多个数据源,必须使用
@Primary
注解指定一个主数据源
配置多个数据源的连接信息
由于Spring Boot 1.x
和Spring Boot 2.x
配置多数据源有一些差别,所以这里我分为1.x
和2.x
两块,具体用哪部分根据Spring Boot
版本决定。
准确的来说是:2.0.6
版本以后和之前的版本有较大区别。
**注意:**两个数据源的实体类和Repository需要分包存放,参考下面的包结构
1.x
-
配置
Primary
数据源package com.ensat.config; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties; import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.persistence.EntityManager; import javax.sql.DataSource; import java.util.Map; @Configuration @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef="entityManagerFactoryPrimary", transactionManagerRef="transactionManagerPrimary", basePackages= { "com.imxushuai.repository.primary" }) //设置Repository所在位置 public class PrimaryConfig { @Autowired @Qualifier("primaryDataSource") private DataSource primaryDataSource; @Autowired private JpaProperties jpaProperties; @Primary @Bean(name = "entityManagerPrimary") public EntityManager entityManager(EntityManagerFactoryBuilder builder) { return entityManagerFactoryPrimary(builder).getObject().createEntityManager(); } @Primary @Bean(name = "entityManagerFactoryPrimary") public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) { return builder .dataSource(primaryDataSource) .properties(getVendorProperties(primaryDataSource)) .packages("com.imxushuai.entity.primary") //设置实体类所在位置 .persistenceUnit("primaryPersistenceUnit") .build(); } private Map<String, String> getVendorProperties(DataSource dataSource) { return jpaProperties.getHibernateProperties(dataSource); } @Primary @Bean(name = "transactionManagerPrimary") public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) { return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject()); } }
-
配置
Secondary
数据源package com.ensat.config; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties; import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.persistence.EntityManager; import javax.sql.DataSource; import java.util.Map; @Configuration @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef="entityManagerFactorySecondary", transactionManagerRef="transactionManagerSecondary", basePackages= { "com.imxushuai.repository.secondary" }) //设置Repository所在位置 public class SecondaryConfig { @Autowired @Qualifier("secondaryDataSource") private DataSource secondaryDataSource; @Autowired private JpaProperties jpaProperties; @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) .properties(getVendorProperties(secondaryDataSource)) .packages("com.imxushuai.entity.secondary") //设置实体类所在位置 .persistenceUnit("secondaryPersistenceUnit") .build(); } private Map<String, String> getVendorProperties(DataSource dataSource) { return jpaProperties.getHibernateProperties(dataSource); } @Bean(name = "transactionManagerSecondary") PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) { return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject()); } }
2.x
-
配置
Primary
数据源package com.imxushuai.config; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties; import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings; import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties; import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.persistence.EntityManager; import javax.sql.DataSource; import java.util.Map; @Configuration @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef="entityManagerFactoryPrimary", transactionManagerRef="transactionManagerPrimary", basePackages= { "com.imxushuai.repository.primary" }) //设置Repository所在位置 public class PrimaryConfig { @Autowired @Qualifier("primaryDataSource") private DataSource primaryDataSource; @Autowired private HibernateProperties hibernateProperties; @Autowired private JpaProperties jpaProperties; @Primary @Bean(name = "entityManagerPrimary") public EntityManager entityManager(EntityManagerFactoryBuilder builder) { return entityManagerFactoryPrimary(builder).getObject().createEntityManager(); } @Primary @Bean(name = "entityManagerFactoryPrimary") public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) { // 在2.0.6以上版本,jpaProperties中没有直接获取HibernateProperties的方法了 Map<String, Object> properties = hibernateProperties .determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings()); return builder .dataSource(primaryDataSource) .properties(properties) .packages("com.imxushuai.entity.primary") //设置实体类所在位置 .persistenceUnit("primaryPersistenceUnit") .build(); } @Primary @Bean(name = "transactionManagerPrimary") public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) { return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject()); } }
-
配置
Secondary
数据源package com.imxushuai.config; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties; import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings; import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties; import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.persistence.EntityManager; import javax.sql.DataSource; import java.util.Map; @Configuration @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef="entityManagerFactorySecondary", transactionManagerRef="transactionManagerSecondary", basePackages= { "com.imxushuai.repository.secondary" }) //设置Repository所在位置 public class SecondaryConfig { @Autowired @Qualifier("secondaryDataSource") private DataSource secondaryDataSource; @Autowired private HibernateProperties hibernateProperties; @Autowired private JpaProperties jpaProperties; @Bean(name = "entityManagerSecondary") public EntityManager entityManager(EntityManagerFactoryBuilder builder) { return entityManagerFactorySecondary(builder).getObject().createEntityManager(); } @Bean(name = "entityManagerFactorySecondary") public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary (EntityManagerFactoryBuilder builder) { // 在2.x中jpaProperties中没有直接获取HibernateProperties的方法了 Map<String, Object> properties = hibernateProperties .determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings()); return builder .dataSource(secondaryDataSource) .properties(properties) .packages("com.imxushuai.entity.secondary") //设置实体类所在位置 .persistenceUnit("secondaryPersistenceUnit") .build(); } @Bean(name = "transactionManagerSecondary") PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) { return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject()); } }
Repository
这个没什么说的,继承CrudRepository
package com.imxushuai.repository.primary;
import com.imxushuai.entity.primary.PrimaryTable;
import org.springframework.data.repository.CrudRepository;
public interface PrimaryRepository extends CrudRepository<PrimaryTable, Long> {
}
package com.imxushuai.repository.secondary;
import com.imxushuai.entity.secondary.SecondaryTable;
import org.springframework.data.repository.CrudRepository;
public interface SecondaryRepository extends CrudRepository<SecondaryTable, Long> {
}
测试
编写测试类
package com.imxushuai;
import com.imxushuai.entity.primary.PrimaryTable;
import com.imxushuai.entity.secondary.SecondaryTable;
import com.imxushuai.repository.primary.PrimaryRepository;
import com.imxushuai.repository.secondary.SecondaryRepository;
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 MultipleDatasourceTest {
@Autowired
private PrimaryRepository primaryRepository;
@Autowired
private SecondaryRepository secondaryRepository;
@Test
public void testAdd() {
PrimaryTable primary = new PrimaryTable();
primary.setId(1L);
primary.setUsername("PrimaryTable 1");
primary.setPassword("PrimaryTable 1");
primary.setP("This primary 1");
primaryRepository.save(primary);
SecondaryTable secondaryTable = new SecondaryTable();
secondaryTable.setId(1L);
secondaryTable.setUsername("SecondaryTable 1");
secondaryTable.setPassword("SecondaryTable 1");
secondaryTable.setS("This secondary 1");
secondaryRepository.save(secondaryTable);
System.out.println(primaryRepository.findById(1L));
System.out.println(secondaryRepository.findById(1L));
}
}
运行结果:
新增成功并且成功查询到数据。
我这里的截图是Spring Boot 2.x
,findById
出来结果使用Optional
包裹的。
代码获取
Github: