Spring Boot多数据源JPA配置与原生查询实践

package com.xxxx.powwow.config;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.HashMap;

@Configuration
@PropertySource({"classpath:application.properties"})
@EnableJpaRepositories(
        basePackages = {"com.xxxx.powwow.dao.pims", "com.xxxx.powwow.repositories.pims"},
        entityManagerFactoryRef = "pimsEntityManager", // 引用pims数据源的EntityManagerFactory
        transactionManagerRef = "pimsTransactionManager") // 引用pims数据源的事务管理器
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;

    /**
     * 配置pims数据源。
     * 使用@ConfigurationProperties注解从application.properties中读取以"spring.datasource1"为前缀的配置。
     */
    @Bean
    @ConfigurationProperties(prefix="spring.datasource1")
    public DataSource pimsDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 配置pims数据源的EntityManagerFactory。
     * 关键在于通过em.setPersistenceUnitName("pimsPersistenceUnit")设置唯一的持久化单元名称。
     */
    @Bean(name = "pimsEntityManager") // 明确指定Bean名称
    public LocalContainerEntityManagerFactoryBean pimsEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(pimsDataSource());
        em.setPackagesToScan(new String[] {"com.xxxx.powwow.entities.pims"}); // 扫描pims数据源对应的实体类包
        em.setPersistenceUnitName("pimsPersistenceUnit"); // 设定唯一的持久化单元名称

        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;
    }

    /**
     * 配置pims数据源的事务管理器。
     */
    @Bean(name = "pimsTransactionManager") // 明确指定Bean名称
    public PlatformTransactionManager pimsTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(pimsEntityManager().getObject());
        return transactionManager;
    }
}

package com.xxxx.powwow.dao.pims;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.Date;
import java.util.List;

@Component
@Transactional("pimsTransactionManager") // 确保使用pims数据源的事务管理器
public class BookingHistoryReportDao {

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

    // 通过unitName明确指定注入pims数据源的EntityManager
    @PersistenceContext(unitName = "pimsPersistenceUnit") 
    private EntityManager entityManager;

    public void executeBookingHistoryReport(Date startDate, Date endDate, List<Integer> companyIds) {
        final String sql = getSQLBookingHistoryReportDao();
        try {
            // 使用正确关联的EntityManager执行原生查询
            Query qry = entityManager.createNativeQuery(sql);
            List<String> merchants = qry.getResultList();
            logger.info("Done executing native query on pims database.");
        } catch (Exception e) {
            logger.error("Error executing query for BookingHistoryReport.", e);
            logger.info("SQL attempted: " + sql);
        }
    }

    private String getSQLBookingHistoryReportDao() {
        // 假设Merchants表在pims数据库中
        return "select company_name from Merchants limit 100";
    }
}