The JPA version 2.1, supports stored procedure calls that are defined in the database. It provides an annotation @NamedStoredProcedureQuery, that can be used to call the stored procedure.

In this article, we will learn how to call stored procedures using spring boot JPA. We will create a simple procedure and call the procedure using JPA’s @NamedStoredProcedureQuery annotation.

Following are the version details used in this example:

  • Spring boot version 2.2.3
  • Java version 1.8
  • Spring data jpa 2.2.4

Let’s begin.

Set up the database

We will create an Employee table to store the employee details. We will also create a database sequence called employee_id_sequence for generating the primary key.

CREATE SEQUENCE public.employee_id_seq INCREMENT 1 MINVALUE 1;

CREATE TABLE public.employee
(
  id bigint NOT NULL DEFAULT nextval('employee_id_seq'::regclass),
  designation character varying(255),
  employee_code character varying(255),
  employee_name character varying(255),
  CONSTRAINT employee_pkey PRIMARY KEY (id)
);
INSERT INTO public.employee(designation, employee_code, employee_name)
VALUES ('Software Developer', 'ECN007', 'Arun');

Create a procedure called get_employee_details as shown below. This procedure will return the employee record based on the name of the employee.

CREATE OR REPLACE FUNCTION public.get_empoyee_details(name text)
  RETURNS employee AS
'select e from employee e where e.employee_name = name;'
  LANGUAGE sql VOLATILE

Calling the procedure from JPA

Create an entity class called Employee.java. Add the following content.

package com.asb.example.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureParameter;

import lombok.Data;
import lombok.ToString;

@Data
@Entity
@ToString
@NamedStoredProcedureQuery(name = "get_empoyee_details", procedureName = "get_empoyee_details", resultClasses = {
		Employee.class }, parameters = {
				@StoredProcedureParameter(name = "emp_name", mode = ParameterMode.IN, type = String.class) })
public class Employee {

	@Id
	@GeneratedValue
	private Long id;

	@Column(name = "designation")
	private String designation;

	@Column(name = "employee_name")
	private String employeeName;

	@Column(name = "employee_code")
	private String employeeCode;
}

The annotations used to call the database stored procedure are:

@NamedStoredProcedureQuery

This annotation is used to call the stored procedure of the database. The following are a few of the parameters supported by this annotation.

  • name: The name used by the JPA entity manager.
  • procedureName: The name of the procedure created in the database.
  • resultClasses: List of classes that can be returned from the stored procedure.
  • parameters: List of input and output procedure parameters.

@StoredProcedureParameter

This annotation is used to define the input/output parameters of the stored procedure. The following are a few of the parameters supported by this annotation.

  • name: The name of the parameter.
  • mode: The mode of the parameter. IN/OUT,etc.
  • type: Type of the parameter.(Ex: String.class, etc)

Create a EmployeeController.java class

package com.asb.example.controller;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.asb.example.model.Employee;

@RestController
public class EmployeeController {

	@PersistenceContext
	private EntityManager entityManager;
	
	@SuppressWarnings("unchecked")
	@GetMapping("/emp-dtls")
	public ResponseEntity<List<Employee>> getEmp(@RequestParam(name = "name") String name) {
		List<Employee> employees = entityManager.createNamedStoredProcedureQuery("get_empoyee_details")
				.setParameter("emp_name", name).getResultList();
		return new ResponseEntity<>(employees, HttpStatus.OK);
	}
}

In this controller class, we are using Entity Manager instance to call the defined named procedure by calling the createNamedStoredProcedureQuery method. The name of the procedure passed here should match the procedure name defined in the entity class.

Time to run the application

Run the spring boot application and hit the endpoint /emp-dtls with the request parameter name as shown below.

Congratulations! 🙂 We have successfully invoked the stored procedure using JPA.

Conclusion

In this article, we learned how to call stored procedure while by using spring data JPA.

Sample code is available on GitHub. Happy coding! 🙂

If you like this content, please subscribe below to get the latest updates. 🙂

Processing…
Success! You're on the list.