@NamedStoredProcedureQuery in Action

Table of Content :

• Introduction
• Create the JPA Diagram
• Create the Stored Procedure
• Generate the entity classes
• Existing Database
• Create tester to call Named Stored Procedure
• Deploy the EJB Module
• Output
• Source Code

Introduction :

In this tutorial we will go through the basic execution of stored procedure using JPA Modeler .

JPA 2.1 supports :

1. Named stored procedures calls defined in @NamedStoredProcedureQuery annotation and created through EntityManager.createNamedStoredProcedureQuery() . 
2. Dynamic stored procedure calls created through EntityManager.createStoredProcedureQuery(). 

StoredProcedureQuery is a JPA Query that provides additional API for setting the stored procedure parameters, and for accessing output parameters and multiple result sets. A StoredProcedureQuery can return entity objects, or data, similar to native SQL queries. A ResultSetMapping can be used to map the returned fields to the entity columns.

Create the JPA Diagram :

1. Goto File menu > New File > Persistence category
2. From the Persistence , select JPA Diagram from Database ( or create new JPA Diagram ) and click Next. 

   

   Select JPA Diagram

    
3. Select table and add to import entity from DB .

   

   Select Tables

4. Type SmapleERD for the diagram name. 
5. Type com.jpamodeler.nsp for the Package. 
6. Click Finish.

   

   Create JPA Diagram 
   

   
   

When we click Finish, the IDE creates the JPA Diagram and opens the diagram in the designer window.

Create the Stored Procedure :

1. Click on the entity > Properties . 
2. From the properties , select Named Stored Procedure Query Property.

   

   Open Stored Procedure Query Panel

3. Click on the Add button to create new named stored procedure .

   

   Create Stored Procedure 

4. In this panel , first select the Database connection. 
5. It will fetch the existing stored procedure list from the DB. 
6. Now select the Stored Procedure and it will fetch existing stored procedure parameter list from the DB.

   

   Select Database Connection to fetch SP 

7. Click on the Result Set Tab to add result classes or resultset mappings.

   

   Select Result Classes

8. Click on the save button to save named stored procedure.

   

   Created Stored Procedure

Generate the entity classes :

1. Right click on the diagram > Generate Source Code

   

   Generate Source Code 

2. Click on the Student entity class to view source code. 

Existing Database :

Click on the Services window > Databases

Database existing table and data 

I have used MySQL as backend database, however PostgreSQL , MS SQL Server or Oracle can also be used. As the schema I have used a simple table to store student’s data.

Student Table SQL Query :

CREATE TABLE student (`SID` INT NOT NULL, `FNAME` VARCHAR(10) NOT NULL, `LNAME` VARCHAR(10), `DEPT` VARCHAR(10), `BATCH` INT, `EMAIL` VARCHAR(30), PRIMARY KEY (`SID`));

INSERT INTO sample_db.student (`SID`, `FNAME`, `LNAME`, `DEPT`, `BATCH`, `EMAIL`) VALUES (1, 'Shiwani', 'Gupta', 'CSE', 2015, 'jShiwanGupta@gmail.com'); 

INSERT INTO sample_db.student (`SID`, `FNAME`, `LNAME`, `DEPT`, `BATCH`, `EMAIL`) VALUES (2, 'Gaurav', 'Gupta', 'IT', 2012, 'gaurav.gupta.jc@gmail.com');

Stored Procedure (MySQL) :

DELIMITER @@
DROP PROCEDURE studentAll @@
CREATE PROCEDURE sample_db.studentAll
(
 IN BATCH_NO INT
)
BEGIN
  SELECT * FROM STUDENT WHERE BATCH = BATCH_NO;
END @@ 
DELIMITER ; 

Create tester to call Named Stored Procedure :

In this exercise we will create a simple startup singleton bean to esecute the store procedure and fetch the student by batch :

1. Right-click the EJB module and choose File > New File wizard . 
2. In the New File wizard, expand the Enterprises JavaBeans and select Session Beans as shown in the figure below.

   

   Create Tester

3. Now , we have to specify the EJB Name and the Package Location in the appropriate text fields and then click Finish. 
4. When we click Finish, the class Tester.java opens in the Source Editor.

Deploy the EJB Module :

We can now build and deploy the EJB module. Right-click the StoredProceduresModule module and choose Deploy. When we click Deploy, the IDE builds the ejb module and deploys the JAR archive to the server. In the Services window, if we expand the Applications node of GlassFish Server, we can see that StoredProceduresModule was deployed.

After we have defined the @NamedStoredProcedureQuery, we can use the createNamedStoredProcedureQuery(String name) method of the EntityManager to create a StoredProcedureQuery object. This provides the required methods to set the input parameter, call the stored procedure and get the result.

Output :

INFO: com.jpamodeler.nsp.Student actually got transformed 
INFO: EclipseLink, version: Eclipse Persistence Services - 2.5.0.v20130507-3faac2b 
INFO: file:/G:/jShiwaniGupta/NetBeans_Projects/StoredProceduresModule/build/classes/_DEFAULT_PU login successful 
INFO: EJB5181:Portable JNDI names for EJB Tester: [java:global/StoredProceduresModule/Tester, java:global/StoredProceduresModule/Tester!com.jpamodeler.nsp.Tester] 
INFO: Executing Stored Procedure Query ................ 
INFO: Student : Shiwani Gupta 
INFO: StoredProceduresModule was successfully deployed in 1,185 milliseconds.

Download Source Code : 

Github - Named Stored Procedure