基于Spring Profile配置不同Oracle存储过程调用的JPA实现疑问

Approach 1: Dynamic Stored Procedure Call (Recommended) #

Instead of relying on static @NamedStoredProcedureQuery, build the procedure name dynamically using Spring's Environment to pull the schema name from environment-specific properties. This keeps your entity class clean and avoids duplication.

1. Add environment-specific properties:

   • In application-dev.properties:

     oracle.db.schema=DB_DEV
     

   • In application-prod.properties:

     oracle.db.schema=DB_PRD
     

2. Inject Environment and create dynamic procedure call:
   In your repository or service class, use EntityManager to construct the stored procedure query with the environment-specific schema:

   @Service
   public class MyEntityService {
       @Autowired
       private EntityManager entityManager;
       
       @Autowired
       private Environment environment;
       
       public List<MyEntity> executeStoredProcedure(String id) {
           String schema = environment.getProperty("oracle.db.schema");
           String fullProcedureName = schema + ".PACKAGE.MYSTOREDPROC";
           
           StoredProcedureQuery query = entityManager.createStoredProcedureQuery(fullProcedureName)
               .registerStoredProcedureParameter("records", void.class, ParameterMode.REF_CURSOR)
               .registerStoredProcedureParameter("id", String.class, ParameterMode.IN)
               .setParameter("id", id);
           
           query.execute();
           // Map REF_CURSOR results to MyEntity (use @SqlResultSetMapping if needed)
           return query.getResultList().stream()
               .map(result -> {
                   // Convert Object[] or Tuple to MyEntity instance
                   MyEntity entity = new MyEntity();
                   // Set fields based on result columns
                   return entity;
               })
               .collect(Collectors.toList());
       }
   }
   

Approach 2: Property Substitution in @NamedStoredProcedureQuery #

If you prefer to keep using @NamedStoredProcedureQuery, enable property resolution in Hibernate annotations by configuring the EntityManagerFactory to use Spring's property sources.

1. Update the entity annotation:

   @Entity
   @Table(name = "MYTABLE")
   @NamedStoredProcedureQueries({
       @NamedStoredProcedureQuery(
           name = "MYSTOREDPROC",
           procedureName = "${oracle.db.schema}.PACKAGE.MYSTOREDPROC",
           parameters = {
               @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name = "records", type = void.class),
               @StoredProcedureParameter(mode = ParameterMode.IN, name = "id", type = String.class)
           }
       )
   })
   public class MyEntity {...}
   

2. Configure EntityManagerFactory for placeholder resolution:
   Add this Spring configuration class to pass environment properties to Hibernate:

   @Configuration
   public class JpaConfig {
       @Autowired
       private Environment environment;
       
       @Bean
       public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
           LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
           em.setDataSource(dataSource);
           em.setPackagesToScan("com.yourpackage.entity");
           
           HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
           em.setJpaVendorAdapter(vendorAdapter);
           
           Properties jpaProps = new Properties();
           jpaProps.put("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");
           em.setJpaProperties(jpaProps);
           
           // Pass Spring environment properties to resolve ${...} placeholders
           em.setJpaPropertyMap(environment.getSystemProperties());
           
           return em;
       }
   }
   

Why PACKAGE.MYSTOREDPROC Doesn't Work #

Oracle requires fully qualified object names (schema.package.procedure) when the package is not owned by the application's database user.

When you omit the schema name, Oracle searches for the package in the default schema of the user your app is connecting with. If that user doesn't own the package or doesn't have it in their default schema, you'll get an error like ORA-06550: PLS-00201: identifier 'PACKAGE.MYSTOREDPROC' must be declared.

Alternative workarounds (less recommended for multi-environment setups):

• Create a synonym for DBNAME.PACKAGE.MYSTOREDPROC in the application user's schema.
• Set the application user's default schema to DBNAME (not ideal for security).

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