Spring Boot多数据源配置异常：无法切换至目标数据源

1. JPA默认绑定主数据源：仅配置两个DataSource但未为次数据源配套JPA核心组件（EntityManagerFactory、TransactionManager），Spring Data JPA会默认使用主数据源执行持久化操作，导致插入请求走MySQL库，触发权限错误。
2. EntityManagerFactory缺失：多数据源场景下未明确配置默认的entityManagerFactory Bean，系统启动时无法找到对应组件报错。
3. 数据源属性绑定冲突：主数据源配置中@ConfigurationProperties(prefix = "spring.datasource.tomcat")与DataSourceProperties的配置重复，可能导致Tomcat连接池属性加载异常。

1. 重构多数据源配置类 #

重新配置数据源及对应JPA组件，确保每个数据源独立绑定一套持久化逻辑：

package com.stcm.report.config;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class MultiDataSourceConfig {

    // 主数据源（MySQL）配置
    @Primary
    @Bean(name = "primaryDataSourceProps")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSourceProperties primaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.tomcat")
    public DataSource primaryDataSource(@Qualifier("primaryDataSourceProps") DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder()
                .type(DataSource.class)
                .build();
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("primaryDataSource") DataSource dataSource) {
        Map<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect");
        jpaProperties.put("hibernate.show_sql", "true");

        return builder
                .dataSource(dataSource)
                .packages("com.stcm.report.entity.mysql") // 主数据源实体类所在包
                .persistenceUnit("primaryPersistenceUnit")
                .properties(jpaProperties)
                .build();
    }

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

    // 次数据源（SQL Server）配置
    @Bean(name = "secondaryDataSourceProps")
    @ConfigurationProperties(prefix = "myotherdatasource.datasource")
    public DataSourceProperties secondaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "myotherdatasource.datasource.tomcat")
    public DataSource secondaryDataSource(@Qualifier("secondaryDataSourceProps") DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder()
                .type(DataSource.class)
                .build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        Map<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.SQLServerDialect");
        jpaProperties.put("hibernate.show_sql", "true");

        return builder
                .dataSource(dataSource)
                .packages("com.stcm.report.entity.sqlserver") // 次数据源实体类所在包
                .persistenceUnit("secondaryPersistenceUnit")
                .properties(jpaProperties)
                .build();
    }

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

2. 为不同数据源配置Repository扫描 #

拆分Repository包路径，通过注解绑定对应数据源的JPA组件：

主数据源Repository配置 #

package com.stcm.report.config;

import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.stereotype.Component;

@EnableJpaRepositories(
        basePackages = "com.stcm.report.repository.mysql", // 主数据源Repository所在包
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager"
)
@Component
public class PrimaryJpaConfig {
}

次数据源Repository配置 #

package com.stcm.report.config;

import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.stereotype.Component;

@EnableJpaRepositories(
        basePackages = "com.stcm.report.repository.sqlserver", // 次数据源Repository所在包
        entityManagerFactoryRef = "secondaryEntityManagerFactory",
        transactionManagerRef = "secondaryTransactionManager"
)
@Component
public class SecondaryJpaConfig {
}

3. 业务层指定目标数据源事务 #

在操作SQL Server的业务方法上，明确指定对应事务管理器：

@Service
public class ReportService {

    @Autowired
    private ReportRepository reportRepository; // 次数据源的Repository

    @Transactional(value = "secondaryTransactionManager")
    public void insertReport(Report report) {
        reportRepository.save(report);
    }
}

4. 简化application.yml配置（可选） #

移除全局JPA配置，改用代码中指定的数据源专属JPA属性：

server:
  port: 9091

spring:
  application:
    name: customer-wallet-service

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://xxx.xxx.xx.xxx:3306/mdi
    username: mdi_mng
    password: yyyyyyy
    tomcat:
      validation-query: select 1
      test-on-borrow: true
          
myotherdatasource:        
  datasource:
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    url: jdbc:sqlserver://yyy.yy.y.y:1433;databaseName=report
    username: usrValores
    password: zzzzzzzzzz
    tomcat:
      validation-query: select 1
      test-on-borrow: true

• 每个数据源必须配套独立的EntityManagerFactory和TransactionManager，Spring Data JPA才能识别并路由请求。
• 实体类、Repository需按数据源拆分到不同包，避免组件混淆。
• 业务方法通过@Transactional指定事务管理器，确保操作精准绑定目标数据源。

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