Create and Call Procedure using Spring Boot with JPA

Create and Call Stored Procedure in Spring Boot with JPA (2026 Guide)

Create and Call Stored Procedure in Spring Boot with JPA

In this tutorial, you will learn how to create MySQL stored procedures and call them from a Spring Boot application using JPA's @NamedStoredProcedureQuery and EntityManager.

You will build two procedures — one to insert a student and one to fetch all students — then expose them through REST endpoints backed by Spring Data JPA. This approach keeps complex SQL logic inside the database while still using the familiar Spring Boot programming model.

Project Structure

The Spring Boot project follows a clean layered package structure:

Spring Boot JPA stored procedure project structure in Eclipse IDE

Creating Stored Procedures in MySQL

First, create the database and both stored procedures directly in your MySQL client. The DELIMITER is required because the procedure body contains semicolons. 

mysql> create database students;

mysql> use students;

# Procedure 1: Insert a student
mysql> DELIMITER $$
mysql> create procedure addStudent(IN id int, IN name varchar(35), IN marks float)
    -> BEGIN
    -> insert into student values(id, marks, name);
    -> END $$
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;

# Procedure 2: Fetch all students
mysql> DELIMITER $$
mysql> create procedure fetchStudents()
    -> BEGIN
    -> select * from student;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

Why DELIMITER is required: The MySQL client uses ; to end statements. Since the procedure body itself contains ;, we temporarily change the delimiter to $$ so the entire procedure is treated as one statement.

ProcedureTutorialApplication.java

Standard Spring Boot entry point.

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ProcedureTutorialApplication {

    public static void main(String[] args) {
        SpringApplication.run(ProcedureTutorialApplication.class, args);
    }
}

application.properties

server.port=8081
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.url=jdbc:mysql://localhost:3306/students

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

Note: In modern Spring Boot (2.4+), the MySQL driver is auto-detected. If you must specify it, use com.mysql.cj.jdbc.Driver (the old com.mysql.jdbc.Driver is deprecated).

StudentController.java

Two REST endpoints are exposed — one to add a student (via the addStudent procedure) and one to fetch all students (via fetchStudents). 

package com.example.demo.controller;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.example.demo.model.Student;
import com.example.demo.service.StudentService;

@RestController
public class StudentController {

    @Autowired
    private StudentService studentService;

    @PostMapping("/addstudent")
    public Integer addStudent(@RequestBody Student student) {
        return studentService.addStudent(student);
    }

    @GetMapping("/students")
    public List<Student> fetchStudents() {
        return studentService.fetchStudents();
    }
}

Student.java (Entity + @NamedStoredProcedureQuery)

The entity maps to the student table and declares both stored procedures using @NamedStoredProcedureQueries. Each parameter is declared with @StoredProcedureParameter and a ParameterMode. 

package com.example.demo.model;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedStoredProcedureQueries;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureParameter;

@Entity
@NamedStoredProcedureQueries({

    @NamedStoredProcedureQuery(
        name = "addStudent",
        procedureName = "addStudent",
        parameters = {
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "id", type = Integer.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "name", type = String.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "marks", type = Float.class)
        }
    ),

    @NamedStoredProcedureQuery(
        name = "fetchStudents",
        procedureName = "fetchStudents"
    )
})
public class Student {

    @Id
    private int id;
    private String name;
    private float marks;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getMarks() {
        return marks;
    }

    public void setMarks(float marks) {
        this.marks = marks;
    }
}

Key annotations:

  • @NamedStoredProcedureQueries — container for multiple procedures on the same entity.
  • @NamedStoredProcedureQuery(name, procedureName) — maps a Java name to the DB procedure.
  • @StoredProcedureParameter(mode, name, type) — declares each parameter. Must match the procedure's parameter names.
  • ParameterMode.IN — input parameter (there are also OUT and INOUT).

StudentRepository.java

The repository extends JpaRepository. In this project, it is used mainly for Hibernate's DDL generation; the actual procedure calls go through EntityManager. 

package com.example.demo.repo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.example.demo.model.Student;

@Repository
public interface StudentRepository extends JpaRepository<Student, Integer> {
}

StudentService.java

The service injects EntityManager and calls the named procedures. Both methods are annotated with @Transactional — without it, JPA throws TransactionRequiredException. 

package com.example.demo.service;

import java.util.List;

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

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.example.demo.model.Student;

@Service
public class StudentService {

    @PersistenceContext
    private EntityManager entityManager;

    @Transactional
    public int addStudent(Student student) {
        return entityManager.createNamedStoredProcedureQuery("addStudent")
                .setParameter("id", student.getId())
                .setParameter("name", student.getName())
                .setParameter("marks", student.getMarks())
                .executeUpdate();
    }

    @Transactional
    @SuppressWarnings("unchecked")
    public List<Student> fetchStudents() {
        return entityManager.createNamedStoredProcedureQuery("fetchStudents")
                .getResultList();
    }
}

executeUpdate() vs getResultList():

  • executeUpdate() — used for procedures that INSERT, UPDATE, or DELETE; returns the affected row count.
  • getResultList() — used for procedures that SELECT; returns a List of mapped entities.

Note: @Autowired is not needed on EntityManager when @PersistenceContext is used — the container injects it correctly. Using both can cause issues on some application servers.

pom.xml Dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

spring-boot-starter-data-jpa brings in Hibernate and EntityManager support. spring-boot-starter-web provides the REST layer. The MySQL connector is needed at runtime to talk to the database.

Output Screens

Below are the output screenshots from testing the endpoints:

Spring Boot add student via stored procedure — Postman output
Spring Boot fetch students via stored procedure — Postman output

How It Works (Step-by-Step)

  1. Create the procedures in MySQL — addStudent inserts a row; fetchStudents returns all rows. The DELIMITER change is required because the body contains semicolons.
  2. Declare them in the entity — @NamedStoredProcedureQueries maps the Java names to the DB procedures and declares each parameter.
  3. Inject EntityManager in the service — Spring Boot creates an EntityManager and injects it using @PersistenceContext.
  4. Call the procedure — entityManager.createNamedStoredProcedureQuery("addStudent") plus setParameter() calls, then executeUpdate() or getResultList().
  5. Transaction required — @Transactional ensures the persistence context has an active transaction while executing the procedure.
  6. REST layer — the controller exposes both operations as POST /addstudent and GET /students.

Customization Ideas

  • Use ParameterMode.OUT to return the inserted row's ID from the procedure.
  • Add pagination parameters to fetchStudents (IN start, IN size).
  • Call a procedure that returns a scalar count using getSingleResult().
  • Add a second procedure to update or delete a student.
  • Test the procedure directly in MySQL Workbench using CALL addStudent(1, 'Deep', 88.5);.

Frequently Asked Questions

How do I call a stored procedure in Spring Boot with JPA?

Declare the procedure using @NamedStoredProcedureQuery on the entity class. Then inject EntityManager and call entityManager.createNamedStoredProcedureQuery(name).setParameter(...).executeUpdate() for INSERT/UPDATE procedures, or .getResultList() for SELECT procedures.

What is @NamedStoredProcedureQuery in JPA?

@NamedStoredProcedureQuery maps a Java name to a database stored procedure. It is placed on an @Entity class and can be repeated using @NamedStoredProcedureQueries. It defines the procedure name and its parameters via @StoredProcedureParameter.

What is the difference between executeUpdate() and getResultList() for procedures?

Use executeUpdate() when the procedure performs INSERT, UPDATE, or DELETE operations and returns the affected row count. Use getResultList() when the procedure performs SELECT and returns rows that should be mapped to entity objects.

Why do I need @Transactional when calling stored procedures?

Spring Boot requires @Transactional on the service method to wrap the procedure call in a transaction. Without it, EntityManager operations throw TransactionRequiredException because the persistence context needs an active transaction to execute queries.

How do I create a stored procedure in MySQL?

Use DELIMITER $$ to change the statement terminator, then CREATE PROCEDURE procedureName(IN param type, ...) BEGIN ... END $$, and finally reset the delimiter with DELIMITER ;. Using DELIMITER is required because the procedure body contains semicolons.

Conclusion

In this tutorial, you learned how to create MySQL stored procedures and call them from Spring Boot using JPA's @NamedStoredProcedureQuery and EntityManager. You also learned why @Transactional is required and how to choose between executeUpdate() and getResultList().

Stored procedures are useful when you need to encapsulate complex SQL logic inside the database — for example, multi-table inserts, reporting queries, or batch operations. Keep business logic in Java where possible, but leverage procedures for performance-critical database operations.

Related topics: Spring Data JPA @Procedure Annotation, OUT Parameters in Stored Procedures, Native Queries vs Stored Procedures, MySQL Cursors and Loops, Transactional Propagation in Spring

Tags:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)