Wie man Oracle-Funktion oder Prozedur unter Verwendung von Hibernate (EntityManager) oder JPA aufruft

Question

Ich habe eine Oracle-Funktion, die ein sys-refcursor zurückgibt, und wenn ich diese Funktion mit Hibernate aufrufe, erhalte ich die folgende Ausnahme.

Hibernate: { ? = call my_function(?) }
 org.hibernate.exception.GenericJDBCException: could not execute query
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1360)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1288)
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:313)

Wie kann ich das lösen?

Oracle Funktion

create or replace 
FUNCTION my_function(p_val IN varchar2)
    RETURN SYS_REFCURSOR
  AS
    my_cursor SYS_REFCURSOR;
  BEGIN
    OPEN my_cursor FOR SELECT emp_name FROM employees
    WHERE lower(emp_name) like lower(p_val||'%');
    RETURN my_cursor;    
  END;

Meine Entity-Klasse

@Entity
@javax.persistence.NamedNativeQuery(name = "getFunc", query = 
"{ ? = call my_function(:empName) }", 
 resultClass = Employee.class, hints = 
 { @javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })
 @Table(name = "EMPLOYEES")

und im DAO

    @Override
        public void findEmployees(QueryData data,
                String empName) {

        List query = (List) entityManager.createNamedQuery("getFunc")
                         .setParameter("empName", empName)
                         .getSingleResult();
                data.setResult(query);
}

Answer 1

Oracle Funktion oder eine gespeicherte Prozedur kann auf folgende Weise über EntityManager aufgerufen werden.

Für Oracle Funktion

Erstellen Sie eine Funktion mit sys_refcursor als Rückgabetyp

CREATE OR REPLACE FUNCTION my_function
(p_val IN varchar2)
    RETURN SYS_REFCURSOR
  AS
    my_cursor SYS_REFCURSOR;
  BEGIN
    OPEN my_cursor FOR SELECT emp_name FROM employees
    WHERE lower(emp_name) like lower(p_val||'%');
    RETURN my_cursor;    
  END;

In der Entity-Klasse definieren Sie die Funktion als

@javax.persistence.NamedNativeQuery(name = "getFunc", query = "{? =  call
my_function(:empName) }", resultClass = Employee.class, hints = {
@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })

Für Oracle gespeicherte Prozedur

Erstellen Sie eine Prozedur mit sys_refcursor als ersten OUT-Parameter

CREATE OR REPLACE PROCEDURE myProcedure(p_cursor out sys_refcursor,
     p_val  in varchar2
)
 AS
BEGIN
     OPEN o_cursor FOR
          SELECT     emp_name 
             FROM     employees 
            WHERE     LOWER (emp_name) LIKE lower(p_val||'%');

In der Entity-Klasse definieren Sie die Prozedur als

@javax.persistence.NamedNativeQuery(name = "getProc", query = "{ call
my_procedure(?,:empName) }", resultClass = Employee.class, hints = {
@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })

und schließlich in der DAO-Klasse die Funktion oder Prozedur aufrufen als

Query query = entityManager.createNamedQuery("getFunc"); // wenn Prozedur dann getProc 
query.setParameter("empName","smith"); 
query.getResultList(); 

Danke

Answer 2

Für Ihre Funktion,

create or replace 
FUNCTION my_function(p_val IN varchar2)
    RETURN SYS_REFCURSOR
  AS
    my_cursor SYS_REFCURSOR;
  BEGIN
    OPEN my_cursor FOR SELECT emp_name FROM employees
    WHERE lower(emp_name) like lower(p_val||'%');
    RETURN my_cursor;    
  END;

Sie können die folgende NamedNativeQuery definieren:

@NamedNativeQuery(
    name = "my_function",
    query = "{ ? = call my_function( ? ) }",
    callable = true,
    resultClass = String.class
)

Und Sie können die Abfrage wie folgt aufrufen:

List employeeNames = entityManager
    .createNamedQuery("my_function")
    .setParameter(1, 1L)
    .getResultList();

Für eine gespeicherte Prozedur:

CREATE OR REPLACE 
PROCEDURE my_procedure(p_val IN VARCHAR2, 
    my_cursor OUT SYS_REFCURSOR,
) 
AS
BEGIN
    OPEN my_cursor FOR
    SELECT emp_name FROM employees
    WHERE lower(emp_name) like lower(p_val||'%');
END;

, können Sie die folgende JPA 2.1 Abfrage verwenden:

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

query.execute();

List result = query.getResultList();

Answer 3

Für Verfahren:

CREATE OR REPLACE PROCEDURE my_procedure(p_val IN VARCHAR2, 
  my_cursor OUT SYS_REFCURSOR) 
AS
BEGIN
  OPEN my_cursor FOR SELECT emp_name FROM employees 
      WHERE lower(emp_name) like lower(p_val||'%');
END;

Alternative Lösung: Prozedur mit sys_refcursor als OUT-Parameter aufrufen ohne @NamedNativeQuery zu definieren

StoredProcedureQuery query = entityManager.createStoredProcedureQuery("myProcedure");
    query.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR);
    query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
    query.setParameter(2, "Umesh");
    List result = query.getResultList();

Answer 4

Sie scheinen Oracle-Funktionen mit Oracle-gespeicherten Prozeduren zu verwechseln.

Funktionen können aus einer SELECT-Anweisung aufgerufen werden - benutzerdefinierte Funktionen wie Ihre verhalten sich genauso wie die integrierten Funktionen, z. B. min() und max(). Sie können nicht durch einen externen "Aufruf" wie gespeicherte Prozeduren aufgerufen werden.

Siehe http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions231.htm#i1012049 für die Definition einer Funktion.

Sie müssen wahrscheinlich Ihre Funktion als gespeicherte Prozedur neu schreiben.

Answer 5

JPA 2.1 early draft besagt, dass es Unterstützung für gespeicherte Prozeduren geben wird, gemäß Arun Gupta von Oracle.

Unterstützung für gespeicherte Prozeduren: Hinzugefügte Unterstützung für den Aufruf von vordefinierten Datenbankfunktionen und benutzerdefinierten Datenbankfunktionen in der Java Persistence-Abfragesprache.

Es gibt verschiedene Varianten von EntityManager.createXXXStoredProcedureQuery Methoden, die eine StoredProcedureQuery zurückgeben, um eine gespeicherte Prozedur auszuführen. Ähnlich wie @NamedQuery gibt es @NamedStoredProcedureQuery, das eine gespeicherte Prozedur, deren Parameter und ihren Ergebnistyp spezifiziert und benennt. Diese Annotation kann auf einer Entität oder einer abgebildeten Oberklasse angegeben werden. Der im Annotation angegebene Name wird dann in EntityManager.createNamedStoredProcedureQuery verwendet. Die IN, OUT und INOUT-Parameter können festgelegt und verwendet werden, um Werte zurückzugeben, die von der Prozedur übergeben wurden. Zum Beispiel:

@Entity
@NamedStoredProcedureQuery(name="topGiftsStoredProcedure", procedureName="Top10Gifts")
public class Product {
 . . .
}

// In Ihrem Client

StoredProcedreQuery query = EntityManager.createNamedStoredProcedureQuery("topGiftsStoredProcedure");
query.registerStoredProcedureParameter(1, String.class, ParameterMode.INOUT);
query.setParameter(1, "top10");
query.registerStoredProcedureParameter(2, Integer.class, ParameterMode.IN);
query.setParameter(2, 100);
// es gibt andere setParameter Methoden zur Definition des zeitlichen Typs eines Parameters
. . .
query.execute();
String response = query.getOutputParameterValue(1);

Was die Fertigstellung des Specs betrifft oder wann Hibernate JPA 2.1 unterstützen wird, kann ich nicht sagen. Es könnte jedoch sinnvoll sein, weiterhin auf dem Laufenden zu bleiben.