How Spring Boot Makes Calling PostgreSQL Stored Procedures with REF CURSOR Easy

In one of my project, I was working on a requirement which had us call a stored procedure. This was a legacy system and had a lot of business logic and intricacies involved. The requirement state was final and we had to come up with a solution to make a call to stored procedure which either returned a cursor or a cursor with some scalar values(String, Integer etc) or multiple cursor and combinations.
For this, when I went through google and spring docs, I found a solution involving mainly @NamedStoredProcedureQuery and @Procedure and their combination together got me the solution I was yearning for.
In this blog I have given an example of how this was achieved and have also added my other findings.

Note: For all of my testing and R&D, I did use Spring boot 3.4.X and along with that Postgres 17.

The PostgreSQL Stored Procedures

Before we dive into Spring Boot, let’s first set up the PostgreSQL stored procedures. These procedures will help us practice calling a REF CURSOR.

Stored Procedure 1: Returning a REF CURSOR

sql
CREATE OR REPLACE PROCEDURE get_employees_ref_cursor(out emp_cursor refcursor)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN emp_cursor FOR SELECT * FROM employees;
END;
$$;

This stored procedure opens a REF CURSOR for the employees table, allowing us to retrieve the result set from a REF CURSOR.

Stored Procedure 2: Returning Both REF CURSOR and Scalar Value

sql
CREATE OR REPLACE PROCEDURE get_employee_count_and_cursor(out emp_count integer, out emp_cursor refcursor)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO emp_count FROM employees;
    OPEN emp_cursor FOR SELECT * FROM employees;
END;
$$;

In this second procedure, we not only return a REF CURSOR but also a scalar value, emp_count, which is the total number of employees in the database.

Spring Boot Setup

Now that we have the stored procedures in place, let’s set up our Spring Boot application to call these procedures. We’ll utilize @NamedStoredProcedureQuery and @Procedure annotations in Spring Boot.

Step 1: Define @NamedStoredProcedureQuery in the Entity

The @NamedStoredProcedureQuery annotation is used to define the stored procedure and map its results. In our case, we define the procedure in the Employee entity.

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

@Entity
@NamedStoredProcedureQuery(
    name = "getEmployeesFromCursor",
    procedureName = "get_employees_ref_cursor",
    resultClasses = Employee.class,
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "emp_cursor", type = Class.class)
    }
)
@NamedStoredProcedureQuery(
    name = "getEmployeeCountAndCursor",
    procedureName = "get_employee_count_and_cursor",
    resultClasses = Employee.class,
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "emp_count", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "emp_cursor", type = Class.class)
    }
)
public class Employee {
    @Id
    private Long id;
    private String name;
    private String department;

    // Getters and Setters
}

In the Employee entity:

• First Procedure (get_employees_ref_cursor): Returns a REF CURSOR.
• Second Procedure (get_employee_count_and_cursor): Returns both a scalar value (emp_count) and a REF CURSOR.

Step 2: Call Stored Procedures Using @Procedure in Repository

In the repository, we’ll use the @Procedure annotation to call the stored procedures and return values from them.

java
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    // Call the procedure that returns a REF CURSOR
    @Procedure(name = "getEmployeesFromCursor")
    List<Employee> callGetEmployeesRefCursor();

    // Call the procedure that returns both a REF CURSOR and a scalar value (e.g., count)
    @Procedure(name = "getEmployeeCountAndCursor")
    Map<String, Object> callGetEmployeeCountAndCursor();
}

The @Procedure annotation allows us to map the procedure's results directly into Java objects.

Step 3: Use the Repository in the Service

In the service layer, we’ll use the repository to call the stored procedures and process the results.

java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmployeeService {

    @Autowired
    private EmployeeRepository employeeRepository;

    // Get employees from the stored procedure that returns a REF CURSOR
    public List<Employee> getEmployeesFromProcedure() {
        return employeeRepository.callGetEmployeesRefCursor();
    }

    // Get employee count and cursor (scalar + REF CURSOR) from the stored procedure
    public Map<String, Object> getEmployeeCountAndCursorFromProcedure() {
        return employeeRepository.callGetEmployeeCountAndCursor();
    }
}

Step 4: Expose the Service via REST Controller

Finally, we expose the service via a REST controller to access these procedures.

java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class EmployeeController {

    @Autowired
    private EmployeeService employeeService;

    @GetMapping("/employees")
    public List<Employee> getEmployees() {
        return employeeService.getEmployeesFromProcedure();
    }

    @GetMapping("/employee-count")
    public Map<String, Object> getEmployeeCountAndCursor() {
        return employeeService.getEmployeeCountAndCursorFromProcedure();
    }
}

Conclusion

When working with cursors as return type, my suggestion would be to always use Class.class as type. In the java doc if you check for Cursor example they suggest using void.class, but I have seen in case of multiple cursors being returned, this does not work. Instead Class.class works for both single cursor and multiple cursor scenarios. Also when you use ResultClasses, for a single cursor it does do the Entity mapping and return you the Employee object but in case of multiple values where you use Map, it writens a ResultSet and thus it becomes out duty to properly map it.
Also do use Map in case of multiple values being returned by your stored procedure as shown in the example above, these multiple values would be a combination of multiple cursors only or just multiple scalar values or multiple scalars or single scalar with multiple cursor or single cursor.

NOTE: Make use of Map only in case of multiple values being sent. If a single cursor is sent use List<Employee> or List<Object[]> and if a single scalar value is being returned like a string, using String as the return type in your method return type defined in Repository class.

Do put up your valueable suggestion's in the comment section.

References

@NamedStoredProcedureQueries Javadoc
@StoredProcedureParameter Javadoc