Spring Boot多数据库JPA：如何在次级库执行原生查询？

我有两个PostgreSQL数据库和一个Spring Boot应用，目前可以成功连接两个库并分别执行事务，使用次级数据库（pims）的普通JPA查询一直正常。但在次级库执行原生查询时，系统会错误地操作主数据库（powwow）：

例如，merchants表只存在于次级库（pims），执行原生查询时却报错：

PSQLException: ERROR: relation "merchants" does not exist

如果在主库（powwow）的表上执行相同的原生查询则完全正常。我认为问题出在次级库（pims）的数据源配置上，另外使用com.xxxx.powwow.entities.pims包下的仓库执行次级库的非原生查询是可以正常运行的。

问题 #

如何让com.xxxx.powwow.dao.pims包下的DAO执行原生查询时正确指向次级数据库（pims）？

配置代码：PersistencePimsAutoConfiguration.java #

@Configuration
@PropertySource({"classpath:application.properties"})
@EnableJpaRepositories(
        basePackages = {"com.xxxx.powwow.dao.pims", "com.xxxx.powwow.repositories.pims"},
        entityManagerFactoryRef = "pimsEntityManager",
        transactionManagerRef = "pimsTransactionManager")
public class PersistencePimsAutoConfiguration {

    private Logger logger = LogManager.getLogger(PersistencePimsAutoConfiguration.class);

    @Value("${spring.datasource1.jdbc-url}")
    private String url;
    @Value("${spring.datasource1.username}")
    private String username;
    @Value("${spring.jpa.hibernate.ddl-auto}")
    private String hbm2ddl;
    @Value("${spring.jpa.database-platform}")
    private String platform;
    @Value("${spring.jpa.properties.hibernate.dialect}")
    private String dialect;
    @Value("${spring.profiles.active}")
    private String profile;

    @Bean
    @ConfigurationProperties(prefix="spring.datasource1")
    public DataSource pimsDataSource() {
        return DataSourceBuilder.create().build();
    }

    //@Bean(name = "pimsEntityManager")
    @Bean
    public LocalContainerEntityManagerFactoryBean pimsEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(pimsDataSource());
        em.setPackagesToScan(new String[] {"com.xxxx.powwow.entities.pims"});
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", hbm2ddl);
        properties.put("hibernate.dialect", dialect);
        em.setJpaPropertyMap(properties);
        String host = null;
        try {
            host = InetAddress.getLocalHost().getHostName();
        } catch (UnknownHostException e) {
            throw new RuntimeException(e);
        }
        logger.info("Setting spring.datasource1 (pims): hibernate.hbm2ddl.auto='"+hbm2ddl+"', platform='"+platform+"', url='"+url+"', username='"+username+"', host='"+host+"', profile='"+profile+"'.");
        return em;
    }

    @Bean
    public PlatformTransactionManager pimsTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(pimsEntityManager().getObject());
        return transactionManager;
    }
}

DAO代码：BookingHistoryReportDao.java（包路径com.xxxx.powwow.dao.pims） #

@Component
@Transactional("pimsTransactionManager")
public class BookingHistoryReportDao {

    private Logger logger = LogManager.getLogger(BookingHistoryReportDao.class);

    @PersistenceContext
    private EntityManager entityManager;

    public void executeBookingHistoryReport(Date startDate, Date endDate, List<Integer> companyIds) {
        final String sql = getSQLBookingHistoryReportDao();
        try {
            Query qry = entityManager.createNativeQuery(sql);
            List<String> merchants = qry.getResultList();
            logger.info("done");
        } catch (Exception e) {
            logger.error("Error executing query for BookingHistoryReport.", e);
            logger.info(sql);
        }
    }

    private String getSQLBookingHistoryReportDao() {
        return "select company_name from Merchants limit 100";
    }
}

解决方案 #

问题核心是@PersistenceContext未指定对应EntityManager，导致注入了默认的主库实例，按以下步骤修改即可：

1. 给次级库EntityManagerBean指定名称
   取消PersistencePimsAutoConfiguration中pimsEntityManager方法的注释，明确Bean名称：

   @Bean(name = "pimsEntityManager")
   public LocalContainerEntityManagerFactoryBean pimsEntityManager() {
       // 原有代码保持不变
   }
   

2. 在DAO中指定注入目标EntityManager
   修改BookingHistoryReportDao中的@PersistenceContext注解，绑定到次级库的EntityManager：

   @PersistenceContext(unitName = "pimsEntityManager")
   private EntityManager entityManager;
   

3. 验证表名大小写匹配
   PostgreSQL默认不区分标识符大小写，但如果创建表时使用了双引号，就会严格区分。你查询中用了Merchants，如果实际表名是小写的merchants，建议改成小写：

   private String getSQLBookingHistoryReportDao() {
       return "select company_name from merchants limit 100";
   }
   

修改完成后，DAO中的EntityManager会绑定到pims数据源，执行原生查询时就会正确操作次级数据库了。

内容的提问来源于stack exchange，提问作者Richard