Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Examples/JPA/StoredProcedures"
< EclipseLink | Examples | JPA
(13 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | + | __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] | ||
− | 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. |
+ | |||
+ | Any CRUD or mapping operation can also be overridden using a stored procedure call using a DescriptorCustomizer and the DescriptorQueryManager API. | ||
+ | |||
+ | 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 === | ||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
− | </source> | + | 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; | ||
+ | </source> | ||
+ | |||
+ | === Using JpaEntityManager createQuery() API to execute a stored procedure === | ||
− | |||
<source lang="java"> | <source lang="java"> | ||
import javax.persistence.Query; | import javax.persistence.Query; | ||
Line 27: | Line 39: | ||
ReadAllQuery databaseQuery = new ReadAllQuery(Employee.class); | ReadAllQuery databaseQuery = new ReadAllQuery(Employee.class); | ||
StoredProcedureCall call = new StoredProcedureCall(); | StoredProcedureCall call = new StoredProcedureCall(); | ||
+ | call.setProcedureName("EMP_READ_ALL"); | ||
+ | call.useNamedCursorOutputAsResultSet("RESULT_CURSOR"); | ||
databaseQuery.setCall(call); | databaseQuery.setCall(call); | ||
Line 32: | Line 46: | ||
List<Employee> result = query.getResultList(); | List<Employee> result = query.getResultList(); | ||
− | </source> | + | </source> |
+ | |||
+ | === Using @NamedStoredProcedureQuery to define a stored procedure === | ||
− | |||
<source lang="java"> | <source lang="java"> | ||
− | @NamedStoredProcedureQuery | + | @NamedStoredProcedureQuery(name="findAllEmployees", procedureName="EMP_READ_ALL", resultClass=Employee.class, parameters={ |
+ | @StoredProcedureParameter(queryParameter="RESULT_CURSOR", name="result", direction=Direction.OUT_CURSOR)}) | ||
@Entity | @Entity | ||
public class Employee { | public class Employee { | ||
... | ... | ||
} | } | ||
− | </source> | + | </source> |
+ | |||
+ | === Using named query === | ||
+ | |||
+ | <source lang="java"> | ||
+ | Query query = entityManager.createNamedQuery("findAllEmployees"); | ||
+ | List<Employee> result = query.getResultList(); | ||
+ | </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:
- IN, OUT and INOUT parameter support
- CURSOR output parameter support
- Result set support
- Annotation support using @NamedStoredProcedureQuery
- Dynamic definition support using StoreProcedureCall
- Stored function support using StoredFunctionCall
- Object-relational data-types, Struct, Array (OBJECT types, VARRAY), including mapping using ObjectRelationalDataTypeDescriptor
- PLSQL types, BOOLEAN, RECORD, TABLE, using PLSQLStoredProcedureCall
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,
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;
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();
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 { ... }
Using named query
Query query = entityManager.createNamedQuery("findAllEmployees"); List<Employee> result = query.getResultList();
PLSQLStoredProcedureCall - Complex data
See a real complex sample at http://ronaldoblanc.blogspot.com.br/2012/05/jpa-eclipselink-and-complex-parameters.html (Blog)