JPA 2.1 Stored Procedure Example

JPA 2.1 Stored Procedure Example - Lear how to call stored procedures from JPA program

In this tutorial you will learn how to create Stored Procedure in MySQL database and then call the stored procedure in your JPA 2.1 based application. Now JPA 2.1 supports stored procedures which makes programming much easier for programmers. You can easily call stored procedure from your Java applications and also easily get the results returned by the stored procedure.

What is Stored Procedure?

Stored procedures are the objects (Stored procedures/functions) created on the database server and resides on the database server. These are database specific and created using the SQL statements. In oracle this language is called PL/SQL. Oracle database server also supports stored procedure created in the Java programming language. In MySQL SQL statements are used to create stored procedure. Stored procedures are database specific and stored procedure from one database can't be moved to another database. Stored procedure portability is another big issue.

Stored procedures are mostly used for batch processing activities. In some cases it is used to solve some specific tasks which is not so easy in SQL.

You should also keep the disadvantages of the stored procedures while designing your application. Stored procedures are not portable and it often written in different language which makes little difficult for the programmers developing the application.

Stored procedures support in JPA 2.1

You can use the JPA 2.1 to call the stored procedures and it is done with the help of StoredProcedureQuery, and @NamedStoredProcedureQuery annotation or <named-stored-procedure-query> XML element. You can use the JPA to call names stored procedure through EntityManager.createNamedStoredProcedureQuery() and dynamic stored procedure through EntityManager.createStoredProcedureQuery(). In this example we are using EntityManager.createStoredProcedureQuery() to call the stored procedure.

Let's start developing the example program

We will use the same entity as described in the tutorial Writing first JPA 2.1 application. The name of entity is Product (Product.java) which is mapped to the database table "products". Here are the steps of create the example:

Step 1: Create stored procedure

Our stored procedure is very simple it just returns the no of records in the "products" table. Here is the sql to create stored procedure:

delimiter //
CREATE PROCEDURE CountProducts(OUT totalProducts INT)
SELECT COUNT(*) INTO totalProducts FROM products;
//

If you want to test the store procedure on the MySQL prompt you can use the following query:

delimiter //
CALL CountProducts(@countProducts);
//

select @countProducts;

Step 2: Write the code to call the stored procedure in Java program and then print the retuned value e.g. no of records in the table.

Here is the code of the Java program (AppTestStoredProcedure.java) which is used to call the stored procedure:

package net.roseindia;

import net.roseindia.model.*;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.ParameterMode;
import javax.persistence.Persistence;
import javax.persistence.StoredProcedureQuery;
		/*
		//Compile mvn compile
		//Run: mvn exec:java -Dexec.mainClass="net.roseindia.AppTestStoredProcedure"
		
		*/
/**
* @author Deepak Kumar
* More tutorials at http://www.roseindia.net
*/
		
public class AppTestStoredProcedure {

	private static final String PERSISTENCE_UNIT_NAME = "psunit1";
	private static EntityManagerFactory factory;

	public static void main(String[] args) {
	factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
	EntityManager em = factory.createEntityManager();

		// Now we will call the stored procedure
        em.getTransaction().begin();
        StoredProcedureQuery storedProcedure = 
               em.createStoredProcedureQuery("CountProducts");
        // set output parameter 'totalProducts'
        storedProcedure.registerStoredProcedureParameter("totalProducts",
        Integer.class, ParameterMode.OUT);
        // execute Stored Procedure
        storedProcedure.execute();
        // get result
        Integer totalCount = 
        (Integer)storedProcedure.getOutputParameterValue("totalProducts");
        System.out.println("Total Records is: " + totalCount);
        em.getTransaction().commit();
        em.close();		
	}
}

In the above example code we have used the createStoredProcedureQuery() function of the EntityManager instance. The method registerStoredProcedureParameter() of the class StoredProcedureQuery is used to register the INPUT and OUTPUT parameters. The storedProcedure.execute() method is used to execute the stored procedure. The method storedProcedure.getOutputParameterValue("totalProducts") is used to get the parameter value.

Step 3: Compile and test the application.

To compile type the following code:

mvn compile

To run the JPA 2.1 stored procedure call example type following code:

mvn exec:java -Dexec.mainClass="net.roseindia.AppTestStoredProcedure"

In this you have leaned how to call simple stored procedure using JPA 2.1 specification.

Download the source code of the application discussed here.

Read more JPA 2.1 tutorials.

JPA 2.1 Stored Procedure Example

JPA 2.1 Stored Procedure Example

JPA 2.1 Stored Procedure Example - Lear how to call stored procedures from JPA program

Tutorials