Calling Stored Procedures with JPA and Spring Data JPA: A Practical Guide

When working with relational databases, stored procedures are a common way to encapsulate business logic at the database level. In Java applications using JPA (Java Persistence API), calling stored procedures has evolved over the years. In this post, we’ll walk through how to call stored procedures using older JPA versions, the improvements introduced in JPA 2.1/2.2, and how to leverage Spring Data JPA for a more declarative approach.

Why Use Stored Procedures?

Stored procedures are precompiled SQL statements stored in the database. They offer:

• Performance: Reduced network traffic and better execution plans.
• Security: Access control at the database level.
• Reusability: Shared logic across applications and systems.

Calling Stored Procedures with Old JPA Versions (Before JPA 2.1)

Before JPA 2.1, calling stored procedures was somewhat verbose and less intuitive. Developers typically relied on the EntityManager‘s createNativeQuery method.

Example:

Suppose we have a stored procedure in MySQL:

CREATE PROCEDURE GET_EMPLOYEE_BY_ID(IN emp_id INT)
BEGIN
    SELECT * FROM employee WHERE id = emp_id;
END;

You could call this procedure in JPA as follows:

Query query = entityManager.createNativeQuery("CALL GET_EMPLOYEE_BY_ID(?)", Employee.class);
query.setParameter(1, 1001);

List<Employee> result = query.getResultList();

This approach works but lacks type safety and flexibility.

Improvements in JPA 2.1 and Beyond

With JPA 2.1, support for stored procedures became part of the specification via the StoredProcedureQuery API.

Using StoredProcedureQuery

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("GET_EMPLOYEE_BY_ID", Employee.class)
    .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
    .setParameter(1, 1001);

List<Employee> result = query.getResultList();

Optional: Mapping Output Parameters

For procedures with OUT parameters, you can register and retrieve them like so:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("GET_EMPLOYEE_SALARY")
    .registerStoredProcedureParameter("emp_id", Integer.class, ParameterMode.IN)
    .registerStoredProcedureParameter("salary", Double.class, ParameterMode.OUT)
    .setParameter("emp_id", 1001);

Double salary = (Double) query.getOutputParameterValue("salary");

JPA 2.2 and 2.3 largely retain the same API, with minor enhancements to integration with Java 8 features like Optional.

Using Stored Procedures with Spring Data JPA

Spring Data JPA simplifies data access layers even further by allowing you to define stored procedure calls through repository interfaces.

Setup Example

First, define your stored procedure:

CREATE PROCEDURE GET_TOTAL_EMPLOYEES(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employee;
END;

Then, declare a method in your repository, and use the annotation @Procedure and indicate the attribute name with the name of the store procedure:

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Procedure(name = "getTotalEmployees")
    Integer getTotalEmployees();
}

If your procedure is associated with an entity, annotate it:

@Entity
@NamedStoredProcedureQuery(
    name = "getTotalEmployees",
    procedureName = "GET_TOTAL_EMPLOYEES",
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "total", type = Integer.class)
    }
)
public class Employee {
    @Id
    private Long id;
    private String name;
}

Bonus: IN/OUT Parameters

You can use @Procedure with input/output arguments:

@Procedure(procedureName = "GET_EMPLOYEE_SALARY")
Double getEmployeeSalary(@Param("emp_id") Integer empId);

Spring handles the binding of parameters behind the scenes, making your code cleaner and more declarative.

Summary

Conclusion

Stored procedures remain a valuable tool in enterprise applications. Whether you’re stuck with legacy JPA or using the latest Spring stack, you have several ways to integrate them cleanly and effectively. Always balance between readability, reusability, and control when choosing your approach.