使用游标的存储过程调用将抛出无效的列名异常 [英] The stored procedure call with cursors throws invalid column name exception

问题描述

我们有一个Spring Boot应用程序，需要连接到Oracle DB并通过存储过程获取数据.我们的每个存储过程都有REF_CURSOR作为OUT参数.我正在尝试使用@NamedStoredProcedureQuery和@Entity注释.我们在pom.xml文件中使用ojdbc14.jar，在application.properties文件中使用Oracle12cDialect.执行我的代码时，出现异常 Invalid Column Name .同样，在实体类中，我必须引入一个带有注释@Id的字段，尽管存储过程的REF_CURSOR没有返回这样的字段.这可能是个问题吗?也不要定义@Id字段，因为Hibernate会抛出一个异常.任何提示将不胜感激.

We have a Spring Boot application where we need to connect to Oracle DB and fetch data via stored procedures. Each of our stored procedure has REF_CURSOR as OUT parameters. I am trying the same using @NamedStoredProcedureQuery and @Entity annotations. We are using ojdbc14.jar in pom.xml and Oracle12cDialect in application.properties file. I get the exception Invalid Column Name while executing my piece of code. Also in the entity class I had to introduce a field with annotation @Id, although there is no such field being returned by the REF_CURSOR of my stored procedure. Can this be a problem? Also not defining @Id field is not an option since Hibernate throws an exception then. Any hints would be highly appreciated.

实施和问题与问题非常相似 调用时无效的列名异常通过JPA 2.1使用ref_cursor构建Oracle存储过程

Implementation and Problem is very similar to the question Invalid column name exception when calling an Oracle stored procedure with ref_cursor through JPA 2.1

但是这里没有答案

推荐答案

如何实现此简单示例:

1. 数据库架构.

create table MY_PATIENT
(
   PAT_RECID  number,
   PAT_NAME varchar2(100),

   constraint PAT_PK primary key(PAT_RECID)
);

create table MY_ORDER
(
   ORD_RECID  number,
   ORD_CODE varchar2(15),
   ORD_PATID number,

   constraint ORD_PK primary key(ORD_RECID),
   constraint ORD_PAT_FK foreign key(ORD_PATID) references MY_PATIENT(PAT_RECID),
   constraint ORD_CODE_UNIQUE unique (ORD_CODE)
);

CREATE OR REPLACE PROCEDURE fetch_patient_orders(
  patientId IN NUMBER, 
  patientOrders OUT SYS_REFCURSOR)
AS
BEGIN
   OPEN patientOrders FOR
   SELECT *
   FROM MY_ORDER
   WHERE ORD_PATID = patientId;
END;

1. 实体定义.

@NamedStoredProcedureQueries(
   @NamedStoredProcedureQuery(
      name = "fetch_patient_orders",
      procedureName = "fetch_patient_orders",
      resultClasses = Order.class, 
      parameters = {
         @StoredProcedureParameter(
            name = "patientId",
            type = Long.class,
            mode = ParameterMode.IN
         ),
         @StoredProcedureParameter(
            name = "patientOrders",
            type = Class.class,
            mode = ParameterMode.REF_CURSOR
         )
      }
   )
)
@Entity
@Table(name = "MY_ORDER")
public class Order
{
   @Id
   @Column(name = "ORD_RECID")
   private Long id;

   @Column(name = "ORD_CODE")
   private String code;

   @ManyToOne
   @JoinColumn(name = "ORD_PATID")
   private Patient patient;
}

1. 和用法:

List<Order> orders = session.createNamedStoredProcedureQuery("fetch_patient_orders")
    .setParameter("patientId", 2L)
    .getResultList();

它已使用休眠版本5.4.12.Final，ojdbc8.jar，Oracle12cDialect进行了测试. 另请参见hibernate 文档.

It was tested with hibernate 5.4.12.Final, ojdbc8.jar, Oracle12cDialect. See also the hibernate documentation.

上述方法将在纯休眠应用程序中运行，但在Spring Boot应用程序中无效.

The described above approach will work in a pure hibernate application, but not in spring boot app.

根据spring boot 文档:

According to the spring boot documentation:

与生产数据库的连接

生产数据库连接也可以通过使用池DataSource进行自动配置. Spring Boot使用以下算法来选择特定的实现:

Production database connections can also be auto-configured by using a pooling DataSource. Spring Boot uses the following algorithm for choosing a specific implementation:

1. 由于其性能和并发性，我们更喜欢 HikariCP .如果HikariCP可用，我们总是选择它.

1. We prefer HikariCP for its performance and concurrency. If HikariCP is available, we always choose it.

否则，如果Tomcat池化数据源可用，我们将使用它.

Otherwise, if the Tomcat pooling DataSource is available, we use it.

如果HikariCP和Tomcat池数据源均不可用，并且 Common DBCP2 可用，我们使用它.

If neither HikariCP nor the Tomcat pooling datasource are available and if Commons DBCP2 is available, we use it.

如果使用spring-boot-starter-jdbc或spring-boot-starter-data-jpa启动器"，则会自动获得对HikariCP的依赖.

If you use the spring-boot-starter-jdbc or spring-boot-starter-data-jpa "starters", you automatically get a dependency to HikariCP.

您可以完全绕过该算法，并通过设置spring.datasource.type属性来指定要使用的连接池.

You can bypass that algorithm completely and specify the connection pool to use by setting the spring.datasource.type property.

因此，默认情况下，spring boot使用HikariCP JDBC连接池.看来REF_CURSOR参数注册有问题:

So, spring boot uses HikariCP JDBC connection pool by default. And it looks like it has a problem with REF_CURSOR parameter registering:

o.h.r.j.i.ResourceRegistryStandardImpl   : Registering statement [HikariProxyCallableStatement@770201936 wrapping oracle.jdbc.driver.OracleCallableStatementWrapper@528a6369]
o.h.type.descriptor.sql.BasicBinder      : binding parameter [patientId] as [BIGINT] - [2]
o.h.s.i.AbstractServiceRegistryImpl      : Initializing service [role=org.hibernate.engine.jdbc.cursor.spi.RefCursorSupport]
o.h.engine.jdbc.spi.SqlExceptionHelper   : Error registering REF_CURSOR parameter [patientOrders] [n/a]

当我在application.properties中使用oracle特定数据源池时:

When I use the oracle specific data source pool in the application.properties:

# com.zaxxer.hikari.HikariDataSource (default value)
spring.datasource.type=oracle.jdbc.pool.OracleDataSource

一切正常.

这篇关于使用游标的存储过程调用将抛出无效的列名异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！