imxushuai首页分类文章总览学习资源关于
Spring Boot + JPA 多数据源配置
|Spring Boot
字数总计: 2k|阅读时长: 10 分钟
Spring Boot + JPA 完成多数据源的配置与使用

前言

在实际的开发中，大多数时候一个项目用到的数据库源都只有一个，但是难免遇到特例，如果遇到了同个项目需要连接两个或更多数据源我们该怎么呢？

本文带你了解 Spring Boot 配置多数据源。

准备工作

  • 准备数据库，我这里使用同一个Mysql中的两个不同的数据库，并且分别在两个数据库中创建了一张数据表

  • primary_table表结构

  • secondary_table表结构

主要代码

引入依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    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以及驱动类）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

配置双数据源

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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数据源

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    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数据源

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    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数据源

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    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数据源

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    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

1
2
3
4
5
6
7
package com.imxushuai.repository.primary;

import com.imxushuai.entity.primary.PrimaryTable;
import org.springframework.data.repository.CrudRepository;

public interface PrimaryRepository extends CrudRepository<PrimaryTable, Long> {
}
1
2
3
4
5
6
7
package com.imxushuai.repository.secondary;

import com.imxushuai.entity.secondary.SecondaryTable;
import org.springframework.data.repository.CrudRepository;

public interface SecondaryRepository extends CrudRepository<SecondaryTable, Long> {
}

测试

编写测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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：

参考

Spring Boot多数据源配置与使用

Spring boot issue

文章作者: imxushuai
文章链接: https://www.imxushuai.com/2019/06/25/14.Spring-Boot-JPA-多数据源配置/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 imxushuai！
Spring BootSpring Data JPA
支付宝打赏
微信打赏