SpringBoot多数据库配置：JPA PostgreSQL+5个Oracle JDBC启动报错求助

场景说明 #

• 维护SpringBoot后端项目，核心需求：
  • 1个PostgreSQL数据库：通过JPA/ORM实现历史记录存储，需创建Repository和Entity
  • 5个Oracle数据库：仅拥有视图只读权限，无需ORM映射，直接用JDBC获取零散数据

当前尝试的配置与代码 #

application.properties #

# 历史记录数据库配置
spring.datasource.url = jdbc:postgresql://localhost:5432/NA_db
spring.datasource.username = userNA
spring.datasource.password = userNApwd
spring.datasource.driver-class-name=org.postgresql.Driver
spring.session.jdbc.initialize-schema: always
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

DataSourceConfiguration.java #

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

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.domain.EntityScan;
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.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 com.zaxxer.hikari.HikariDataSource;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "historyEntityManagerFactory", transactionManagerRef = "historyTransactionManager", basePackages = "mypackage.history.dao")
@EntityScan("mypackage.history.model.entities")
public class DataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties("spring.datasource")
    public DataSourceProperties SourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean(name = "historyDatasource")
    @ConfigurationProperties(prefix = "spring.datasource.configuration")
    public DataSource dataSource() {
        return SourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();

    }

    @Primary
    @Bean(name = "historyEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean barEntityManagerFactory(EntityManagerFactoryBuilder builder,
            @Qualifier("historyDatasource") DataSource dataSource) {
        return builder.dataSource(dataSource).packages("mypackage.history.model.entities")
                .persistenceUnit("historyPU").build();
    }

    @Primary
    @Bean(name = "historyTransactionManager")
    public PlatformTransactionManager barTransactionManager(
            @Qualifier("historyEntityManagerFactory") EntityManagerFactory barEntityManagerFactory) {
        return new JpaTransactionManager(barEntityManagerFactory);
    }
}

Oracle数据库JDBC连接代码 #

Class.forName("oracle.jdbc.OracleDriver");
connectionYG = DriverManager.getConnection(urlYG, user, password);
stmtYG = connectionYG.createStatement();
connectionZA = DriverManager.getConnection(urlZA, user, password);
stmtZA = connectionZA.createStatement();
connectionZB = DriverManager.getConnection(urlZB, user, password);
stmtZB = connectionZB.createStatement();
connectionZC = DriverManager.getConnection(urlZC, user, password);
stmtZC = connectionZC.createStatement();
connectionZD = DriverManager.getConnection(urlZD, user, password);
stmtZD = connectionZD.createStatement();

启动错误信息 #

***************************
APPLICATION FAILED TO START
***************************

Description:

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.

Reason: Failed to determine a suitable driver class


Action:

Consider the following:
    If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
    If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).

解决方案 #

问题根源 #

自定义DataSourceConfig后，SpringBoot自动配置的数据源逻辑被干扰；同时手动用DriverManager创建Oracle连接未纳入Spring管理，导致启动时Spring无法识别主数据源的配置有效性。

修正步骤 #

1. 修正PostgreSQL主数据源配置

   • 规范DataSourceProperties的bean命名，避免与Spring默认bean冲突；同时去掉多余的配置前缀，确保完整绑定spring.datasource属性：

     @Primary
     @Bean(name = "historyDataSourceProperties")
     @ConfigurationProperties("spring.datasource")
     public DataSourceProperties historyDataSourceProperties() {
         return new DataSourceProperties();
     }
     
     @Primary
     @Bean(name = "historyDatasource")
     public DataSource historyDataSource(@Qualifier("historyDataSourceProperties") DataSourceProperties properties) {
         return properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
     }
     

2. 禁用SpringBoot默认数据源自动配置

   • 在SpringBoot启动类添加注解，避免自动创建默认数据源：

     @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class})
     

3. 规范Oracle数据库连接管理

   • 放弃手动DriverManager创建连接，改用Spring管理的JdbcTemplate：
     • 在application.properties添加Oracle配置：

       # Oracle YG
       oracle.yg.url=jdbc:oracle:thin:@//xxx:1521/xxx
       oracle.yg.username=xxx
       oracle.yg.password=xxx
       oracle.yg.driver-class-name=oracle.jdbc.OracleDriver
       
       # 其他Oracle实例配置（za/zb/zc/zd）同理
       

     • 创建Oracle数据源配置类：

       @Configuration
       public class OracleDataSourceConfig {
       
           @Bean(name = "ygDataSource")
           @ConfigurationProperties(prefix = "oracle.yg")
           public DataSource ygDataSource() {
               return DataSourceBuilder.create().build();
           }
       
           @Bean(name = "ygJdbcTemplate")
           public JdbcTemplate ygJdbcTemplate(@Qualifier("ygDataSource") DataSource dataSource) {
               return new JdbcTemplate(dataSource);
           }
       
           // 重复上述代码，创建zaJdbcTemplate、zbJdbcTemplate等
       }
       

     • 服务类中注入JdbcTemplate查询数据：

       @Service
       public class OracleDataService {
           private final JdbcTemplate ygJdbcTemplate;
       
           public OracleDataService(@Qualifier("ygJdbcTemplate") JdbcTemplate ygJdbcTemplate) {
               this.ygJdbcTemplate = ygJdbcTemplate;
           }
       
           public List<Map<String, Object>> queryYGData(String sql) {
               return ygJdbcTemplate.queryForList(sql);
           }
       }
       

4. 依赖检查

   • 确保pom.xml包含PostgreSQL和Oracle驱动：

     <!-- PostgreSQL驱动 -->
     <dependency>
         <groupId>org.postgresql</groupId>
         <artifactId>postgresql</artifactId>
         <scope>runtime</scope>
     </dependency>
     
     <!-- Oracle驱动 -->
     <dependency>
         <groupId>com.oracle.database.jdbc</groupId>
         <artifactId>ojdbc8</artifactId>
         <scope>runtime</scope>
     </dependency>
     

验证启动 #

完成以上配置后，SpringBoot会优先加载自定义的PostgreSQL主数据源，同时将5个Oracle数据源以JdbcTemplate形式纳入管理，启动时不会再出现数据源配置错误。

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