Spring Boot运行时多数据库切换配置咨询：基于application.properties与Data JPA

一、基础方案：同时连接多个数据库（分Repository独立操作） #

这种方式适合需要同时操作不同数据库的场景，通过划分实体类、Repository包，配合独立的数据源配置实现隔离。

1. 配置application.properties #

在配置文件中用前缀区分两个数据库的连接信息及JPA属性：

# 第一个数据库配置
spring.datasource.first.url=jdbc:mysql://localhost:3306/db_first
spring.datasource.first.username=root
spring.datasource.first.password=123456
spring.datasource.first.driver-class-name=com.mysql.cj.jdbc.Driver

# 第二个数据库配置
spring.datasource.second.url=jdbc:mysql://localhost:3306/db_second
spring.datasource.second.username=root
spring.datasource.second.password=123456
spring.datasource.second.driver-class-name=com.mysql.cj.jdbc.Driver

# 第一个数据库的JPA配置
spring.jpa.first.hibernate.ddl-auto=update
spring.jpa.first.show-sql=true
spring.jpa.first.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

# 第二个数据库的JPA配置
spring.jpa.second.hibernate.ddl-auto=update
spring.jpa.second.show-sql=true
spring.jpa.second.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

2. 编写第一个数据库的配置类 #

创建FirstDataSourceConfig，指定该数据源对应的实体类、Repository路径，配置EntityManagerFactory和事务管理器：

@Configuration
@EnableJpaRepositories(
        basePackages = "com.example.demo.repository.first",
        entityManagerFactoryRef = "firstEntityManagerFactory",
        transactionManagerRef = "firstTransactionManager"
)
public class FirstDataSourceConfig {

    @Primary // 标记为主数据源，避免多数据源冲突
    @Bean(name = "firstDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.first")
    public DataSource firstDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "firstEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean firstEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("firstDataSource") DataSource dataSource) {
        Map<String, String> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.ddl-auto", "update");
        jpaProperties.put("hibernate.show-sql", "true");
        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");

        return builder
                .dataSource(dataSource)
                .packages("com.example.demo.entity.first")
                .persistenceUnit("firstPU")
                .properties(jpaProperties)
                .build();
    }

    @Primary
    @Bean(name = "firstTransactionManager")
    public PlatformTransactionManager firstTransactionManager(
            @Qualifier("firstEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

3. 编写第二个数据库的配置类 #

类似创建SecondDataSourceConfig，对应第二个数据库的路径和配置：

@Configuration
@EnableJpaRepositories(
        basePackages = "com.example.demo.repository.second",
        entityManagerFactoryRef = "secondEntityManagerFactory",
        transactionManagerRef = "secondTransactionManager"
)
public class SecondDataSourceConfig {

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

    @Bean(name = "secondEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("secondDataSource") DataSource dataSource) {
        Map<String, String> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.ddl-auto", "update");
        jpaProperties.put("hibernate.show-sql", "true");
        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");

        return builder
                .dataSource(dataSource)
                .packages("com.example.demo.entity.second")
                .persistenceUnit("secondPU")
                .properties(jpaProperties)
                .build();
    }

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

4. 划分实体类与Repository #

• 第一个数据库的实体类放在com.example.demo.entity.first包下，Repository接口放在com.example.demo.repository.first包下（继承JpaRepository）
• 第二个数据库的实体类和Repository同理放在对应包下

使用时直接注入对应包下的Repository即可操作目标数据库。

二、进阶方案：运行时动态切换数据源 #

如果需要在同一个Repository中动态切换数据库，可通过AbstractRoutingDataSource实现数据源路由。

1. 定义数据源上下文持有者 #

存储当前线程的数据源标识：

public class DataSourceContextHolder {
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    public static void setDataSourceKey(String key) {
        CONTEXT_HOLDER.set(key);
    }

    public static String getDataSourceKey() {
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSourceKey() {
        CONTEXT_HOLDER.remove();
    }
}

2. 实现动态数据源路由类 #

继承AbstractRoutingDataSource，重写方法获取当前数据源标识：

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSourceKey();
    }
}

3. 配置动态数据源 #

修改配置类，将两个数据源注入动态路由数据源：

@Configuration
@EnableJpaRepositories(basePackages = "com.example.demo.repository")
public class DynamicDataSourceConfig {

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

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

    @Primary
    @Bean(name = "dynamicDataSource")
    public DataSource dynamicDataSource(
            @Qualifier("firstDataSource") DataSource firstDataSource,
            @Qualifier("secondDataSource") DataSource secondDataSource) {
        DynamicRoutingDataSource routingDataSource = new DynamicRoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("first", firstDataSource);
        targetDataSources.put("second", secondDataSource);
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(firstDataSource); // 设置默认数据源
        return routingDataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("dynamicDataSource") DataSource dataSource) {
        Map<String, String> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.ddl-auto", "update");
        jpaProperties.put("hibernate.show-sql", "true");
        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");

        return builder
                .dataSource(dataSource)
                .packages("com.example.demo.entity")
                .persistenceUnit("dynamicPU")
                .properties(jpaProperties)
                .build();
    }

    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

4. 业务中切换数据源 #

在方法执行前指定数据源标识，执行后清空：

@Service
public class DemoService {
    @Autowired
    private UserRepository userRepository;

    public List<User> getUsersFromSecondDb() {
        DataSourceContextHolder.setDataSourceKey("second");
        try {
            return userRepository.findAll();
        } finally {
            DataSourceContextHolder.clearDataSourceKey();
        }
    }
}

也可以自定义@DataSource注解配合AOP切面，进一步简化数据源切换逻辑。

内容的提问来源于stack exchange，提问作者Md Hasmat Noorani