Spring Boot连接双Oracle数据库登录报ORA-00904问题求助

已完成的配置与代码 #

1. application.properties 配置 #

#############################################
## Database Configuration
#############################################
# HikariCP settings
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.poolName=HikariPoolOrcl
# JPA settings
spring.jpa.database=default
spring.datasource.dialect=org.hibernate.dialect.OracleDialect
spring.jpa.hibernate.use-new-id-generator-mappings=false
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.datasource.driver.class=oracle.jdbc.driver.OracleDriver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.OracleDialect
spring.jpa.database-platform=org.hibernate.dialect.OracleDialect
############################################
# OracleDB connection settings
###########################################
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
spring.datasource.username=userdev
spring.datasource.password=pass123
spring.datasource.pool-size=30
############################################
# OracleDB connection settings FOR IL DB
###########################################
spring.il.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.il.datasource.jdbc-url=jdbc:oracle:thin:@//192.126.98.77:1521/apimdbuat
spring.il.datasource.username=userdev
spring.il.datasource.password=Ahjhj20
spring.il.datasource.pool-size=30 

2. 主库 User 实体类 #

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity(name = "User")
@Table(name = "users")
public class User {
    @Id
    @SequenceGenerator(name = "user_sequence", sequenceName = "user_sequence", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_sequence")
    @Column(nullable = false, updatable = false)
    private Long id;

    @Column(nullable = false, length = 64)
    @NotBlank(message = "Firstname is required")
    private String firstname;
    
    @NotBlank(message = "Lastname is required")
    @Column(nullable = false, length = 64)
    private String lastname;

    @NotBlank(message = "Username is required")
    @Column(nullable = false, length = 64, unique = true)
    private String username;

    @Column(nullable = false, length = 64, unique = true)
    @Email
    @NotEmpty(message = "Email is required")
    private String email;

    @NotBlank(message = "Password is required")
    @Column(nullable = false, length = 64)
    @JsonIgnore
    private String password;

    private String profileImgUrl;
    private Date lastLoginDate;
    private Date joinDate;
    
    @JsonProperty("isActive")
    private boolean isActive;
    @JsonProperty("isNotLocked")
    private boolean isNotLocked;

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Set<Role> roles = new HashSet<>();
}

3. 从库 AppConfig 实体类 #

@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "app_config")
public class AppConfig {
    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    private String appCode;
    private String appName;
    private String version;
}

4. Repository 接口 #

public interface UserRepository extends JpaRepository<User, Long> {
    boolean existsByEmail(String email);
    boolean existsByUsername(String username);
} 

public interface AppConfigRepository extends JpaRepository<AppConfig, Long> {
}

5. 数据源配置类 #

主库配置

@Configuration(proxyBeanMethods = false)
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager",
        basePackages = {"com.app.models", "com.app.repositories"}
)
public class PrimaryDatabaseConfig {
    @Bean(name = "primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public HikariDataSource dataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                              @Qualifier("primaryDataSource") DataSource primaryDataSource) {
        return builder
                .dataSource(primaryDataSource)
                .packages("com.app.repositories", "com.app.models")
                .build();
    }

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

从库配置

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "secondaryEntityManagerFactory",
        transactionManagerRef = "secondaryTransactionManager",
        basePackages = {"com.app.il_models", "com.app.il_repositories"}
)
public class SecondaryDatabaseConfig {
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.il.datasource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                                @Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
        return builder
                .dataSource(secondaryDataSource)
                .packages("com.app.il_models", "com.app.il_repositories")
                .build();
    }

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

遇到的问题 #

修复JDBC URL异常后应用可正常启动，但调用登录API（校验用户名/密码）时抛出错误：

SQL Error: 904, SQLState: 42000
ORA-00904: "USER0_"."PROFILEIMGURL": invalid identifier
Unauthorized error: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

{
    "code": "401",
    "message": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet"
}

备注：单数据源模式下一切正常。

内容的提问来源于stack exchange，提问作者Mohamde amine Karek