Java Reference
In-Depth Information
Support for Database Stored Procedures
In some applications, database stored procedures are used to encapsulate logic for performing database-specific
transactions. Many times, these database stored procedures need to be invoked from an application. Historically,
the only way to work with database-stored procedures from JPA was to utilize a native query. To create a native query,
a SQL string needs to be created, which can then be passed to the EntityManager createNativeQuery method. The
returned Query object can then be used to invoke the stored procedure.
Suppose that there was a database procedure named CREATE_EMP , and it accepted three arguments: first, last
and status . You could invoke the CREATE_EMP procedure by calling it via a native SQL query. The following method,
named createEmpOld , accepts a first , last , and status as arguments, and passes them to the underlying database
procedure and executes it.
public void createEmpOld(String first,
String last,
String status) {
Query qry = em.createNativeQuery("select CREATE_EMP(:first, :last, :status) from dual")
.setParameter("first", first)
.setParameter("last", last)
.setParameter("status", status);
Using this technique, the EntityManager's createNativeQuery method is called, and a SQL String that
performs a SELECT on the stored procedure is passed to the method. In SQL, performing a SELECT on a stored
procedure will cause the procedure to be executed. Notice that the DUAL table is being referenced in the SQL. The
DUAL is a dummy table in Oracle that can be used when you need to apply SELECT statements to different database
constructs, such as a stored procedure. One could also issue the following SQL string to do the same thing.
"begin create_emp(:first, :last, :status); end; "
"exec create_emp(:first, :last, :status) ";
Execution of native SQL is an acceptable solution for invoking stored procedures that have no return values, or
when you only have a limited number of SQL statements to maintain. However, in most enterprise situations that
require an application with multiple stored procedure calls or calls that require a return value, the better technique is
to utilize the new @NamedStoredProcedureQuery annotation within an entity class to specify the name and parameters
of a stored procedure.
@NamedStoredProcedureQuery(name="createEmp", procedureName="CREATE_EMP")
Execution of the stored procedure occurs within the EJB by calling the EntityManager object's
createNamedStoredProcedureQuery method, and passing the name that was specified within the
@NamedStoredProcedureQuery annotation. The following method, which resides within the org.javaee7.jpa.
session.EmployeeSession , can be used to invoke the underlying database's CREATE_EMP procedure.
public boolean createEmp(String firstName, String lastName, String status){
boolean result = false;
System.out.println("executing the stored procedure");
StoredProcedureQuery qry = em.createNamedStoredProcedureQuery("createEmp");
Search WWH ::

Custom Search