Java 如何使用 Hibernate 调用 Oracle 存储过程？

How to Call Oracle Stored Procedure using Hibernate?

提问by ssp

Below is my Oracle stored procedure,

下面是我的 Oracle 存储过程，

create or replace 
PROCEDURE "REPORT_HIBERNATE"(
start_year IN NUMBER,
output_data OUT SYS_REFCURSOR
)
AS 
BEGIN
DECLARE 
select_query LONG;

BEGIN
select_query :='SELECT 
  GLOBAL_ID,
  PROJECT_NUMBER,
  FISCAL_YEAR,
  FISCAL_MONTH,
  WEEK_END_DATE,
  ACTIVITY,
  COST_CENTER,
  ACTUAL_HOURS,
  APPROVED_HOURS,
  NORMALIZED_HOURS
FROM TS_TBTIMECARD_WEEKLY_DATA
where FISCAL_YEAR in
(SELECT  FISCAL_YEAR        
FROM TS_TBTIMECARD_WEEKLY_DATA        
where      FISCAL_YEAR               = ' ||start_year|| '
 )';
OPEN output_data FOR select_query;
END;
END REPORT_HIBERNATE;

Below is the Entity class:

下面是实体类：

@Entity
@SequenceGenerator(name="wkseq", initialValue=1, allocationSize=1)
@Table(name = "TS_TBTIMECARD_WEEKLY_DATA")



@NamedNativeQuery(name = "call_REPORT_HIBERNATE_procedure",
query = "{ CALL REPORT_HIBERNATE(:start_year) }", 
resultClass = TimecardWeeklyData.class, hints = {
@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })



public class TimecardWeeklyData {
...
}

DAOImpl:

DAOImpl:

Query query = sessionFactory.getCurrentSession().getNamedQuery("call_REPORT_HIBERNATE_procedure");

        query.setParameter("start_year",2014); 
    List result = query.list();

I get the following exception: Hibernate: { CALL REPORT_HIBERNATE(?) } org.hibernate.exception.GenericJDBCException: could not execute query... and Caused by: java.sql.SQLException: Invalid column index

我收到以下异常：Hibernate: { CALL REPORT_HIBERNATE(?) } org.hibernate.exception.GenericJDBCException: could not execute query... and Caused by: java.sql.SQLException: Invalid column index

Plz let me know how will I call this stored procedure using Hibernate Annotation in Spring??

请让我知道如何在 Spring 中使用 Hibernate Annotation 调用这个存储过程？

回答by Christopher Parker

Your OUTparameter isn't first. If you are able to modify your procedure, rearrange the parameters to make the OUTparameter the first parameter, then account for it in your @NamedNativeQueryannotation.

你的OUT参数不是第一个。如果您能够修改您的过程，请重新排列参数以使该OUT参数成为第一个参数，然后在您的@NamedNativeQuery注释中对其进行说明。

query = "{ CALL REPORT_HIBERNATE(?, :start_year) }",

回答by Vlad Mihalcea

Considering you have a SYS_REFCURSOROUT parameter:

考虑到您有一个SYS_REFCURSOROUT 参数：

CREATE OR REPLACE PROCEDURE post_comments ( 
   postId IN NUMBER, 
   postComments OUT SYS_REFCURSOR ) 
AS 
BEGIN
    OPEN postComments FOR
    SELECT *
    FROM post_comment 
    WHERE post_id = postId; 
END;

You can call the stored procedure like this:

您可以像这样调用存储过程：

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("post_comments")
    .registerStoredProcedureParameter(1, Long.class, 
         ParameterMode.IN)
    .registerStoredProcedureParameter(2, Class.class, 
         ParameterMode.REF_CURSOR)
    .setParameter(1, 1L);

query.execute();

List<Object[]> postComments = query.getResultList();

For more details, check out this articlewhich explains how you should call stored procedures with simple types and database functions as well.

有关更多详细信息，请查看这篇文章，该文章解释了如何使用简单类型和数据库函数调用存储过程。