如何使用Hibernate调用Oracle存储过程? [英] How to Call Oracle Stored Procedure using Hibernate?

问题描述

下面是我的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;

下面是Entity类:

@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:

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

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

我得到以下异常:休眠状态:{CALL REPORT_HIBERNATE(?)} org.hibernate.exception.GenericJDBCException:无法执行查询 ...和原因:java.sql.SQLException:无效的列索引

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

请让我知道如何在Spring中使用Hibernate Annotation调用此存储过程?

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

推荐答案

考虑到您有一个

Considering you have a SYS_REFCURSOR OUT parameter:

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();

这篇关于如何使用Hibernate调用Oracle存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！