如何在API调用后动态建立次级数据库连接？当前为启动时初始化

需求背景 #

当前次级数据库连接在应用启动时初始化，需实现调用指定API后，根据接口返回的数据库配置信息（IP、库名、用户名、密码等）动态重建次级数据库连接。

现有代码参考 #

Controller 代码 #

@GetMapping("/updateSecondaryDatabase")
public ResponseEntity<String> updateSecondaryDatabase(@RequestParam Long propertyId) {
    return dashboardService.updateSecondaryDatabaseConnection(propertyId);
}

Service 实现代码 #

public ResponseEntity<String> updateSecondaryDatabaseConnection(Long propertyId) {
    Optional<NetworkConfiguration> configOpt = networkConfigurationRepository
                .findByPropertyIdAndIsActiveAndIsDelete(propertyId, true, false);
    if (configOpt.isPresent()) {
        NetworkConfiguration config = configOpt.get();
        String dbUrl = "jdbc:mysql://" + config.getIpAddress() + config.getDbName()
                + "?useSSL=false&useUnicode=true&characterEncoding=utf-8";
        String dbUser = config.getDbUser();
        String dbPassword = config.getDbPassword();
        return ResponseEntity.ok("Database connection updated successfully.");
    } else {
        System.err.println("No configuration found for propertyId: " + propertyId);
        return ResponseEntity.status(404).body("No configuration found for propertyId: " + propertyId);
    }
}

次级数据库连接配置代码 #

package com.wairport.app.config;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.wairport.app.secondary.repository",
        entityManagerFactoryRef = "secondEntityManagerFactory",
        transactionManagerRef = "secondTransactionManager"
)
public class SecondDataSourceConfig {

    @Bean(name = "secondDataSource")
    public DataSource secondDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/radius?useSSL=false&useUnicode=true&characterEncoding=utf-8");
        dataSource.setUsername("root");
        dataSource.setPassword("root123");
        return dataSource;
    }

    @Bean(name = "secondEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("secondDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.wairport.app.secondary.model")
                .persistenceUnit("second")
                .build();
    }

    @Bean(name = "secondTransactionManager")
    public PlatformTransactionManager secondTransactionManager(
            @Qualifier("secondEntityManagerFactory") EntityManagerFactory secondEntityManagerFactory) {
        return new JpaTransactionManager(secondEntityManagerFactory);
    }
}

实现步骤 #

1. 创建动态数据源持有者类 #

通过Holder类统一管理可动态替换的数据源、EntityManagerFactory和事务管理器实例，保证线程可见性：

@Component
public class DynamicSecondaryDataSourceHolder {
    private volatile DataSource dataSource;
    private volatile EntityManagerFactory entityManagerFactory;
    private volatile PlatformTransactionManager transactionManager;

    // Getter和Setter方法
    public DataSource getDataSource() { return dataSource; }
    public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; }

    public EntityManagerFactory getEntityManagerFactory() { return entityManagerFactory; }
    public void setEntityManagerFactory(EntityManagerFactory entityManagerFactory) { this.entityManagerFactory = entityManagerFactory; }

    public PlatformTransactionManager getTransactionManager() { return transactionManager; }
    public void setTransactionManager(PlatformTransactionManager transactionManager) { this.transactionManager = transactionManager; }
}

2. 修改原数据源配置类 #

将初始配置的Bean注入到Holder中，保留启动时的默认连接：

@Configuration
@EnableJpaRepositories(
        basePackages = "com.wairport.app.secondary.repository",
        entityManagerFactoryRef = "secondEntityManagerFactory",
        transactionManagerRef = "secondTransactionManager",
        repositoryFactoryBeanClass = DynamicJpaRepositoryFactoryBean.class
)
public class SecondDataSourceConfig {

    @Autowired
    private DynamicSecondaryDataSourceHolder dataSourceHolder;

    @Bean(name = "secondDataSource")
    public DataSource secondDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/radius?useSSL=false&useUnicode=true&characterEncoding=utf-8");
        dataSource.setUsername("root");
        dataSource.setPassword("root123");
        dataSourceHolder.setDataSource(dataSource);
        return dataSource;
    }

    @Bean(name = "secondEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("secondDataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean emf = builder
                .dataSource(dataSource)
                .packages("com.wairport.app.secondary.model")
                .persistenceUnit("second")
                .build();
        emf.afterPropertiesSet();
        dataSourceHolder.setEntityManagerFactory(emf.getObject());
        return emf;
    }

    @Bean(name = "secondTransactionManager")
    public PlatformTransactionManager secondTransactionManager(
            @Qualifier("secondEntityManagerFactory") EntityManagerFactory secondEntityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager(secondEntityManagerFactory);
        dataSourceHolder.setTransactionManager(transactionManager);
        return transactionManager;
    }
}

3. 完善Service动态更新逻辑 #

在Service方法中创建新的数据库连接组件，并替换Holder中的实例，同时清理旧资源：

@Service
public class DashboardService {

    @Autowired
    private NetworkConfigurationRepository networkConfigurationRepository;
    @Autowired
    private DynamicSecondaryDataSourceHolder dataSourceHolder;
    @Autowired
    private EntityManagerFactoryBuilder entityManagerFactoryBuilder;

    public ResponseEntity<String> updateSecondaryDatabaseConnection(Long propertyId) {
        Optional<NetworkConfiguration> configOpt = networkConfigurationRepository
                .findByPropertyIdAndIsActiveAndIsDelete(propertyId, true, false);
        if (configOpt.isPresent()) {
            NetworkConfiguration config = configOpt.get();
            // 1. 创建新的DataSource（生产环境建议用HikariCP等连接池替代DriverManagerDataSource）
            DriverManagerDataSource newDataSource = new DriverManagerDataSource();
            newDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
            // 修正URL拼接逻辑，补充斜杠和端口（若IP未包含端口）
            String dbUrl = "jdbc:mysql://" + config.getIpAddress() + "/" + config.getDbName()
                    + "?useSSL=false&useUnicode=true&characterEncoding=utf-8";
            newDataSource.setUrl(dbUrl);
            newDataSource.setUsername(config.getDbUser());
            newDataSource.setPassword(config.getDbPassword());

            // 2. 创建新的EntityManagerFactory
            LocalContainerEntityManagerFactoryBean newEmf = entityManagerFactoryBuilder
                    .dataSource(newDataSource)
                    .packages("com.wairport.app.secondary.model")
                    .persistenceUnit("second")
                    .build();
            newEmf.afterPropertiesSet();

            // 3. 创建新的TransactionManager
            JpaTransactionManager newTransactionManager = new JpaTransactionManager(newEmf.getObject());

            // 4. 清理旧资源
            if (dataSourceHolder.getEntityManagerFactory() != null) {
                dataSourceHolder.getEntityManagerFactory().close();
            }

            // 5. 更新Holder中的实例
            dataSourceHolder.setDataSource(newDataSource);
            dataSourceHolder.setEntityManagerFactory(newEmf.getObject());
            dataSourceHolder.setTransactionManager(newTransactionManager);

            return ResponseEntity.ok("Database connection updated successfully.");
        } else {
            System.err.println("No configuration found for propertyId: " + propertyId);
            return ResponseEntity.status(404).body("No configuration found for propertyId: " + propertyId);
        }
    }
}

4. 自定义Repository工厂类适配动态EntityManager #

让Spring Data JPA能动态获取最新的EntityManager实例：

public class DynamicJpaRepositoryFactoryBean<T extends Repository<S, ID>, S, ID> extends JpaRepositoryFactoryBean<T, S, ID> {

    @Autowired
    private DynamicSecondaryDataSourceHolder dataSourceHolder;

    public DynamicJpaRepositoryFactoryBean(Class<? extends T> repositoryInterface) {
        super(repositoryInterface);
    }

    @Override
    protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
        EntityManager dynamicEntityManager = dataSourceHolder.getEntityManagerFactory().createEntityManager();
        return super.createRepositoryFactory(dynamicEntityManager);
    }

    @Override
    public void setEntityManager(EntityManager entityManager) {
        super.setEntityManager(dataSourceHolder.getEntityManagerFactory().createEntityManager());
    }
}

关键注意事项 #

• 线程安全：用volatile修饰Holder中的实例，保证多线程下的可见性；更新操作尽量原子化。
• 连接池优化：生产环境替换DriverManagerDataSource为HikariCP等连接池，提升性能并支持连接复用。
• 事务一致性：使用次级数据库的Service方法需标注@Transactional("secondTransactionManager")，确保事务绑定最新的事务管理器。

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