Java EE 7: Applying JPA to Stored Procedures

Purpose

This tutorial demonstrates how to invoke a stored procedure residing in a database by using Java Persistence API 2.1 (JPA 2.1).

Time to Complete

Approximately 45 minutes

Introduction

Java Specification Request 238 (JSR 338) defines the specification of the Java API for the management of persistence and object/relational mapping with Java Platform, Enterprise Edition 7 (Java EE 7) and Java Platform, Standard Edition (Java SE). JPA 2.1 added support for the invocation of predefined and user-defined stored procedures residing in the database. 

The StoredProcedureQuery interface supports database-stored procedures. You can specify stored procedures with the NamedStoredProcedureQuery annotation or you can specify them dynamically.

• The NamedStoredProcedureQuery annotation names a stored procedure residing in a database, specifies the parameter types for the stored procedure, and specifies mapping for the result sets. You use the createNamedStored-ProcedureQuery method to create an executable StoredProcedureQuery object.
• In a dynamically specified stored procedure, you must register all parameters by using the registerStoredProcedureParameter method of the StoredProcedureQuery interface. You can provide the result set mapping information with the createStoredProcedureQuery method.

In this tutorial, you use JPA 2.1 to invoke a stored procedure dynamically.

Scenario

This tutorial implements a simple scenario to demonstrate how to use JPA 2.1 to invoke an existing stored procedure in the Java database (Java DB) server.

You are provided with source files and scripts to create a stored procedure in the sample database of the Java DB server. The stored procedure, CUSTOMERCOUNT, returns the count of customers whose credit limit is above a specific value.

You modify a web application that contains the following:

• An Enterprise JavaBeans (EJB) class with a method that dynamically invokes the CUSTOMERCOUNT stored procedure
• A servlet that accepts user input for the credit limit, invokes the EJB method, and displays the count of customers whose credit limit is above the input value

Hardware and Software Requirements

The following is a list of hardware and software requirements:

• Java SE 7 (Java SE 7u11 recommended)
• NetBeans 7.3.1 IDE for Java EE 7
• Oracle GlassFish Server 4

Prerequisites

Before starting this tutorial, you should:

• Have some experience writing and deploying web applications.
• Have some experience using JPA 2.1 in a web application.
• Have installed NetBeans 7.3.1, Java EE 7, and GlassFish Server 4.
• Have started NetBeans.
• Have unzipped the CustomerStoredProc.zip file.
• Have opened the CustStoredProc project in NetBeans.
• Have unzipped the JPAExampleApp.zip file.
• Have opened the JPAExampleApp project in NetBeans.

In this section, you create the CustomerStoredProc stored procedure in the sample database of the Java DB server.

1. On the Projects tab, expand CustStoredProc > Source Packages > <default package> and then click CustStoredProc.java to open it in the code editor window.

   

2. Examine the CusStoredProc.java file and observe the content of the CountCustomer method. Make a note of the method's arguments and data types.
   

   

   
   

3. On the Projects tab, right-click CustomerStoredProc and select Clean and Build.

4. On the Files tab, verify that CustStoredproc.jar is located in the dist folder.
   

   

5. In Windows Explorer, copy CustStoredproc.jar from the CustStoredProc > dist folder and paste it in the D:\ drive of your system.

6. On the NetBeans Services tab, perform the following steps:

   1. Expand Databases.
   2. Right-click jdbc:derby://localhost:1527/sample [app on APP].
   3. Select Connect.

7. In the Connect dialog box, enter app for the password and click OK.

   

8. Complete the following steps to open a script file:

   1. Select Files > Open File.
   2. Browse to the location where you have extracted the CustomerStoredproc.zip file.
   3. Select scripts.sql and click Open.

9. Complete the following steps to open a script file:

   1. In the Code Editor window, select jdbc:derby://localhost:1527/sample [app on APP] from the Connection list.
   2. Select the code snippet under SECTION 1, right-click, and select Run Selection.
   3. Select the code snippet under SECTION 2, right-click, and select Run Selection.

   

10. On the Services tab, expand Databases > jdbc:derby://localhost:1527/sample [app on APP] > App > Procedures and verify that the CUSTOMERCOUNT stored procedure was created.

    

In this section, you review the JPAExampleApp web application in NetBeans.

1. On the Projects tab, expand JPAExampleApp and view the application structure.



2. Expand JPAExampleApp > Web Pages and click index.jsp to open it.

3. Examine the code within the <form> tags.
   

   

4. On the Projects tab, expand JPAExampleApp > Source Packages > com.example.sp and ensure that RunProcServlet.java is displayed.

In this section, you create a persistence unit and a stateless session bean, CustEJB. You add a method to CustEJB that invokes a stored procedure.

Creating the Persistence Unit

1. On the Projects tab, right-click JPAExampleApp and select New > Persistence Unit.



2. Perform the following steps in the New Persistence Unit dialog box:

   1. Select jdbc/sample for Data Source.
   2. Select the Use Java Transaction APIs check box.
   3. Select None for Table Generation Strategy.
   4. Click Finish.

3. On the Projects tab, expand JPAExampleApp > Configuration Files and then perform the following steps:

   1. Verify that Persistence.xml was created.
   2. Open Persistence.xml and verify its configuration information.

Developing the Session Bean

1. On the Projects tab, expand JPAExampleApp > Source Packages.com.example.beans and then perform the following steps:

   1. Right-click com.example.beans and select New > Session Bean.
   2. In the New Session Bean dialog box, provide the following information:
   • EJB Name: Enter CustEJB.
   • Package: Select com.example.beans.
   • Session Type: Select Stateless.
   3. Click Finish.

2. Open CustEJB.java and modify it to add the following:
1. An @LocalBean annotation to the bean class
2. An instance of EntityManager with an @PersistenceContext annotation
3. A method, with the following signature:
   

   public Integer invokeStoredProcedure(String limit) 

4. In the invokeStoredProcedure method, declare an instance of StoredProcedureQuery to perform the following:
• Invoke the createStoredProcedureQuery method of EntityManager and store its return value in the StoredProcedureQuery instance.
• Register the input and output parameters of the CUSTOMERCOUNT stored procedure by using the registerStoredProcedureParameter method.
• Set the values of the input parameter by using the setParameter method.
• Execute the stored procedure by using the execute method.
5. All required import statements

package com.example.beans;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.StoredProcedureQuery;
@Stateless
@LocalBean
public class CustEJB {

    @PersistenceContext
    private EntityManager em;

    
    public Integer invokeStoredProcedure(String limit) {
        StoredProcedureQuery storedProcQuery = em.createStoredProcedureQuery("CustomerCount");
        // Parameters can be numbered or named
        // Derby stored procedures use numbers
        storedProcQuery.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
        storedProcQuery.registerStoredProcedureParameter(2, Integer.class, ParameterMode.OUT);
      int limitamt=Integer.parseInt(limit);
        storedProcQuery.setParameter(1,limitamt);
        boolean result = storedProcQuery.execute();
        return (Integer) storedProcQuery.getOutputParameterValue(2);


    }
}

 In this section, you modify the RunProcServlet class to invoke the invokeProcedure method of the CustEJB class.

1. On the Projects tab, expand JPAExampleApp > Source Packages > com.example.sp.

2. Open RunProcServlet.java and perform the following steps to modify it:

   1. Declare an instance of CustEJB with an @EJB annotation.
   2. Locate the processRequest method.
   3. Retrieve the value of the txtlimit parameter from the Request object and store it in a string variable named limit.
   4. Invoke the invokeProcedure method of the CustEJB class and provide the limit string variable as its argument value.
   5. Add the appropriate catch statement.
   6. Import the required classes.
   7. Retain that the pre-exisitng methods in the class.

package com.example.sp;

import com.example.beans.CustEJB;
import java.io.IOException;
import java.io.PrintWriter;
import javax.ejb.EJB;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet(name = "RunProcServlet", urlPatterns = {"/RunProcServlet"})
public class RunProcServlet extends HttpServlet {

    @EJB
    private CustEJB custejb;

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        try (PrintWriter out = response.getWriter()) {

            String limit = request.getParameter("txtlimit");

            out.print("<h2>JPA 2.1 Stored Procedure Result </h2>");
            out.print("<h4>Number of Customers with credit limit above " + limit + "  =  " + custejb.invokeStoredProcedure(request.getParameter("txtlimit")) + "</h4>");


        } catch (Exception e) {
            System.out.println("problem with return of proc   " + e);
        }
    }
// per-exisiting methods go here

3. On the Projects tab, expand JPAExampleApp > Web Pages and open index.jsp.

   1. Locate the starting <Form> tag.
   2. Ensure that the <Form> tag has the action attribute whose value points to the RunProcServlet servlet.

   <form method="POST" action="${pageContext.request.contextPath}/RunProcServlet">

1. Perform the following steps on the Projects tab:

   1. Right-click JPAExampleApp and select Clean and Build.
   2. Right-click BatchExampleApp and select Run.

   The home page opens in the web browser.





2. Enter 50000 in the Credit Limit text box and click Invoke Stored Procedure.

   The result set of the CUSTOMERCOUNT stored procedure is displayed.



3. Repeat step 2 with credit limit values of 25000, 90000, and 70000, and verify that the number of customers changes.

In this tutorial, you learned how to:

• Create a stored procedure in the Java DB server
• Use JPA 2.1 to invoke the stored procedure dynamically in a web application:
• Declare the stored procedure
• Register the input and output parameters
• Provide the parameter values
• Execute the stored procedure and retrieve the result set
• Display the values from the result set

Resources

To learn more about Java EE 7 and the batch processing API, see the following resources:

• JSR 338: Java Persistence API 2.1
  
• Java Persistence API 2.1: What’s New and What’s Coming
• Introduction to Java Platform, Enterprise Edition 7
• To learn more about Java EE 7, visit other OBE tutorials in the Oracle Learning Library.

Credits

• Lead Curriculum Developer: Paromita Dutta
• Contributor: Tom McGinn
• QA: Diganta Choudhury
• Editor: Susan Moxley