Use Store Procedure in JPA

Usually, we use JDBC to call store procedure and it works well.However, i found that it is hard to call store procedure when i used OpenJPA in my project.Frankly speaking, i try my best to think up possible solutions like using EntityManager's native query or getting the Connection object from OpenJPA API, but it is still not so convenient and lots of trouble indeed.I began to doubt the JPA specification and implementation, then i read the JPA specification totally. I am excited to note that JPA 2.1 specification draft the store procedure support.

Store procedure is highlighted in JPA 2.1 specification:
Support for Stored Procedures: Added support for the invocation of predefined database functions and user-defined database functions to the Java Persistence query language.
There are different variants of EntityManager.createXXXStoredProcedureQuery methods that return aStoredProcedureQuery for executing a stored procedure.  Just liked @NamedQuery, there is@NamedStoredProcedureQuery that specifies and names a stored procedure, its parameters, and its result type. This annotation can be specified on an entity or mapped superclass. The name specified in the annotation is then used in EntityManager.createNamedStoredProcedureQuery. The IN, OUT, and INOUT parameters can be set and used to retrieve values passed back from the procedure.

Moreover, EclipseLink is the best one of JPA implementations which supports store procedure. 
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.

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();