Spring Boot 多个外部数据源存储在内部数据库中

Question

我有一个 spring boot 项目，并且我有一个内部数据库，其中包含 application.properties 上的配置。在这个数据库中，我有一个 Company 表，其中包含与外部数据库的连接信息（所有外部数据库都具有相同的结构）。

我创建了一个在需要时创建数据源的类：

public class PgDataSource {

    private static Map<Long, DataSource> dataSourceMap = new HashMap<>();

    private static void createDataSource(Company company) {

        HikariConfig hikariConfig = new HikariConfig();

        hikariConfig.setMaximumPoolSize(10);

        hikariConfig.setMinimumIdle(1);

        hikariConfig.setJdbcUrl("jdbc:postgresql://"+company.getUrl()+"/"+company.getIdClient());

        hikariConfig.setUsername(company.getUsername());

        hikariConfig.setPassword(company.getPassword());

        dataSourceMap.put(company.getId(), new HikariDataSource(hikariConfig));

    }

    public static DataSource getDataSource(Company company) {

        if (!dataSourceMap.containsKey(company.getId()))

            createDataSource(company);

        return dataSourceMap.get(company.getId());

    }

}

你能告诉我这个解决方案是否是最好的，我是否可以在这个解决方案中使用 JPA？

Answer 1

设置的困难不是多个数据源，而是它们是动态的，即在运行时确定的事实。除了DataSourceJPA 使用EntityManagerFactory和TransactionManager，它们是静态确定的，即在编译时。因此，将 JPA 与动态数据源一起使用并不容易。在 Spring Boot 2 中，您可以尝试AbstractRoutingDataSource，它允许基于某些（线程绑定）上下文将 JPA 调用路由到不同的数据源。这是一个如何使用它的示例和一个演示应用程序。或者，您可以将设置转换为静态设置，然后使用常规的多数据源方法。缺点是“公司”列表将在编译时固定，因此可能不是您想要的。

Answer 2

我的解决方案：我使用@Primary注释为我的本地数据库创建了第一个数据源。@Configuration@EnableTransactionManagement@EnableJpaRepositories(&nbsp; &nbsp; entityManagerFactoryRef = "localEntityManagerFactory",&nbsp; &nbsp; basePackages = {"fr.axygest.akostaxi.local"})public class LocalConfig {&nbsp; &nbsp; @Primary&nbsp; &nbsp; @Bean(name = "dataSource")&nbsp; &nbsp; @ConfigurationProperties(prefix = "spring.datasource")&nbsp; &nbsp; public DataSource dataSource() {&nbsp; &nbsp; &nbsp; &nbsp; return DataSourceBuilder.create().build();&nbsp; &nbsp; }&nbsp; &nbsp; @Primary&nbsp; &nbsp; @Bean(name = "localEntityManagerFactory")&nbsp; &nbsp; public LocalContainerEntityManagerFactoryBean entityManagerFactory(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EntityManagerFactoryBuilder builder,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @Qualifier("dataSource") DataSource dataSource) {&nbsp; &nbsp; &nbsp; &nbsp; return builder&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .dataSource(dataSource)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .packages("fr.axygest.akostaxi.local.model")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .persistenceUnit("local")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .build();&nbsp; &nbsp; }&nbsp; &nbsp; @Primary&nbsp; &nbsp; @Bean(name = "transactionManager")&nbsp; &nbsp; public PlatformTransactionManager transactionManager(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @Qualifier("localEntityManagerFactory") EntityManagerFactory&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; entityManagerFactory&nbsp; &nbsp; ) {&nbsp; &nbsp; &nbsp; &nbsp; return new JpaTransactionManager(entityManagerFactory);&nbsp; &nbsp; }}接下来，对于保存在本地数据库company表中的x个外部数据库，我使用的是AbstractRoutingDataSource我将当前上下文存储为 ThreadLocal ：public class ThreadPostgresqlStorage {&nbsp; &nbsp; private static ThreadLocal<Long> context = new ThreadLocal<>();&nbsp; &nbsp; public static void setContext(Long companyId) {&nbsp; &nbsp; &nbsp; &nbsp; context.set(companyId);&nbsp; &nbsp; }&nbsp; &nbsp; public static Long getContext() {&nbsp; &nbsp; &nbsp; &nbsp; return context.get();&nbsp; &nbsp; }}我定义了 RoutingSource 来扩展AbstractRoutingDataSource：public class RoutingSource extends AbstractRoutingDataSource{&nbsp; &nbsp; @Override&nbsp; &nbsp; protected Object determineCurrentLookupKey() {&nbsp; &nbsp; &nbsp; &nbsp; return ThreadPostgresqlStorage.getContext();&nbsp; &nbsp; }}配置类创建保存在公司表中的所有数据库连接：@Configuration@EnableJpaRepositories(&nbsp; &nbsp; &nbsp; &nbsp; basePackages = {"fr.axygest.akostaxi.postgresql"},&nbsp; &nbsp; &nbsp; &nbsp; entityManagerFactoryRef = "pgEntityManager")@EnableTransactionManagementpublic class PgConfig {&nbsp; &nbsp; private final CompanyRepository companyRepository;&nbsp; &nbsp; @Autowired&nbsp; &nbsp; public PgConfig(CompanyRepository companyRepository) {&nbsp; &nbsp; &nbsp; &nbsp; this.companyRepository = companyRepository;&nbsp; &nbsp; }&nbsp; &nbsp; @Bean(name = "pgDataSource")&nbsp; &nbsp; public DataSource pgDataSource() {&nbsp; &nbsp; &nbsp; &nbsp; RoutingSource routingSource = new RoutingSource();&nbsp; &nbsp; &nbsp; &nbsp; List<Company> companies = companyRepository.findAll();&nbsp; &nbsp; &nbsp; &nbsp; HashMap<Object, Object> map = new HashMap<>(companies.size());&nbsp; &nbsp; &nbsp; &nbsp; companies.forEach(company -> {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; map.put(company.getId(), createDataSource(company));&nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; routingSource.setTargetDataSources(map);&nbsp; &nbsp; &nbsp; &nbsp; routingSource.afterPropertiesSet();&nbsp; &nbsp; &nbsp; &nbsp; return routingSource;&nbsp; &nbsp; }&nbsp; &nbsp; @Bean(name = "pgEntityManager")&nbsp; &nbsp; public LocalContainerEntityManagerFactoryBean pgEntityManager(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EntityManagerFactoryBuilder builder,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @Qualifier("pgDataSource") DataSource dataSource) {&nbsp; &nbsp; &nbsp; &nbsp; return builder&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .dataSource(dataSource)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .packages("fr.axygest.akostaxi.postgresql.model")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .persistenceUnit("pg")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .properties(jpaProperties())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .build();&nbsp; &nbsp; }&nbsp; &nbsp; private DataSource createDataSource(Company company) {&nbsp; &nbsp; &nbsp; &nbsp; HikariConfig hikariConfig = new HikariConfig();&nbsp; &nbsp; &nbsp; &nbsp; hikariConfig.setMaximumPoolSize(10);&nbsp; &nbsp; &nbsp; &nbsp; hikariConfig.setMinimumIdle(1);&nbsp; &nbsp; &nbsp; &nbsp; hikariConfig.setJdbcUrl("jdbc:postgresql://" + company.getUrl() + "/" + company.getIdClient());&nbsp; &nbsp; &nbsp; &nbsp; hikariConfig.setUsername(company.getUsername());&nbsp; &nbsp; &nbsp; &nbsp; hikariConfig.setPassword(company.getPassword());&nbsp; &nbsp; &nbsp; &nbsp; return new HikariDataSource(hikariConfig);&nbsp; &nbsp; }&nbsp; &nbsp; private Map<String, Object> jpaProperties() {&nbsp; &nbsp; &nbsp; &nbsp; Map<String, Object> props = new HashMap<String, Object>();&nbsp; &nbsp; &nbsp; &nbsp; props.put("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");&nbsp; &nbsp; &nbsp; &nbsp; return props;&nbsp; &nbsp; }}