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

Revision as of 11:28, 1 February 2011

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,

• User Guide:Using a StoredProcedureCall

Oracle stored procedure using OUT CURSOR

CREATE PROCEDURE EMP_READ_ALL

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_SET");
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", name="RESULT", direction=Direction.OUT_CURSOR))
@Entity
public class Employee {
 ...
}