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

Revision as of 14:45, 9 May 2011

It is possible to override any EclipseLink generated SQL with custom SQL or a stored procedure call. For the CRUD (Create, Read, Update, Delete) operations these are by default generated by EclipseLink. Each of these operations can be overridden through using a DescriptorCustomizer and the DescriptorQueryManager API.

Custom SQL or stored procedures can be used to perform different functionality, or to adhere to specific security requirements or corporate policies. Note that usage of a stored procedure to execute the same SQL as would be generated will not improve performance.

DescriptorQueryManager allows the following operations to be overridden:

• insert - setInsertCall(Call), setInsertSQLString(String), setInsertQuery(InsertObjectQuery) - persist() operation
• update - setUpdateCall(Call), setUpdateSQLString(String), setUpdateQuery(UpdateObjectQuery) - any direct update
• delete - setDeleteCall(Call), setDeleteSQLString(String), setDeleteQuery(DeleteObjectQuery) - remove() operation
• readObject - setReadObjectCall(Call), setReadObjectSQLString(String), setReadObjectQuery(ReadObjectQuery) - find() operation
• readAll - setReadAllCall(Call), setReadAllSQLString(String), setReadAllQuery(ReadAllQuery) - query operation for "all" instances

Any mapping operation can also be overridden using the ForeignReferenceMapping API:

• selectionQuery - setSelectionCall(Call), setSelectionSQLString(String), setSelectionQuery(ReadQuery), setSelectionCriteria(Expression) - read operation
• CollectionMapping.deleteAll - setDeleteAllCall(Call), setDeleteAllSQLString(String), setCustomDeleteAllQuery(ModifyQuery) - remove operation
• ManyToManyMapping.insertCall - setInsertCall(Call), setDeleteSQLString(String), setCustomInsertQuery(DataModifyQuery) - join table insert
• ManyToManyMapping.deleteCall - setDeleteCall(Call), setDeleteSQLString(String), setCustomDeleteQuery(DataModifyQuery) - join table remove

Oracle insert stored procedure

CREATE PROCEDURE EMP_INSERT (	
	P_EMP_ID NUMBER,
	P_SALARY NUMBER,
	P_F_NAME VARCHAR2,
	P_L_NAME VARCHAR2) AS
BEGIN 
INSERT INTO EMPLOYEE (EMP_ID, F_NAME, L_NAME, SALARY) VALUES (P_EMP_ID, P_F_NAME, P_L_NAME, P_SALARY); 
END;

Using DescriptorCustomizer to use a stored procedure for insert

import org.eclipse.persistence.config.DescriptorCustomizer;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.queries.StoredProcedureCall;
 
public class MyCustomizer implements DescriptorCustomizer {
  public void customize(ClassDescriptor descriptor) {
    StoredProcedureCall call = new StoredProcedureCall();
    call.setName("EMP_INSERT");
    call.addArgument("P_EMP_ID", "EMP_ID");
    call.addArgument("P_F_NAME", "F_NAME");
    call.addArgument("P_L_NAME", "L_NAME");
    call.addArgument("P_SALARY", "SALARY");
    descriptor.getQueryManager().setInsertCall(call);
  }
}