Springboot2.x Jpa ——详细的多数据源配置和使用案例

---

前言

最近有个需求，要同时操作多个数据源，并分别进行curd操作。于是百度了一番，编写依赖测试demo，搞定了这个问题。

以下demo实现功能是：

• 在数据源一中，给测试表新增一条数据。
• 同时在数据源二中，给测试表新增数据。

---

一、准备阶段

1.数据表和存储过程

1.1 数据源一

-- Create table
create table TEST_DATA
(
  id   NUMBER,
  name VARCHAR2(30)
)
tablespace PUSH
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

1.2 数据源二

-- Create table
create table TEST_JPA
(
  id   NUMBER not null,
  name VARCHAR2(300)
)
tablespace TBS_BSS_NHIS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column TEST_JPA.id
  is 'id';

二、集成配置

1.引入依赖

    compile group: 'org.projectlombok', name: 'lombok', version: '1.18.10'
    annotationProcessor group: 'org.projectlombok', name: 'lombok', version: '1.18.10'
    compile 'org.springframework.boot:spring-boot-starter-web'
    compile group: 'org.springframework.boot' ,name: 'spring-boot-starter-aop'
    compile group: 'org.springframework.boot' ,name: 'spring-boot-starter-data-jpa'
    compile 'com.oracle:ojdbc6:11.2.0.3'
    compile 'com.alibaba:druid-spring-boot-starter:1.1.20'
    compile group: 'org.apache.commons' ,name: 'commons-lang3'
    compile group: 'com.alibaba', name: 'fastjson', version: '1.2.68'
    testCompile group: 'org.springframework.boot',name: 'spring-boot-starter-test'
    testCompile group: 'junit', name: 'junit', version: '4.12'

2.设置application.yml

spring:
  datasource:
    driverClassName: oracle.jdbc.driver.OracleDriver

    # 使用 alibaba druid 连接池、监控
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 数据源一
      primary :
        url: jdbc:oracle:thin:@xxx.xx.xx.xxxx:port:cfc
        username: xxx
        password: xx
       # 数据源二
      secondary :
        url: jdbc:oracle:thin:@xxx.xx.xx.xxxx:port:cfc
        username: xxx
        password: xx

      initial-size: 2
      min-idle: 5
      max-active: 10
      max-wait: 5000
      validation-query: SELECT 1
      test-on-borrow: false
      test-while-idle: true
      time-between-eviction-runs-millis: 18800
      web-stat-filter:
        enabled: true
        exclusions: js,gif,jpg,png,css,ico,/druid/*
#      stat-view-servlet:
#        enabled: true
#        login-username: root
#        login-password: druid2019

3.config配置多数据源

3.1 DataSourceConfig.java

@Configuration
public class DataSourceConfig {

    private static final Logger log = org.slf4j.LoggerFactory.getLogger(DataSourceConfig.class);

    /**
     * 数据源一
     * 标红为yml文件中数据源路径：primary
     */
    @Primary
    @Bean(value = "primaryDataSource",initMethod = "init")
    @ConfigurationProperties("spring.datasource.druid.primary")
    public DataSource dataSourceOne(){
        log.info("Init DataSourceOne");
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 数据源二
     * 标红为yml文件中数据源路径：secondary
     */
    @Bean(value = "secondDataSource",initMethod = "init")
    @ConfigurationProperties("spring.datasource.druid.secondary")
    public DataSource dataSourceTwo(){
        log.info("Init DataSourceTwo");
        return DruidDataSourceBuilder.create().build();
    }
}

3.2.PrimaryJpaConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        //设置Repository所在位置-设置扫包范围
        basePackages = {"com.xxx.dao.first.mapper"})
public class PrimaryJpaConfig {

    @Autowired
    private JpaProperties jpaProperties;
    @Autowired
    private HibernateProperties hibernateProperties;

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;



    @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)
                // 设置jpa配置
                .properties(jpaProperties.getProperties())
                // 设置hibernate配置
                .properties(getVendorProperties())
                //设置实体类所在位置
                .packages("com.xxx.dao.first.model")
                // 设置持久化单元名，用于@PersistenceContext注解获取EntityManager时指定数据源
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    private Map getVendorProperties() {
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }

}

3.3.SecondJpaConfig.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecond",
        transactionManagerRef = "transactionManagerSecond",
        //设置Repository所在位置-设置扫包范围
        basePackages = {"com.xxx.dao.second.mapper"})
public class SecondJpaConfig {
    @Autowired
    private JpaProperties jpaProperties;
    @Autowired
    private HibernateProperties hibernateProperties;

    @Autowired
    @Qualifier("secondDataSource")
    private DataSource secondDataSource;

    @Bean(name = "secondEntityManager")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecond(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactorySecond")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecond(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondDataSource)
                // 设置jpa配置
                .properties(jpaProperties.getProperties())
                .properties(getVendorProperties())
                //设置实体类所在位置
                .packages("com.xxxx.dao.second.model")
                .persistenceUnit("secondPersistenceUnit")
                .build();
    }

    private Map getVendorProperties() {
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }

    @Bean(name = "transactionManagerSecond")
    public PlatformTransactionManager transactionManagerSecond(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecond(builder).getObject());
    }
}

4. 定义实体和映射

包路径：com.xxx.dao.first

4.1 TestDataModel.java

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity(name = "TEST_DATA")
public class TestDataModel implements Serializable {

    @Id
    @Column(name = "ID")
    // @GeneratedValue和@SequenceGenerator是JPA标准注解，
    // @GeneratedValue 定义主键生成策略
    // @SequenceGenerator 定义一个生成主键的序列
    // 结合起来才有效
    //定义主键生成策略为序列，并引用声明好的序列ID_SEQ
    long id;
    @Column(name = "NAME")
    String name;
    
}

4.2 TestDataMapper.java

@Repository
public interface TestDataMapper extends CrudRepository<TestDataModel, Long> {

}

包路径：com.xxx.dao.second

4.3 TestJpa.java

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity(name = "TEST_JPA")
public class TestJpa {

    @Id
    @Column(name = "ID")
    private long id;

    @Column(name = "NAME")
    private String name;

}

4.4 TestJpaMapper.java

@Repository
public interface TestJpaMapper extends CrudRepository<TestJpa, Long> {
}

三、测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = AppStart.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class TestOracle {

    @Resource
    private TestDataMapper testDataMapper;

    @Resource
    private TestJpaMapper testJpaMapper;



    @Test
    public void test4(){
        //数据源一
        TestDataModel testData = TestDataModel.builder().id(1234567).name("testData").build();
        testDataMapper.save(testData);

        //数据源二
        TestJpa testJpa = TestJpa.builder().id(7654321).name("testJpa").build();
        testJpaMapper.save(testJpa);
    }
}

四、如果更换为mysql数据库呢？

更换 application.yml里的配置即可

spring:
  datasource:
     primary:
       jdbcUrl: jdbc:mysql://127.0.0.1:3306/test1?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false
       username: root
       password: 12345678
       driver-class-name: com.mysql.cj.jdbc.Driver
     secondary:
       jdbcUrl: jdbc:mysql://127.0.0.1:3306/test2?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false
       username: root
       password: 12345678
       driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
    database-platform: org.hibernate.dialect.MySQL5Dialect

到此就结束了，各位看官可以自行cv代码。使用oracle数据库测试，当然也可以使用mysql数据库，测试结果就不贴图展示了，主要是我比较懒~。

---

总结

本章主要内容：

• 使用使用spring jpa 配置多数据源，其中使用alibaba druid 作为连接池。
• 注意每个数据源都有自己的扫包范围，别配置错误了
• 更换mysql，只需要更改连接信息配置即可。

参考：
https://blog.csdn.net/water_3700348/article/details/101671450