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);
qry.executeUpdate();
}
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; "
OR
"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");
