Spring Boot 2配置多Oracle数据源仅@Primary标注的数据源可用问题

你只完成了双数据源的DataSource实例配置，没有为第二个数据源配置对应的JPA上下文（EntityManagerFactory、事务管理器）以及Repository分包绑定，导致第二个数据源的Repository实际使用了@Primary标注的主数据源连接，跨库访问表自然触发ORA-00942错误。

1. 修正第二数据源配置格式 #

你的第二数据源用的是Hikari连接池，但配置里写了DBCP的max-total属性，且前缀不匹配，修改application.properties对应配置：

# 把原来的spring.sgc-datasource.max-total=30替换为下面的配置
spring.sgc-datasource.configuration.maximum-pool-size=30

2. 新增主数据源JPA配置类 #

新建PrimaryJpaConfig.java，绑定主数据源对应的实体、Repository包路径：

package com.indra.vmo.edenorte.config;

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.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.indra.vmo.edenorte.repository.inmp", // 主数据源Repository包路径
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager"
)
public class PrimaryJpaConfig {

    private final DataSource inMpDataSource;

    public PrimaryJpaConfig(DataSource inMpDataSource) {
        this.inMpDataSource = inMpDataSource;
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        Map<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");
        jpaProperties.put("hibernate.hbm2ddl.auto", "none");

        return builder
                .dataSource(inMpDataSource)
                .packages("com.indra.vmo.edenorte.entity.inmp") // 主数据源实体类包路径
                .persistenceUnit("primaryPersistenceUnit")
                .properties(jpaProperties)
                .build();
    }

    @Bean
    @Primary
    public PlatformTransactionManager primaryTransactionManager(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(primaryEntityManagerFactory(builder).getObject());
    }
}

3. 新增第二数据源JPA配置类 #

新建SgcJpaConfig.java，绑定第二数据源对应的实体、Repository包路径：

package com.indra.vmo.edenorte.config;

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.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.indra.vmo.edenorte.repository.sgc", // 第二数据源Repository包路径
        entityManagerFactoryRef = "sgcEntityManagerFactory",
        transactionManagerRef = "sgcTransactionManager"
)
public class SgcJpaConfig {

    private final DataSource sgcDataSource;

    public SgcJpaConfig(DataSource sgcDataSource) {
        this.sgcDataSource = sgcDataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean sgcEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        Map<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");
        jpaProperties.put("hibernate.hbm2ddl.auto", "none");
        // 如果第二数据源的表所属schema和连接用户名不一致，可添加下面配置指定schema
        // jpaProperties.put("hibernate.default_schema", "你的SGC库表所属schema名");

        return builder
                .dataSource(sgcDataSource)
                .packages("com.indra.vmo.edenorte.entity.sgc") // 第二数据源实体类包路径
                .persistenceUnit("sgcPersistenceUnit")
                .properties(jpaProperties)
                .build();
    }

    @Bean
    public PlatformTransactionManager sgcTransactionManager(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(sgcEntityManagerFactory(builder).getObject());
    }
}

4. 清理全局JPA配置 #

可以删除application.properties里的全局JPA配置，避免冲突：

# 可删除以下全局配置
# spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
# spring.jpa.database=default
# spring.jpa.hibernate.ddl-auto=none

如果修改后仍报错，可先通过以下方式排查：

• 确认第二数据源配置的用户名是否有对应表的访问权限，直接用该账号通过客户端连接Oracle，执行SQL查询select * from 你的表名验证可正常访问
• 检查Clientes实体类的@Table注解配置的表名、schema是否和实际库中一致

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