Jump to: navigation, search

Difference between revisions of "EclipseLink/Examples/JPA/PLSQLStoredFunction"

(Oracle stored function returning PLSQL record type)
 
(8 intermediate revisions by the same user not shown)
Line 6: Line 6:
 
OBJECT types are returned as java.sql.Struct and VARRAY as java.sql.Array types in JDBC.
 
OBJECT types are returned as java.sql.Struct and VARRAY as java.sql.Array types in JDBC.
  
To call a stored function using PLSQL types the PLSQLStoredFunctionCall or @NamedPLSQLStoredFunctionQuery must be used.  PLSQLStoredProcedureCall and @NamedPLSQLStoredProcedureQuery also exist for stored procedures.  For regular stored functions and procedure that do not return complex PLSQL types the regular StoredFunctionCall, StoredProcedureCall and @NamedStoredFunctionQuery, @NamedStoredProcedureQuery can be used.
+
To call a stored function using PLSQL types the [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredFunctionCall.html PLSQLStoredFunctionCall] or [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/annotations/NamedPLSQLStoredFunctionQuery.html @NamedPLSQLStoredFunctionQuery] must be used.  [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.html PLSQLStoredProcedureCall] and [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/annotations/NamedPLSQLStoredProcedureQuery.html @NamedPLSQLStoredProcedureQuery] also exist for stored procedures.  For regular stored functions and procedure that do not return complex PLSQL types the regular [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/queries/StoredFunctionCall.html StoredFunctionCall], [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/queries/StoredProcedureCall.html StoredProcedureCall] and [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/annotations/NamedStoredFunctionQuery.html @NamedStoredFunctionQuery], [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/annotations/NamedStoredProcedureQuery.html @NamedStoredProcedureQuery] can be used.
  
 
To call PLSQL stored functions or procedures mirror OBJECT/VARRAY types must be defined for the RECORD/TABLE types.
 
To call PLSQL stored functions or procedures mirror OBJECT/VARRAY types must be defined for the RECORD/TABLE types.
 +
OBJECT types can be mapped to @Entity or @Embeddable classes using the [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/annotations/Struct.html @Struct] annotation.  Normally @Embeddable is used, unless the OBJECT type defines an Id and can be stored in a table.
 +
Nested OBJECT and VARRAY types can be mapped using the [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/annotations/Structure.html @Structure] and [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/annotations/Array.html @Array] annotations.
  
 
=== Oracle stored function returning PLSQL record type ===
 
=== Oracle stored function returning PLSQL record type ===
Line 21: Line 23:
 
CREATE OR REPLACE PACKAGE BODY EMP_PKG AS
 
CREATE OR REPLACE PACKAGE BODY EMP_PKG AS
 
FUNCTION GET_EMP RETURN EMP_REC AS
 
FUNCTION GET_EMP RETURN EMP_REC AS
   P_EMP EMP_REC; "
+
   P_EMP EMP_REC;
 
   BEGIN P_EMP.F_NAME := 'Bob'; P_EMP.F_NAME := 'Smith'; P_EMP.SALARY := 30000;
 
   BEGIN P_EMP.F_NAME := 'Bob'; P_EMP.F_NAME := 'Smith'; P_EMP.SALARY := 30000;
 
   RETURN P_EMP;
 
   RETURN P_EMP;
Line 30: Line 32:
 
An OBJECT type mirror for the EMP_REC type must also be defined.
 
An OBJECT type mirror for the EMP_REC type must also be defined.
 
<source lang="sql">
 
<source lang="sql">
CREATE OR REPLACE TYPE EMP_REC AS OBJECT (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2))
+
CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2))
 +
</source>
 +
 
 +
=== Defining Java class to map to OBJECT type ===
 +
<source lang="java">
 +
@Embeddable
 +
@Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"})
 +
public class Employee {
 +
@Column(name="F_NAME")
 +
private String firstName;
 +
@Column(name="L_NAME")
 +
private String lastName;
 +
@Column(name="SALARY")
 +
private BigDecimal salary;
 +
...
 +
}
 
</source>
 
</source>
  
Line 40: Line 57:
  
 
DataReadQuery databaseQuery = new DataReadQuery();
 
DataReadQuery databaseQuery = new DataReadQuery();
PLSQLStoredFunctionCall call = new PLSQLStoredFunctionCall ();
+
databaseQuery.setResultType(DataReadQuery.VALUE);
call.setProcedureName("GET_EMP");
+
PLSQLrecord record = new PLSQLrecord();
call.
+
record.setTypeName("EMP_PKG.EMP_REC");
 +
record.setCompatibleType("EMP_TYPE");
 +
record.setJavaType(Employee.class);
 +
record.addField("F_NAME", JDBCTypes.VARCHAR_TYPE, 30);
 +
record.addField("L_NAME", JDBCTypes.VARCHAR_TYPE, 30);
 +
record.addField("SALARY", JDBCTypes.NUMERIC_TYPE, 10, 2);
 +
PLSQLStoredFunctionCall call = new PLSQLStoredFunctionCall(record);
 +
call.setProcedureName("EMP_PKG.GET_EMP");
 
databaseQuery.setCall(call);
 
databaseQuery.setCall(call);
  
Line 52: Line 76:
 
=== Using @NamedPLSQLStoredFunctionQuery to define a stored function ===
 
=== Using @NamedPLSQLStoredFunctionQuery to define a stored function ===
 
<source lang="java">
 
<source lang="java">
@NamedPLSQLStoredFunctionQuery(name="getEmployee", procedureName="GET_EMP",
+
@NamedPLSQLStoredFunctionQuery(name="getEmployee", functionName="EMP_PKG.GET_EMP",
     returnParameter=@PLSQLParameter(name="RESULT", databaseType="EMP_REC", javaType=Employee.class))
+
     returnParameter=@PLSQLParameter(name="RESULT", databaseType="EMP_PKG.EMP_REC"))
 
@Embeddable
 
@Embeddable
@Struct(fields={"F_NAME", "L_NAME", "SALARY"})
+
@Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"})
@PLSQLRecord(name="EMP_REC")
+
@PLSQLRecord(name="EMP_PKG.EMP_REC", compatibleType="EMP_TYPE", javaType=Employee.class,
 +
    fields={@PLSQLParameter(name="F_NAME"), @PLSQLParameter(name="L_NAME"), @PLSQLParameter(name="SALARY", databaseType="NUMERIC_TYPE")})
 
public class Employee {
 
public class Employee {
@Column(name="F_NAME")
 
private String firstName;
 
@Column(name="L_NAME")
 
private String lastName;
 
@Column(name="SALARY")
 
private BigDecimal salary;
 
 
  ...
 
  ...
 
}
 
}

Latest revision as of 14:36, 19 December 2011

EclipseLink has support for calling Oracle PLSQL stored functions that return complex PLSQL data-types such as RECORD types and TABLE types. PLSQL types are not supported by Oracle JDBC, so these types must be translated to Oracle OBJECT types and VARRAY types. OBJECT types are returned as java.sql.Struct and VARRAY as java.sql.Array types in JDBC.

To call a stored function using PLSQL types the PLSQLStoredFunctionCall or @NamedPLSQLStoredFunctionQuery must be used. PLSQLStoredProcedureCall and @NamedPLSQLStoredProcedureQuery also exist for stored procedures. For regular stored functions and procedure that do not return complex PLSQL types the regular StoredFunctionCall, StoredProcedureCall and @NamedStoredFunctionQuery, @NamedStoredProcedureQuery can be used.

To call PLSQL stored functions or procedures mirror OBJECT/VARRAY types must be defined for the RECORD/TABLE types. OBJECT types can be mapped to @Entity or @Embeddable classes using the @Struct annotation. Normally @Embeddable is used, unless the OBJECT type defines an Id and can be stored in a table. Nested OBJECT and VARRAY types can be mapped using the @Structure and @Array annotations.

Oracle stored function returning PLSQL record type

CREATE OR REPLACE PACKAGE EMP_PKG AS
TYPE EMP_REC IS RECORD (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2));
FUNCTION GET_EMP RETURN EMP_REC;
END EMP_PKG;
CREATE OR REPLACE PACKAGE BODY EMP_PKG AS
FUNCTION GET_EMP RETURN EMP_REC AS
  P_EMP EMP_REC;
  BEGIN P_EMP.F_NAME := 'Bob'; P_EMP.F_NAME := 'Smith'; P_EMP.SALARY := 30000;
  RETURN P_EMP;
END;
END EMP_PKG;

An OBJECT type mirror for the EMP_REC type must also be defined.

CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2))

Defining Java class to map to OBJECT type

@Embeddable
@Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"})
public class Employee {
 @Column(name="F_NAME")
 private String firstName;
 @Column(name="L_NAME")
 private String lastName;
 @Column(name="SALARY")
 private BigDecimal salary;
 ...
}

Using JpaEntityManager createQuery() API to execute a PLSQL stored function

import javax.persistence.Query;
import org.eclipse.persistence.platform.database.orcle.plsql.PLSQLStoredFunctionCall;
import org.eclipse.persistence.queries.ReadAllQuery;
 
DataReadQuery databaseQuery = new DataReadQuery();
databaseQuery.setResultType(DataReadQuery.VALUE);
PLSQLrecord record = new PLSQLrecord();
record.setTypeName("EMP_PKG.EMP_REC");
record.setCompatibleType("EMP_TYPE");
record.setJavaType(Employee.class);
record.addField("F_NAME", JDBCTypes.VARCHAR_TYPE, 30);
record.addField("L_NAME", JDBCTypes.VARCHAR_TYPE, 30);
record.addField("SALARY", JDBCTypes.NUMERIC_TYPE, 10, 2);
PLSQLStoredFunctionCall call = new PLSQLStoredFunctionCall(record);
call.setProcedureName("EMP_PKG.GET_EMP");
databaseQuery.setCall(call);
 
Query query = ((JpaEntityManager)entityManager.getDelegate()).createQuery(databaseQuery);
Employee result = (Employee)query.getSingleResult();

Using @NamedPLSQLStoredFunctionQuery to define a stored function

@NamedPLSQLStoredFunctionQuery(name="getEmployee", functionName="EMP_PKG.GET_EMP",
    returnParameter=@PLSQLParameter(name="RESULT", databaseType="EMP_PKG.EMP_REC"))
@Embeddable
@Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"})
@PLSQLRecord(name="EMP_PKG.EMP_REC", compatibleType="EMP_TYPE", javaType=Employee.class,
    fields={@PLSQLParameter(name="F_NAME"), @PLSQLParameter(name="L_NAME"), @PLSQLParameter(name="SALARY", databaseType="NUMERIC_TYPE")})
public class Employee {
 ...
}

Using named query

Query query = entityManager.createNamedQuery("getEmployee");
Employee result = (Employee)query.getSingleResult();