Call DB Stored Procedure using @NamedStoredProcedureQuery Injection
- by anwilson
Oracle Database Stored Procedure can be called from EJB business layer to perform complex DB specific operations. This approach will avoid overhead from frequent network hits which could impact end-user result. DB Stored Procedure can be invoked from EJB Session Bean business logic using org.eclipse.persistence.queries.StoredProcedureCall API. Using this approach requires more coding to handle the Session and Arguments of the Stored Procedure, thereby increasing effort on maintenance. EJB 3.0 introduces @NamedStoredProcedureQuery Injection to call Database Stored Procedure as NamedQueries. This blog will take you through the steps to call Oracle Database Stored Procedure using @NamedStoredProcedureQuery.EMP_SAL_INCREMENT procedure available in HR schema will be used in this sample.Create Entity from EMPLOYEES table.Add @NamedStoredProcedureQuery above @NamedQueries to Employees.java with definition as given below -
@NamedStoredProcedureQuery(name="Employees.increaseEmpSal", procedureName = "EMP_SAL_INCREMENT",
resultClass=void.class, resultSetMapping = "", returnsResultSet = false, parameters = {
@StoredProcedureParameter(name = "EMP_ID", queryParameter = "EMPID"),
@StoredProcedureParameter(name = "SAL_INCR", queryParameter = "SALINCR")}
)
Observe how Stored Procedure's arguments are handled easily in @NamedStoredProcedureQuery using @StoredProcedureParameter.Expose Entity Bean by creating a Session Facade.Business method need to be added to Session Bean to access the Stored Procedure exposed as NamedQuery.
public void salaryRaise(Long empId, Long salIncrease) throws Exception {
try{
Query query = em.createNamedQuery("Employees.increaseEmpSal");
query.setParameter("EMPID", empId);
query.setParameter("SALINCR", salIncrease);
query.executeUpdate();
} catch(Exception ex){
throw ex;
}
}
Expose business method through Session Bean Remote Interface.
void salaryRaise(Long empId, Long salIncrease) throws Exception;
Session Bean Client is required to invoke the method exposed through remote interface.Call exposed method in Session Bean Client main method.
final Context context = getInitialContext();
SessionEJB sessionEJB = (SessionEJB)context.lookup("Your-JNDI-lookup");
sessionEJB.salaryRaise(new Long(200), new Long(1000));
Deploy Session BeanRun Session Bean Client.Salary of Employee with Id 200 will be increased by 1000.
