SpringBoot JPA 多数据源，使用 QueryDSL 时遇到的坑

Author： Quan
发布时间：December 30, 2021
3829views
2 comments
8470 words
Categories：主页笔记

## 前言
最近一个需求需要使用到多数据源，记录一下踩到的坑。

## 环境
- SpringBoot  2.2.1.RELEASE
- QueryDSL 5.0.0

## 多数据源配置

```java
@Configuration
public class DataSourceConfiguration {

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

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

}
```

```java
@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
@EnableJpaRepositories(
    repositoryFactoryBeanClass = BaseRepositoryFactory.class,
    entityManagerFactoryRef = "entityManagerFactoryPrimary",
    transactionManagerRef = "transactionManagerPrimary",
    basePackages = {"com.xxx.xxx.xxx.repository.primary"})
public class PrimaryConfig {

private static final String DEFAULT_IMPLICIT_STRATEGY = "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy";

private DataSource primaryDataSource;
    private JpaProperties jpaProperties;
    private HibernateProperties hibernateProperties;

public PrimaryConfig(@Qualifier("primaryDataSource") DataSource primaryDataSource,
        JpaProperties jpaProperties, HibernateProperties hibernateProperties) {
        this.primaryDataSource = primaryDataSource;
        this.jpaProperties = jpaProperties;
        this.hibernateProperties = hibernateProperties;
    }

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

@Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {

return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()));

// 这种创建出来会有缓存问题
//        return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject())
//            .createEntityManager(SynchronizationType.SYNCHRONIZED);
    }

@Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(
        EntityManagerFactoryBuilder builder) {
        Map<String, Object> vendorProperties = getVendorProperties();
//        vendorProperties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        return builder
            .dataSource(primaryDataSource)
            .packages("com.xxx.xxx.xxx.entity.primary") //设置实体类所在位置
            .persistenceUnit("primaryPersistenceUnit")
            .properties(getVendorProperties())
            .build();
    }

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

@Bean
    public JPAQueryFactory queryFactory(
        @Qualifier("entityManagerPrimary") EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }

}
```

```java

@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
@EnableJpaRepositories(
    repositoryFactoryBeanClass = BaseRepositoryFactory.class,
    entityManagerFactoryRef = "entityManagerFactorySecondary",
    transactionManagerRef = "transactionManagerSecondary",
    basePackages = {"com.xxx.xxx.xxx.repository.secondary"}) //设置Repository所在位置
public class SecondaryConfig {

private static final String DEFAULT_PHYSICAL_STRATEGY = "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy";

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

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

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

@Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(entityManagerFactorySecondary(builder).getObject()));
    }

@Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(
        EntityManagerFactoryBuilder builder) {

Map<String, Object> vendorProperties = getVendorProperties();
        vendorProperties.put("hibernate.hbm2ddl.auto", "none");
        // 在这里设置方言
        vendorProperties.put("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect");
        return builder
            .dataSource(secondaryDataSource)
            //设置实体类所在位置
            .packages("com.xxx.xxx.xxx.entity.secondary")
            .persistenceUnit("secondaryPersistenceUnit")
            .properties(vendorProperties)
            .build();
    }

@Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(
            Objects.requireNonNull(entityManagerFactorySecondary(builder).getObject()));
    }

@Bean
    public JPAQueryFactory queryFactorySecondary(
        @Qualifier("entityManagerSecondary") EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}

```

#### 配置文件
```yaml
spring:
  datasource:
    primary:
      jdbc-url: jdbc:postgresql://1.1.1.1:5432/xxxx
      username: postgres
      password: xxxxxx
      driver-class-name: org.postgresql.Driver
    secondary:
      username: sa
      password: xxxxxx
      jdbc-url: jdbc:sqlserver://1.1.1.1:1433;databasename=xxxx
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    properties:
      hibernate:
        hbm2ddl:
          auto: update
#        primary-dialect: org.hibernate.dialect.PostgreSQLDialect
#        secondary-dialect: org.hibernate.dialect.SQLServer2008Dialect
```

## 遇到的坑

1. 方言配置不生效
	安装查到的资料配置JPA方言，但启动后控制台输出SQL Server 数据库的方言也是PostgreSQLDialect，说明没有走日志，同时自动识别也没识别出来。最后实在配置类里面手动写入的，不知道是不是我没配置对。相关代码：
	```yaml
@Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(
        EntityManagerFactoryBuilder builder) {

2. 解决了方言问题发现又出现使用 QueryDSL 进行更新删除操作报错的问题，报错如下：
```java
Executing an update/delete query
```
经过排查，发现是缺少事务，通过在创建entityManager时增加参数 `SynchronizationType.SYNCHRONIZED` 解决。相关代码：
```java
    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
       return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject())
          .createEntityManager(SynchronizationType.SYNCHRONIZED);
    }
```

3. 使用 QueryDSL 查询缓存不更新，查询出来的结果始终是缓存数据，通过`entityManager.clear()` 后能查到新数据，但不可每次查询都去手动更新缓存吧，通过Google，看到了 https://springboot.io/t/topic/3892 这个帖子。按照帖子说的方式去创建 `entityManager`，果然能够自动更新缓存了。相关代码：
```java
    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {

return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()));

Last modification：December 30th, 2021 at 12:41 pm

If you think my article is useful to you, please feel free to appreciate

2 comments

22攻略
May 25th, 2023 at 08:03 pm

非常感谢你分享这篇文章，我从中学到了很多新的知识。

Reply
grfe
October 8th, 2022 at 06:34 pm

赞一个OωO

Reply

SpringBoot JPA 多数据源，使用 QueryDSL 时遇到的坑

Quan • 2021 年 12 月 30 日

## 前言
最近一个需求需要使用到多数据源，记录一下踩到的坑。

## 环境
- SpringBoot  2.2.1.RELEASE
- QueryDSL 5.0.0

## 多数据源配置

```java
@Configuration
public class DataSourceConfiguration {

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

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

}
```

private static final String DEFAULT_IMPLICIT_STRATEGY = "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy";

private DataSource primaryDataSource;
    private JpaProperties jpaProperties;
    private HibernateProperties hibernateProperties;

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

@Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {

return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()));

@Bean
    public JPAQueryFactory queryFactory(
        @Qualifier("entityManagerPrimary") EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }

}
```

```java

private static final String DEFAULT_PHYSICAL_STRATEGY = "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy";

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

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

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

@Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(
        EntityManagerFactoryBuilder builder) {

@Bean
    public JPAQueryFactory queryFactorySecondary(
        @Qualifier("entityManagerSecondary") EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}

```

## 遇到的坑

return SharedEntityManagerCreator.createSharedEntityManager(Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()));

SpringBoot JPA 多数据源，使用 QueryDSL 时遇到的坑

Leave a Comment Cancel reply

2 comments

自建 RSSHub 公开使用说明

ELF_RSS 订阅插件使用、安装教程

天翼云盘目录直链解析批量下载

酷Q 推特转发机器人插件

酷Q RSS订阅转发插件安装教程实现Twitter、YouTube等转发

java实现PV操作解决生产者-消费者问题

天翼云盘目录直链解析批量下载

Eclipse下打包JavaWeb程序为war及发布到Linux服务器Tomcat上

为京都动画祈福 #PrayForKyoAni

动态规划 02

SpringBoot JPA 多数据源，使用 QueryDSL 时遇到的坑