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.
