JPA SQL Stored Procedure Example

Here we will discuss about the JPA SQL Stored Procedures, i.e. using database Stored Procedures from JPA (Java Persistance API) along with Cursors, Multiple Parameters (IN & OUT) etc. We have used EclipseLink for our project. However this can also be done with other JPA providers such as Hibernate, Toplink, Spring Data JPA, etc. JPA 2.1 supports calling database stored procedures using the StoredProcedureQuery and @NamedStoredProcedureQuery annotation or XML element.

JPA supports both named stored procedures calls defined in meta-data and created through EntityManager.createNamedStoredProcedureQuery(), and 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.lds to the entity columns.

1. The Backend Database

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

Create_Table.sql

CREATE TABLE STUDENT (
SID NUMBER(3,0) PRIMARY KEY,
FNAME VARCHAR2(10) NOT NULL,
LNAME VARCHAR2(10),
DEPT VARCHAR2(10) DEFAULT 'N/A',
year number(1,0),
email varchar2(30)
);

2. The Students Class

The Student class is an entity (POJO) class, annotated with the javax.persistence.Entity annotation. It uses the @Id annotation to define its id property, and the @GeneratedValue annotation with strategy set to GenerationType.AUTO so that the id gets auto-generated values.

Student.java

package com.javacodegeeks.examples.jpa.entity;

import javax.persistence.*;

@Entity
@Table
public class Student {
	 @Id
	 @GeneratedValue(strategy= GenerationType.AUTO)
	 private int sid;
	 private String fname;
	 private String lname;
	 private String dept;
	 private int year;
	 private String email;
	
	public Student() {
		// TODO Auto-generated constructor stub
	}
	
	public Student(int sid, String fname, String lname, String dept, int year,
			String email) {
		super();
		this.sid = sid;
		this.fname = fname;
		this.lname = lname;
		this.dept = dept;
		this.year = year;
		this.email = email;
	}

	public int getSid() {
		return sid;
	}

	public void setSid(int sid) {
		this.sid = sid;
	}

	public String getFname() {
		return fname;
	}

	public void setFname(String fname) {
		this.fname = fname;
	}

	public String getLname() {
		return lname;
	}

	public void setLname(String lname) {
		this.lname = lname;
	}

	public String getDept() {
		return dept;
	}

	public void setDept(String dept) {
		this.dept = dept;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	@Override
	public String toString() {
		return "Student [sid=" + sid + ", fname=" + fname + ", lname=" + lname
				+ ", dept=" + dept + ", year=" + year + ", email=" + email
				+ "]";
	}

}

3. Using createQuery() and StoredProcedureCall With Cursor

We will start by using the method Query createQuery(DatabaseQuery query) and StoredProcedureCall for using stored procedure from Java. these helps to call stored procedures dynamically. We will start by creating a stored procedure in Oracle to return a Cursor containing all Student’s data.

3.1 The Oracle Stored Procedure

ViewAllStudents.sql

CREATE OR REPLACE PROCEDURE studentAll(data out SYS_REFCURSOR)
AS
BEGIN
OPEN data FOR SELECT SID,FNAME,LNAME,DEPT,YEAR,EMAIL FROM STUDENT;
END;
/

3.2 The Java Code

JPAcreateQueryCursor.java

package com.javacodegeeks.examples.jpa.service;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;

import javax.mail.Session;
import javax.persistence.*;

import org.eclipse.persistence.*;
import org.eclipse.persistence.internal.helper.DatabaseType;
import org.eclipse.persistence.internal.sessions.ArrayRecord;
import org.eclipse.persistence.jpa.JpaEntityManager;
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes;
import org.eclipse.persistence.platform.database.oracle.plsql.OraclePLSQLTypes;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall;
import org.eclipse.persistence.platform.database.oracle.plsql.PLSQLrecord;
import org.eclipse.persistence.queries.DataReadQuery;
import org.eclipse.persistence.queries.ReadAllQuery;
import org.eclipse.persistence.queries.StoredProcedureCall;

import com.javacodegeeks.examples.jpa.entity.Student;
import com.sun.javafx.scene.traversal.Direction;

/**
 * @author Rivu
 *
 */
public class JPAcreateQueryCursor {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "RivuChk_JPA" );
		EntityManager entitymanager =  emfactory.createEntityManager();
			
		//initialising a StoredProcedureCall object
		StoredProcedureCall call = new StoredProcedureCall();
		//Setting The Procedure Name
		call.setProcedureName("studentAll");
		
		//Defining The Out Cursor Parameters
		call.useNamedCursorOutputAsResultSet("data");
		
		//Initialising a DataReadQuery
		DataReadQuery databaseQuery = new DataReadQuery();
		//Setting Call		
		databaseQuery.setCall(call);
		
		 //Initialising and Executing The Call and Getting the Result as List
		Query query = ((JpaEntityManager)entitymanager.getDelegate()).createQuery(databaseQuery);
		List students = query.getResultList();
		
		//Creating Iterator for the List
		Iterator i=students.iterator();
		
		//Iterating Through
		while(i.hasNext())
		{
			ArrayRecord ar=(ArrayRecord) i.next();
			System.out.println(ar.values());
		}
	}

}

3.3 Output

[1, Esha, Dey, Comp. Sc., 3, esha.dey]
[2, Rivu, Chk, Comp. Sc., 4, rivuchk.tk]
[3, Raj, Roy, Electrical, 4, rroy@in.com]
[4, Ilias, Tsagkills, Comp. Sc., 4, ilias@jcg]
[5, Byron, Kiourtzogl, Comp. Sc., 4, byron@jcg]
[6, Rajdeep, Samanta, Commerce, 2, rajEgmail]
[7, Nandan, Banerjee, Comp. Sc., 4, hellonandan]
[8, Nikos, Maravitsas, CSE, 4, nikos@jcg]

3.4 Explanation

In this Example at first I have created and initialised a StoredProcedureCall Object, then specified it’s name and parameters, after that I have initialised the DataReadQuery and set the call, then by using the createQuery() we created the query to execute. The getResultList() method executes a query and return the query results as a List. Then we just looped through the list to print all students data.

4. Using named query

The use of @NamedStoredProcedureQuery Annotation and createNamedQuery() makes invoking Stored Procedure through JPA a lot more simple and easy. We would use the same Stored Procedure as in the section 3.1.

So let us start by adding some changes to the Student POJO class.

4.1 The POJO Class with @NamedStoredProcedureQuery annotation

Student.java

package com.javacodegeeks.examples.jpa.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

import org.eclipse.persistence.annotations.*;
import org.eclipse.persistence.*;


@NamedStoredProcedureQuery(name="studentAll", procedureName="studenyByDept", resultClass=Student.class, parameters={
	@StoredProcedureParameter(queryParameter="data", name="data", direction=Direction.OUT_CURSOR)})
@Entity
public class Student {
	 @Id
	 @GeneratedValue(strategy= GenerationType.AUTO)
	 private int sid;
	 private String fname;
	 private String lname;
	 private String dept;
	 private int year;
	 private String email;
	
	public Student() {
		// TODO Auto-generated constructor stub
	}
		
	public Student(int sid, String fname, String lname, String dept, int year,
			String email) {
		super();
		this.sid = sid;
		this.fname = fname;
		this.lname = lname;
		this.dept = dept;
		this.year = year;
		this.email = email;
	}

	public int getSid() {
		return sid;
	}

	public void setSid(int sid) {
		this.sid = sid;
	}

	public String getFname() {
		return fname;
	}

	public void setFname(String fname) {
		this.fname = fname;
	}

	public String getLname() {
		return lname;
	}

	public void setLname(String lname) {
		this.lname = lname;
	}

	public String getDept() {
		return dept;
	}

	public void setDept(String dept) {
		this.dept = dept;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	@Override
	public String toString() {
		return "Student [sid=" + sid + ", fname=" + fname + ", lname=" + lname
				+ ", dept=" + dept + ", year=" + year + ", email=" + email
				+ "]";
	}
	
}

4.2 The Service Class with createNamedQuery()

JPAcreateNamedQuery.java

Want to be a JPA Master ?

Subscribe to our newsletter and download the JPA Ultimate Guide right now!

In order to help you master programming with JPA, we have compiled a kick-ass guide with all the major JPA features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

package com.javacodegeeks.examples.jpa.service;

import java.util.Iterator;
import java.util.List;

import javax.persistence.*;
import javax.management.Query;

import org.eclipse.persistence.internal.sessions.ArrayRecord;
import org.eclipse.persistence.jpa.JpaEntityManager;

import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import org.eclipse.persistence.*;

import com.javacodegeeks.examples.jpa.entity.Student;

/**
 * @author Rivu
 *
 */
public class JPAcreateNamedQuery {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "RivuChk_JPA" );
		EntityManager entitymanager =  emfactory.createEntityManager();
		
		javax.persistence.Query query =  entitymanager.createNamedQuery("studentAll");
		List students =  query.getResultList();
		
		System.out.println("The Students are:");
		//Looping Through the Resultant list
		for(Student student:students)
		{
			System.out.println(student.toString());
		}
	}
}

4.3 Output

The Students are:
Student [sid=1, fname=Esha, lname=Dey, dept=Comp. Sc., year=3, email=esha.dey]
Student [sid=2, fname=Rivu, lname=Chk, dept=Comp. Sc., year=4, email=rivuchk.tk]
Student [sid=3, fname=Raj, lname=Roy, dept=Electrical, year=4, email=rroy@in.com]
Student [sid=4, fname=Ilias, lname=Tsagkills, dept=Comp. Sc., year=4, email=ilias@jcg]
Student [sid=5, fname=Byron, lname=Kiourtzogl, dept=Comp. Sc., year=4, email=byron@jcg]
Student [sid=6, fname=Rajdeep, lname=Samanta, dept=Commerce, year=2, email=rajEgmail]
Student [sid=7, fname=Nandan, lname=Banerjee, dept=Comp. Sc., year=4, email=hellonandan]
Student [sid=8, fname=Raj, lname=Chowdhury, dept=CSE, year=4, email=raj.c@abcd.com]

4.4 Explanation

First we have annotated a NamedStoredProcedureQuery in the Student POJO class, this annotation defines the Stored Procedure name with the parameter names and types. Then in the service class JPAcreateNamedQuery.java we have used the method createNamedQuery(String name) to create a query object by the name provided.

5. With Multiple Parameters (IN and OUT)

What about if the Stored Procedure returns more than one parameters, or both IN and OUT parameters ? We can easily perform it as previous examples just have to make some minor changes, as follows.

5.1 Oracle Stored Procedure

Firstly we are creating another Stored Procedure that will take the Student Id as the IN parameter and then will return the Student’s Full name and Department as OUT parameters.

studentById.sql

CREATE OR REPLACE PROCEDURE studentById(id in number,sname out varchar2,dept out varchar2)
AS
BEGIN
select fname||' '||lname,dept into sname,dept from student where sid=id;
END;
/

5.2 The Service Class

Now it is the time to call the stored procedure from JPA Service Class. Here we will use the createQuery() method to make the query.

JpaINParam.java

package com.javacodegeeks.examples.jpa.service;


import java.util.Scanner;
import org.eclipse.persistence.sessions.Session;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.ParameterMode;
import javax.persistence.Persistence;
import javax.persistence.StoredProcedureQuery;

import org.eclipse.persistence.jpa.JpaEntityManager;


import com.javacodegeeks.examples.jpa.entity.Student;

public class JpaINParam {

	public static void main(String[] args) {
		Scanner sc=new Scanner(System.in);
		EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "RivuChk_JPA" );
		EntityManager entitymanager =  emfactory.createEntityManager();
		
		
		
		System.out.println("Enter ID to View");
		int id=sc.nextInt();
		
		StoredProcedureQuery storedProcedure = entitymanager.createStoredProcedureQuery("studentById");
		// set parameters
		storedProcedure.registerStoredProcedureParameter("id", Integer.class, ParameterMode.IN);
		storedProcedure.registerStoredProcedureParameter("sname", String.class, ParameterMode.OUT);
		storedProcedure.registerStoredProcedureParameter("dept", String.class, ParameterMode.OUT);
		storedProcedure.setParameter("id", id);
		// execute SP
		storedProcedure.execute();
		
		String name=storedProcedure.getOutputParameterValue("sname").toString();
		String dept=storedProcedure.getOutputParameterValue("dept").toString();
			
		System.out.println("Name : "+name);
		System.out.println("Department : "+dept);	
	}

}

Output

Enter ID to View
1
[EL Fine]: sql: 2015-01-21 03:19:33.238--ServerSession(559670971)--Connection(1613912455)--Thread(Thread[main,5,main])--BEGIN studentById(id=>?, sname=>?, dept=>?); END;
	bind => [1, => sname, => dept]
Name : Esha Dey
Department : Comp. Sc.

5.3 Explanation

In this example after creating the StoredProcedureQuery object using the createQuery() method the first thing I did is to register all the parameters using the registerStoredProcedureParameter(...). Then we assigned value to the parameter id using the method setParameter(...). After that I executed the query with the storedProcedure.execute() method and then fetched value of each output parameter using the storedProcedure.getOutputParameterValue(...).

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.