Jump to: navigation, search

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

(New page: CRUDStoredProcedures __TOC__ It is possible to override any EclipseLink generated SQL with custom SQL or a stored procedure call. For the CRUD (Create...)
 
 
(2 intermediate revisions by the same user not shown)
Line 4: Line 4:
 
It is possible to override any EclipseLink generated SQL with custom SQL or a stored procedure call.
 
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.
 
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.
+
Each of these operations can be overridden through using a [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/config/DescriptorCustomizer.html DescriptorCustomizer] and the [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/descriptors/DescriptorQueryManager.html DescriptorQueryManager] API.
  
 
Custom SQL or stored procedures can be used to perform different functionality, or to adhere to specific security requirements or corporate policies.
 
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.
 
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 [http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/mappings/ForeignReferenceMapping.html 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 ===
 
=== Oracle insert stored procedure ===
 
<source lang="sql">
 
<source lang="sql">
CREATE PROCEDURE EMP_INSERT (
+
CREATE PROCEDURE EMP_INSERT (
RESULT_CURSOR OUT CURSOR_TYPE.ANY_CURSOR) AS
+
P_EMP_ID NUMBER,
 +
P_SALARY NUMBER,
 +
P_F_NAME VARCHAR2,
 +
P_L_NAME VARCHAR2) AS
 
BEGIN  
 
BEGIN  
OPEN RESULT_CURSOR FOR Select e.*, s.* from EMPLOYEE e, SALARY s WHERE e.EMP_ID = s.EMP_ID;  
+
INSERT INTO EMPLOYEE (EMP_ID, F_NAME, L_NAME, SALARY) VALUES (P_EMP_ID, P_F_NAME, P_L_NAME, P_SALARY);  
 
END;
 
END;
 
</source>
 
</source>
Line 27: Line 43:
 
   public void customize(ClassDescriptor descriptor) {
 
   public void customize(ClassDescriptor descriptor) {
 
     StoredProcedureCall call = new StoredProcedureCall();
 
     StoredProcedureCall call = new StoredProcedureCall();
     call.setName("EMP_INSERT");
+
     call.setProcedureName("EMP_INSERT");
     call.addArgument();
+
     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);
 
     descriptor.getQueryManager().setInsertCall(call);
 
   }
 
   }
 
}
 
}
 
</source>
 
</source>

Latest revision as of 14:33, 17 October 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.setProcedureName("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);
  }
}