SpringBoot多数据源配置

本文介绍springboot结合jpa，在需要多个数据源，多个数据库时，应该怎样配置等。
使用mysql存储，使用基于Hibernate的JPA,在下文的demo中两个数据库test，它有一张User表; 另一个库prod，它有一张表名为Person.

---

• project目录

├─java
│  └─com
│      └─demo
│          └─multi
│              └─datasource
│                  │  DataSourceConfig.java
│                  │  MultiDatasourceApplication.java
│                  │  ProdDbConfig.java
│                  │  TestDbConfig.java
│                  │
│                  ├─controller
│                  │      DemoController.java
│                  │
│                  ├─model
│                  │  │  SexEnum.java
│                  │  │
│                  │  ├─prod
│                  │  │      Person.java
│                  │  │
│                  │  └─test
│                  │          User.java
│                  │
│                  ├─repository
│                  │  ├─prod
│                  │  │      PersonRepository.java
│                  │  │
│                  │  └─test
│                  │          UserRepository.java
│                  │
│                  └─service
│                          DemoService.java
│
└─resources
        application.yml
        logback-spring.xml

• jpa&mysql pom依赖
  在spring中要使用jpa时只需引入相应的起步依赖，spring就会自动开启相关的功能。如果使用mysql作为存储，也只需引入相应的依赖。

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

• application.yml中数据源的配置

spring:
  test:
    datasource:
      url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8
      username: xxx
      password: xxx
      driver-class-name: com.mysql.jdbc.Driver
      test-while-idle: true
      time-between-eviction-runs-millis: 3000
      min-evictable-idle-time-millis: 14400000
  prod:
    datasource:
      url: jdbc:mysql://127.0.0.1:3306/prod?useUnicode=true&characterEncoding=UTF-8
      username: xxx
      password: xxx
      driver-class-name: com.mysql.jdbc.Driver
      test-while-idle: true
      time-between-eviction-runs-millis: 3000
      min-evictable-idle-time-millis: 14400000
  jpa:
    show-sql: true

• model&repository
  这里两个model的要在不同的package路径下，repository也要用不同package路径区分；否则在下文配置Entity、Repository扫描时势必会配置相同的路径，从而会导致Bean虽然能reload成功，但是库和表在对应时会出错。

Person.java

package com.demo.multi.datasource.model.prod;

@Data
@Entity(name = "person")
public class Person {
    @Id
    @Column(name = "local_id", insertable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer localId;

    private String name;

    @Enumerated(EnumType.STRING)
    private SexEnum sex;
}

User.java

package com.demo.multi.datasource.model.test;

@Data
@Entity(name = "user")
public class User {

    @Id
    @Column(name = "local_id", insertable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer localId;

    private String name;

    @Enumerated(EnumType.STRING)
    private SexEnum sex;
}

PersonRepository.java

package com.demo.multi.datasource.repository.prod;

public interface PersonRepository extends CrudRepository<Person, Integer> {
    Person findByName(String name);
}

UserRepository.java

package com.demo.multi.datasource.repository.test;

public interface UserRepository extends CrudRepository<User, Integer> {
    User findByName(String name);
}

• 配置多路DataSource
  这里通过使用注解的形式，告诉springboot自动的从application.yml文件中获取正确的数据源参数，并创建对应的数据源。

1. ConfigurationProperties配置DataSource所需的参数
2. 需要用@Primary注解标注其中的一个数据源，否则bean会有冲突

@Configuration
public class DataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties(prefix = "spring.test.datasource")
    public DataSource testDatasource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.prod.datasource")
    public DataSource prodDatasource() {
        return DataSourceBuilder.create().build();
    }
}

• 配置EntityManagerFactoryBean等

1. basePackages配置repository扫描路径
2. entityManagerFactoryRef：实体配置
3. transactionManagerRef：事务管理
4. DataSource等bean用Qualifier注解限定

ProdDbConfig.java

@Configuration
@EnableJpaRepositories(
        basePackages = {
                "com.demo.multi.datasource.repository.prod"
        },
        entityManagerFactoryRef = "prodEntityManagerFactory",
        transactionManagerRef = "prodTransactionManager"
)
@EntityScan("com.demo.multi.datasource.model.prod")
public class ProdDbConfig {

    @Autowired
    @Qualifier("prodDatasource")
    public DataSource prodDatasource;

    @Bean
    @Qualifier("prodTransactionManager")
    PlatformTransactionManager prodTransactionManager() {
        return new JpaTransactionManager(prodEntityManagerFactory().getObject());
    }

    @Bean
    @Qualifier("prodEntityManagerFactory")
    LocalContainerEntityManagerFactoryBean prodEntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        // BeanFactory参数配置
        factoryBean.setDataSource(prodDatasource);
        factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        factoryBean.setPackagesToScan("com.demo.multi.datasource.model.prod");
        factoryBean.setPersistenceUnitName("prod-dao-p");
        // hibernate个性化属性配置
        Properties properties = new Properties();
        properties.setProperty("hibernate.hbm2ddl.auto", "none");
        properties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");
        properties.setProperty("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
        properties.setProperty("hibernate.implicit_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy");
        factoryBean.setJpaProperties(properties);

        return factoryBean;
    }
}

TestDbConfig.java

@Configuration
@EnableJpaRepositories(
        basePackages = {
                "com.demo.multi.datasource.repository.test"
        },
        entityManagerFactoryRef = "testEntityManagerFactory",
        transactionManagerRef = "testTransactionManager"
)
@EntityScan("com.demo.multi.datasource.model.test")
public class TestDbConfig {

    @Autowired
    @Qualifier("testDatasource")
    public DataSource testDatasource;

    @Primary
    @Bean
    @Qualifier("testTransactionManager")
    PlatformTransactionManager testTransactionManager() {
        return new JpaTransactionManager(testEntityManagerFactory().getObject());
    }

    @Primary
    @Bean
    @Qualifier("testEntityManagerFactory")
    LocalContainerEntityManagerFactoryBean testEntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        factoryBean.setDataSource(testDatasource);
        factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        factoryBean.setPackagesToScan("com.demo.multi.datasource.model.test");
        factoryBean.setPersistenceUnitName("test-dao-p");
        Properties properties = new Properties();
        properties.setProperty("hibernate.hbm2ddl.auto", "none");
        properties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");
        properties.setProperty("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
        properties.setProperty("hibernate.implicit_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy");
        factoryBean.setJpaProperties(properties);

        return factoryBean;
    }
}

• 查询
  这里使用repository直接查询

	@Autowired
    private UserRepository userRepository;

    @Autowired
    private PersonRepository personRepository;

    public User getUser(String name) {
        return userRepository.findByName(name);
    }

    public Person getPerson(String name) {
        return personRepository.findByName(name);
    }