How to use Java and JPA to call a stored procedure

Problem

I’m working on a basic web application that will call a stored procedure and retrieve data. It’s a straightforward application that interacts with the client’s database. The employee id and company id are passed to the stored procedure, which returns employee information.

The web application uses SQL Server and is unable to edit or delete data.

Jboss AS is where I’m deploying my web application. Should I use JPA to access the stored procedure or CallableStatement. Any advantage of using JPA in this case.

Also, what SQL statement will be used to call this stored procedure? I’ve never worked with stored procedures before, and I’m having trouble with this one. Google was of little assistance.

The saved procedure is as follows:

CREATE procedure getEmployeeDetails (@employeeId int, @companyId int)
as
begin
    select firstName, 
           lastName, 
           gender, 
           address
      from employee et
     where et.employeeId = @employeeId
       and et.companyId = @companyId
end

Update:

Anyone else experiencing trouble using JPA to call stored procedures?

Query query = em.createNativeQuery("{call getEmployeeDetails(?,?)}",
                                   EmployeeDetails.class)           
                                   .setParameter(1, employeeId)
                                   .setParameter(2, companyId);

List<EmployeeDetails> result = query.getResultList();

I’ve noticed the following:

Asked by user431514

Solution #1

JPA 2.1 now supports Stored Procedures; for more information, see the Java documentation.

Example:

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("sales_tax");
// set parameters
storedProcedure.registerStoredProcedureParameter("subtotal", Double.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("tax", Double.class, ParameterMode.OUT);
storedProcedure.setParameter("subtotal", 1f);
// execute SP
storedProcedure.execute();
// get result
Double tax = (Double)storedProcedure.getOutputParameterValue("tax");

Here’s a more detailed example.

Answered by Pau Kiat Wee

Solution #2

JPA doesn’t really support it, but it’s doable. Even so, I wouldn’t go this route:

Given the simplicity of your application, I would recommend using Spring support for JDBC data access, a data mapper like MyBatis, or raw JDBC and CallableStatement. In fact, JDBC would be my first option. Here’s an example of a basic kickoff:

CallableStatement cstmt = con.prepareCall("{call getEmployeeDetails(?, ?)}");
cstmt.setInt("employeeId", 123);
cstmt.setInt("companyId", 456);
ResultSet rs = cstmt.executeQuery();

Answered by Pascal Thivent

Solution #3

The parameters must be passed to the stored procedure.

This is how it should work:

    List result = em
      .createNativeQuery("call getEmployeeDetails(:employeeId,:companyId)")
      .setParameter("emplyoyeeId", 123L)
      .setParameter("companyId", 456L)
      .getResultList();

Update:

Perhaps it shouldn’t.

JPA does not support stored procedures, according to page 383 of the book EJB3 in Action (page is merely a preview, you don’t receive the whole content; the entire book is available as a download in various locations, including this one; I’m not sure if this is legal).

Anyway, here’s the text:

Answered by Sean Patrick Floyd

Solution #4

Check out the following articles for further information on how to use JPA and Hibernate to invoke stored procedures and functions.

Answered by Vlad Mihalcea

Solution #5

Despite the fact that this answer goes into further detail about returning a recordset from a stored procedure, I’m posting here because it took me a long time to figure it out and this discussion was quite helpful.

My application was utilizing Eclipselink-2.3.1, but I’m forcing an update to Eclipselink-2.5.0 because JPA 2.1 has significantly improved stored procedure support.

This technique is special to EclipseLink implementation because it requires imports of EclipseLink classes from “org.eclipse.persistence.”

“http://www.yenlo.nl/en/calling-oracle-stored-procedures-from-eclipselink-with-multiple-out-parameters” was where I discovered it.

StoredProcedureCall storedProcedureCall = new StoredProcedureCall();
storedProcedureCall.setProcedureName("mypackage.myprocedure");
storedProcedureCall.addNamedArgument("i_input_1"); // Add input argument name.
storedProcedureCall.addNamedOutputArgument("o_output_1"); // Add output parameter name.
DataReadQuery query = new DataReadQuery();
query.setCall(storedProcedureCall);
query.addArgument("i_input_1"); // Add input argument names (again);
List<Object> argumentValues = new ArrayList<Object>();
argumentValues.add("valueOf_i_input_1"); // Add input argument values.
JpaEntityManager jpaEntityManager = (JpaEntityManager) getEntityManager();
Session session = jpaEntityManager.getActiveSession();
List<?> results = (List<?>) session.executeQuery(query, argumentValues);
DatabaseRecord record = (DatabaseRecord) results.get(0);
String result = String.valueOf(record.get("o_output_1")); // Get output parameter

This method is not dependant on the implementation (no Eclipslink imports are required).

StoredProcedureQuery query = getEntityManager().createStoredProcedureQuery("mypackage.myprocedure");
query.registerStoredProcedureParameter("i_input_1", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("o_output_1", String.class, ParameterMode.OUT);
query.setParameter("i_input_1", "valueOf_i_input_1");
boolean queryResult = query.execute();
String result = String.valueOf(query.getOutputParameterValue("o_output_1"));

Answered by Malcolm Boekhoff

Post is based on https://stackoverflow.com/questions/3572626/how-to-call-a-stored-procedure-from-java-and-jpa