Calling Stored Procedure – Spring Boot JPA

The JPA version 2.1, supports stored procedure calls that are defined in the database. It also provides an annotation @NamedStoredProcedureQuery, that we can use in the spring boot application to call the stored procedure.

In this article, we will learn how to call the stored procedure using spring boot JPA.

We will also create a simple procedure and call the procedure using JPA’s @NamedStoredProcedureQuery annotation.

Version details:

  • 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 the 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 the get_employee_details as shown below.

Also, 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. Add the below 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

We can use this annotation to call the stored procedure of the database.

Also, below 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 the stores procedure can return as return type.
  • parameters: List of input and output procedure parameters.

@StoredProcedureParameter

We can use this annotation to define the input/output parameters of the stored procedure.

The below 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 an EmployeeController 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 the Entity Manager instance to call the defined named procedure by calling the createNamedStoredProcedureQuery method.

Also, 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.

Finally, we get the response from the endpoint as shown in the above image.

Conclusion

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

Also, the sample code is available on GitHub.