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

From Eclipsepedia

Jump to: navigation, search
(Oracle stored procedure using OUT CURSOR)
 
(5 intermediate revisions by 2 users not shown)
Line 1: Line 1:
[[Category:EclipseLink/Example/JPA|StoredProcedures]]
+
__TOC__
  
EclipseLink has extended support for stored procedure execution including:
+
EclipseLink has extended support for stored procedure execution including:  
* IN, OUT and INOUT parameter support
+
* CURSOR output parameter support
+
* Result set support
+
* Annotation support using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/annotations/NamedStoredProcedureQuery.html @NamedStoredProcedureQuery ]
+
* Dynamic definition support using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/queries/StoredProcedureCall.html StoreProcedureCall]
+
* Stored function support using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/queries/StoredFunctionCall.html StoredFunctionCall]
+
* Object-relational data-types, Struct, Array (OBJECT types, VARRAY), including mapping using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/mappings/structures/ObjectRelationalDataTypeDescriptor.html ObjectRelationalDataTypeDescriptor]
+
* PLSQL types, BOOLEAN, RECORD, TABLE, using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.html PLSQLStoredProcedureCall]
+
  
A stored procedure call be used in any query to read objects, or read or modify raw data.
+
*IN, OUT and INOUT parameter support
 +
*CURSOR output parameter support
 +
*Result set support
 +
*Annotation support using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/annotations/NamedStoredProcedureQuery.html @NamedStoredProcedureQuery ]
 +
*Dynamic definition support using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/queries/StoredProcedureCall.html StoreProcedureCall]
 +
*Stored function support using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/queries/StoredFunctionCall.html StoredFunctionCall]
 +
*Object-relational data-types, Struct, Array (OBJECT types, VARRAY), including mapping using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/mappings/structures/ObjectRelationalDataTypeDescriptor.html ObjectRelationalDataTypeDescriptor]
 +
*PLSQL types, BOOLEAN, RECORD, TABLE, using [http://www.eclipse.org/eclipselink/api/2.2/org/eclipse/persistence/platform/database/oracle/plsql/PLSQLStoredProcedureCall.html PLSQLStoredProcedureCall]
  
Any CRUD or mapping operation can also be overridden using a stored procedure call using a DescriptorCustomizer and the DescriptorQueryManager API.
+
A stored procedure call be used in any query to read objects, or read or modify raw data.  
  
See,
+
Any CRUD or mapping operation can also be overridden using a stored procedure call using a DescriptorCustomizer and the DescriptorQueryManager API.
* [http://wiki.eclipse.org/Using_Basic_Query_API_%28ELUG%29#Using_a_StoredProcedureCall User Guide:Using a StoredProcedureCall]
+
 
 +
See,  
 +
 
 +
*[http://wiki.eclipse.org/Using_Basic_Query_API_%28ELUG%29#Using_a_StoredProcedureCall User Guide:Using a StoredProcedureCall]
 +
 
 +
=== Oracle stored procedure using OUT CURSOR  ===
  
=== Oracle stored procedure using OUT CURSOR ===
 
 
<source lang="sql">
 
<source lang="sql">
 
CREATE PROCEDURE EMP_READ_ALL (
 
CREATE PROCEDURE EMP_READ_ALL (
Line 25: Line 28:
 
OPEN RESULT_CURSOR FOR Select e.*, s.* from EMPLOYEE e, SALARY s WHERE e.EMP_ID = s.EMP_ID;  
 
OPEN RESULT_CURSOR FOR Select e.*, s.* from EMPLOYEE e, SALARY s WHERE e.EMP_ID = s.EMP_ID;  
 
END;
 
END;
</source>
+
</source>  
 +
 
 +
=== Using JpaEntityManager createQuery() API to execute a stored procedure  ===
  
=== Using JpaEntityManager createQuery() API to execute a stored procedure ===
 
 
<source lang="java">
 
<source lang="java">
 
import javax.persistence.Query;
 
import javax.persistence.Query;
Line 36: Line 40:
 
StoredProcedureCall call = new StoredProcedureCall();
 
StoredProcedureCall call = new StoredProcedureCall();
 
call.setProcedureName("EMP_READ_ALL");
 
call.setProcedureName("EMP_READ_ALL");
call.useNamedCursorOutputAsResultSet("RESULT_SET");
+
call.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
 
databaseQuery.setCall(call);
 
databaseQuery.setCall(call);
  
Line 42: Line 46:
 
List<Employee> result = query.getResultList();
 
List<Employee> result = query.getResultList();
  
</source>
+
</source>  
 +
 
 +
=== Using @NamedStoredProcedureQuery to define a stored procedure  ===
  
=== Using @NamedStoredProcedureQuery to define a stored procedure ===
 
 
<source lang="java">
 
<source lang="java">
 
@NamedStoredProcedureQuery(name="findAllEmployees", procedureName="EMP_READ_ALL", resultClass=Employee.class, parameters={
 
@NamedStoredProcedureQuery(name="findAllEmployees", procedureName="EMP_READ_ALL", resultClass=Employee.class, parameters={
   @StoredProcedureParameter(queryParameter="result", name="RESULT_SET", direction=Direction.OUT_CURSOR})
+
   @StoredProcedureParameter(queryParameter="RESULT_CURSOR", name="result", direction=Direction.OUT_CURSOR)})
 
@Entity
 
@Entity
 
public class Employee {
 
public class Employee {
 
  ...
 
  ...
 
}
 
}
</source>
+
</source>  
 +
 
 +
=== Using named query  ===
  
=== Using named query ===
 
 
<source lang="java">
 
<source lang="java">
 
Query query = entityManager.createNamedQuery("findAllEmployees");
 
Query query = entityManager.createNamedQuery("findAllEmployees");
 
List<Employee> result = query.getResultList();
 
List<Employee> result = query.getResultList();
</source>
+
</source>  
 +
 
 +
=== PLSQLStoredProcedureCall - Complex data<br> ===
 +
 
 +
See a real complex sample at [http://ronaldoblanc.blogspot.com.br/2012/05/jpa-eclipselink-and-complex-parameters.html http://ronaldoblanc.blogspot.com.br/2012/05/jpa-eclipselink-and-complex-parameters.html (Blog)]<br>
 +
 
 +
[[Category:EclipseLink/Example/JPA|StoredProcedures]]

Latest revision as of 10:44, 24 July 2012

Contents


EclipseLink has extended support for stored procedure execution including:

A stored procedure call be used in any query to read objects, or read or modify raw data.

Any CRUD or mapping operation can also be overridden using a stored procedure call using a DescriptorCustomizer and the DescriptorQueryManager API.

See,

[edit] Oracle stored procedure using OUT CURSOR

CREATE PROCEDURE EMP_READ_ALL (
	RESULT_CURSOR OUT CURSOR_TYPE.ANY_CURSOR) AS
BEGIN 
OPEN RESULT_CURSOR FOR SELECT e.*, s.* FROM EMPLOYEE e, SALARY s WHERE e.EMP_ID = s.EMP_ID; 
END;

[edit] Using JpaEntityManager createQuery() API to execute a stored procedure

import javax.persistence.Query;
import org.eclipse.persistence.queries.StoredProcedureCall;
import org.eclipse.persistence.queries.ReadAllQuery;
 
ReadAllQuery databaseQuery = new ReadAllQuery(Employee.class);
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("EMP_READ_ALL");
call.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
databaseQuery.setCall(call);
 
Query query = ((JpaEntityManager)entityManager.getDelegate()).createQuery(databaseQuery);
List<Employee> result = query.getResultList();

[edit] Using @NamedStoredProcedureQuery to define a stored procedure

@NamedStoredProcedureQuery(name="findAllEmployees", procedureName="EMP_READ_ALL", resultClass=Employee.class, parameters={
  @StoredProcedureParameter(queryParameter="RESULT_CURSOR", name="result", direction=Direction.OUT_CURSOR)})
@Entity
public class Employee {
 ...
}

[edit] Using named query

Query query = entityManager.createNamedQuery("findAllEmployees");
List<Employee> result = query.getResultList();

[edit] PLSQLStoredProcedureCall - Complex data

See a real complex sample at http://ronaldoblanc.blogspot.com.br/2012/05/jpa-eclipselink-and-complex-parameters.html (Blog)