JPA和SYS_REFCURSOR之类的OUT参数

问题描述

我想使用带有SYS_REFCURSOR的JPA(如OUT参数)来调用过程.使用普通的JDBC，这非常容易，但是我不确定在JPA中是否可能.

我的过程如下:

CREATE OR REPLACE FUNCTION FN_GET_COINS
  RETURN SYS_REFCURSOR
IS vCursor SYS_REFCURSOR;

BEGIN
  OPEN vCursor FOR
    SELECT
      ...
  RETURN vCursor;
  CLOSE vCursor;

EXCEPTION
  ...
END FN_GET_COINS;

解决方案

JPA 2.0 不支持存储过程，但是在 JPA 2.1 中添加了支持 Java EE 7 .使用Oracle SYS_REF_CURSOR的标准JPA 2.1代码示例:

http://wiki.eclipse.org/EclipseLink/Release/2.5 /JPA21#Ref_cursor_Example
http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#JPA_2. 1_StoredProcedureQuery

---

• EclipseLink 2.5支持JPA 2.1(这是参考实现):

  http://www.eclipse.org/eclipselink/releases/2.5.php
  https://glassfish.java.net/

• Hibernate 4.3.11支持JPA 2.1:

  http://www.hibernate.org/downloads

• 休眠达4.2的版本通过本机API和存储支持存储过程. config(非JPA):

  http://docs.jboss .org/hibernate/orm/3.3/reference/zh-CN/html/querysql.html#sp_query

  (此处CURSOR必须通过存储函数"返回，或者必须是存储过程的第一个参数).

  此外，Polpan的答案此处展示如何使用带有QueryHint的JPA 2.0本机查询将Hibernate专有提示属性org.hibernate.callable设置为true.

I want to call a procedure using JPA with SYS_REFCURSOR like OUT parameter. This is very easy using plain JDBC but I'm not sure that is possible in JPA.

My procedure is like following:

CREATE OR REPLACE FUNCTION FN_GET_COINS
  RETURN SYS_REFCURSOR
IS vCursor SYS_REFCURSOR;

BEGIN
  OPEN vCursor FOR
    SELECT
      ...
  RETURN vCursor;
  CLOSE vCursor;

EXCEPTION
  ...
END FN_GET_COINS;

解决方案

JPA 2.0 has no support for stored procedures, but support has been added in JPA 2.1, part of Java EE 7. Examples of standard JPA 2.1 code using Oracle SYS_REF_CURSOR:

http://wiki.eclipse.org/EclipseLink/Release/2.5/JPA21#Ref_cursor_Example
http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#JPA_2.1_StoredProcedureQuery

---

• EclipseLink 2.5 supports JPA 2.1 (it's the reference implementation):

  http://www.eclipse.org/eclipselink/releases/2.5.php
  https://glassfish.java.net/

• Hibernate 4.3.11 supports JPA 2.1:

  http://www.hibernate.org/downloads

• Hibernate up to 4.2 supports stored procedures via native API & config (non-JPA) :

  http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#sp_query

  (Here the CURSOR must be either returned via a 'stored function', or must be the first parameter (out) of a stored procedure).

  Additionally, Polpan's answer here show how this can be done with a JPA 2.0 native query with a QueryHint, setting Hibernate proprietary hint property org.hibernate.callable to true.

这篇关于JPA和SYS_REFCURSOR之类的OUT参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！