Spring Boot 1.0 && 2.0 + JPA 多数据源配置与使用

环境说明

• Spring Boot 1.5.17.RELEASE 或 Spring Boot 2.1.0.RELEASE
• MySQL v5.6.19
• PostgreSQL v10.4

无特殊说明，以下所说的环境均指 Spring Boot 2.1.0.RELEASE，如果使用的是 Spring Boot 1.5.17.RELEASE 这个版本，只需要调整下面有做说明的几处地方

连接配置

在application.yml中定义如下信息：

spring:
  jpa:
    hibernate:
      # 多数据源下，该属性不生效，需要在配置中额外指定，这里仅表示普通定义
      ddl-auto: create-drop
    properties:
      hibernate:
        show_sql: true
        format_sql: true
        jdbc:
          lob:
            non_contextual_creation: true
    open-in-view: false
  # 定义不同数据源的连接信息
  datasource:
    hikari:
      mysql:
        # Spring Boot 1.0+ 版本：使用spring.datasource.url
        # Spring Boot 2.0+ 版本：使用spring.datasource.hikari.jdbc-url
        jdbc-url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
        # Spring Boot 1.0+ 版本：使用com.mysql.jdbc.Driver
        # Spring Boot 2.0+ 版本：使用com.mysql.cj.jdbc.Driver
        driver-class-name: com.mysql.cj.jdbc.Driver
      postgres:
        jdbc-url: jdbc:postgresql://localhost:5432/postgres
        username: postgres
        password: postgres
        driver-class-name: org.postgresql.Driver

配置数据源

根据上面定义的配置信息，配置这两个数据源：

// Spring Boot 1.0+ ，DataSourceBuilder所在包位置为：org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder
// Spring Boot 2.0+ ，DataSourceBuilder所在包位置为：org.springframework.boot.jdbc.DataSourceBuilder
@Configuration
public class DataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties("spring.datasource.hikari.mysql")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.hikari.postgres")
    public DataSource postgresDataSource() {
        return DataSourceBuilder.create().build();
    }

}

JPA 支持

添加 mysql 对应数据源的 JPA 支持：

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "mysqlEntityManagerFactory",
        transactionManagerRef = "mysqlTransactionManager",
        // 数据层所在的包位置
        basePackages = "cn.mariojd.springboot.multiple.datasource.jpa.mysql.repository")
public class MysqlDataSourceConfig {

    @Resource
    private Environment environment;

    @Resource
    @Qualifier("mysqlDataSource")
    private DataSource dataSource;

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        Map<String, Object> properties = new HashMap<>(4);
        // Spring Boot 1.0+ ，使用MySQLDialect
        // Spring Boot 2.0+ ，指定MySQLDialect会默认使用MyISAM引擎，改成MySQL55Dialect即可
        properties.put("hibernate.dialect", "org.hibernate.dialect.MySQL55Dialect");
        properties.put("hibernate.hbm2ddl.auto", environment.getProperty("spring.jpa.hibernate.ddl-auto"));
        return builder.dataSource(dataSource)
                .properties(properties)
                // 实体所在的包位置
                .packages("cn.mariojd.springboot.multiple.datasource.jpa.mysql.entity")
                .persistenceUnit("jpa-mysql")
                .build();
    }

    @Bean
    @Primary
    public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

}

添加 postgres 对应数据源的 JPA 支持：

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "postgresEntityManagerFactory",
        transactionManagerRef = "postgresTransactionManager",
        // 数据层所在的包位置
        basePackages = "cn.mariojd.springboot.multiple.datasource.jpa.postgres.repository")
public class PostgresDataSourceConfig {

    @Resource
    private Environment environment;

    @Resource
    @Qualifier("postgresDataSource")
    private DataSource dataSource;

    @Bean
    public LocalContainerEntityManagerFactoryBean postgresEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        Map<String, Object> properties = new HashMap<>(4);
        properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        properties.put("hibernate.hbm2ddl.auto", environment.getProperty("spring.jpa.hibernate.ddl-auto"));
        return builder.dataSource(dataSource)
                // 实体所在的包位置
                .properties(properties)
                .packages("cn.mariojd.springboot.multiple.datasource.jpa.postgres.entity")
                .persistenceUnit("jpa-postgres")
                .build();
    }

    @Bean
    public PlatformTransactionManager postgresTransactionManager(@Qualifier("postgresEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

}

相关定义

mysql 对应的数据源配置中，定义了实体 Student 和对应的数据层接口 StudentRepository：

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    public Student(String name) {
        this.name = name;
    }
}

public interface StudentRepository extends JpaRepository<Student, Integer> {
}

postgres 对应的数据源配置中，定义了实体 Teacher 和对应的数据层接口 TeacherRepository：

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String name;

    public Teacher(String name) {
        this.name = name;
    }
}

public interface TeacherRepository extends JpaRepository<Teacher, Integer> {
}

单元测试

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootJpaMultipleDataSourceTest {

    @Resource
    private StudentRepository studentRepository;

    @Resource
    private TeacherRepository teacherRepository;

    @Test
    public void test() {
        studentRepository.save(new Student("张三"));
        studentRepository.save(new Student("李四"));
        studentRepository.save(new Student("王五"));

        Assert.assertEquals(3, studentRepository.findAll().size());

        teacherRepository.save(new Teacher("张老师"));
        teacherRepository.save(new Teacher("李老师"));
        teacherRepository.save(new Teacher("王老师"));
        Assert.assertEquals(3, teacherRepository.findAll().size());
    }

}

参考链接

Using multiple datasources with Spring Boot and Spring Data
Spring JPA – Multiple Databases
Spring Boot多数据源配置与使用
How to connect to Multiple databases with Spring Data JPA
Springboot2.0中Hibernate默认创建的mysql表为myisam引擎问题
关于springboot2.0.0配置多数据源出现jdbcUrl is required with driverClassName的错误
解决mysql java.sql.SQLException: The server time zone value ...

示例源码
欢迎关注我的个人公众号：超级码里奥
如果这对您有帮助，欢迎点赞和分享，转载请注明出处