How do you map a stored procedure that returns a result set (REF_CURSOR) or multiple OUT parameters in Spring Data JPA?

seniornice to knowfreq 20%

answer

@NamedStoredProcedureQuery on entity
@StoredProcedureParameter mode = REF_CURSOR for result sets
resultClasses / resultSetMappings map rows
outputParameterName picks one OUT value
multiple OUT -> drop to StoredProcedureQuery API

basics

~10 s

Declare a JPA @NamedStoredProcedureQuery on an entity with @StoredProcedureParameter entries — use mode REF_CURSOR for a returned result set or several OUT parameters — then reference it from the repository via @Procedure(name = "...").

solid answer

~40 s

For anything beyond a single IN/OUT, you define a @NamedStoredProcedureQuery (a JPA annotation) on an @Entity. It sets procedureName (the DB name), a logical name, and a parameters array of @StoredProcedureParameter, each with a mode — IN, OUT, INOUT, or REF_CURSOR — plus name/type. REF_CURSOR models a procedure that returns a cursor/result set (common on Oracle and PostgreSQL); an optional resultClasses/resultSetMappings tells JPA how to map the rows. The repository method then uses @Procedure(name = "Employee.myProc"). When a procedure returns multiple OUT parameters, @Procedure(outputParameterName = "...") selects which one becomes the return value; if you need several, drop to the JPA StoredProcedureQuery API directly and read each named OUT parameter. Parameter ordering and REF_CURSOR support are DB/driver specific.

For anything beyond a single IN/OUT, you define a `@NamedStoredProcedureQuery` (a JPA annotation) on an `@Entity`. It sets `procedureName` (the DB name), a logical `name`, and a `parameters` array of `@StoredProcedureParameter`, each with a `mode` — `IN`, `OUT`, `INOUT`, or `REF_CURSOR` — plus `name`/`type`. `REF_CURSOR` models a procedure that returns a cursor/result set (common on Oracle and PostgreSQL); an optional `resultClasses`/`resultSetMappings` tells JPA how to map the rows. The repository method then uses `@Procedure(name = "Employee.myProc")`. When a procedure returns multiple OUT parameters, `@Procedure(outputParameterName = "...")` selects which one becomes the return value; if you need several, drop to the JPA `StoredProcedureQuery` API directly and read each named OUT parameter. Parameter ordering and REF_CURSOR support are DB/driver specific.

code

java · 25 lines

java

@Entity
@NamedStoredProcedureQuery(
    name = "Employee.findByDept",
    procedureName = "find_employees_by_dept",
    resultClasses = Employee.class,
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "dept_id", type = Long.class),
        @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class)
    })
public class Employee { /* ... */ }

public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    @Procedure(name = "Employee.findByDept")
    List<Employee> findByDept(@Param("dept_id") Long deptId);
}

// Multiple OUT params: use the JPA API directly
@Transactional
public Stats readStats(Long deptId) {
    var q = em.createNamedStoredProcedureQuery("Employee.stats");
    q.setParameter("dept_id", deptId);
    q.execute();
    return new Stats((Long) q.getOutputParameterValue("total"),
                     (BigDecimal) q.getOutputParameterValue("avg_salary"));
}

go deeper

for a junior

Know a result-set-returning procedure needs more than a plain @Procedure call.

for a middle

Declare @NamedStoredProcedureQuery with @StoredProcedureParameter and reference it via @Procedure(name=...).

for a senior

Map REF_CURSOR with resultClasses/resultSetMappings and read multiple OUT params via the JPA API.

for a principal

Judge DB portability (REF_CURSOR support), migration ownership, and whether procedure-resident result sets are worth the coupling.

The problem

@Procedure alone handles a simple call (some IN params, one OUT/result). When the procedure returns a result set (a cursor) or exposes multiple OUT parameters, you need explicit metadata — that's what @NamedStoredProcedureQuery provides.

`@NamedStoredProcedureQuery` (JPA)

jakarta.persistence.NamedStoredProcedureQuery, placed on an @Entity:

name — logical name Spring's @Procedure(name=...) references (convention: Entity.method).
procedureName — the real DB procedure name.
parameters — array of @StoredProcedureParameter.
resultClasses and/or resultSetMappings — how returned rows map to entities/DTOs.

`@StoredProcedureParameter` and modes

Each parameter declares:

mode (jakarta.persistence.ParameterMode):
- IN — input only.
- OUT — output only (single scalar result).
- INOUT — passed in and returned.
- REF_CURSOR — the procedure returns a cursor / result set. Widely used on Oracle and PostgreSQL; support and positioning are DB/driver specific.
name — parameter name (for named binding).
type — Java type.

REF_CURSOR mapping

A REF_CURSOR parameter yields rows, so you tell JPA how to turn rows into objects via resultClasses = Employee.class (map to an entity) or resultSetMappings (custom @SqlResultSetMapping for DTO/scalar projections). Example:

java

@NamedStoredProcedureQuery(
  name = "Employee.findByDept",
  procedureName = "find_employees_by_dept",
  resultClasses = Employee.class,
  parameters = {
    @StoredProcedureParameter(mode = ParameterMode.IN, name = "dept_id", type = Long.class),
    @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class)
  })

Repository:

java

@Procedure(name = "Employee.findByDept")
List<Employee> findByDept(@Param("dept_id") Long deptId);

Multiple OUT parameters

@Procedure returns a single value. If the procedure has several OUT params, use outputParameterName to pick one:

java

@Procedure(procedureName = "stats", outputParameterName = "total")
Long total(@Param("dept_id") Long id);

To read all of them, bypass the annotation and use the JPA API directly with an injected EntityManager:

java

StoredProcedureQuery q = em.createNamedStoredProcedureQuery("Employee.stats");
q.setParameter("dept_id", id);
q.execute();
Long total = (Long) q.getOutputParameterValue("total");
BigDecimal avg = (BigDecimal) q.getOutputParameterValue("avg_salary");

Gotchas

REF_CURSOR is not portable — MySQL historically doesn't support it; behavior differs by DB.
Named vs positional binding depends on driver support; some DBs require positional order matching the procedure signature.
The procedure must exist (via migration); annotations don't create it.
Mixing entity resultClasses with detached/read-only concerns still applies as with any JPA query.

When to use

Use the @NamedStoredProcedureQuery route whenever the call is non-trivial: result sets (REF_CURSOR), INOUT, or multiple OUT parameters. For a single IN→OUT scalar, inline @Procedure(procedureName=...) is enough.

## The problem `@Procedure` alone handles a simple call (some IN params, one OUT/result). When the procedure returns a **result set** (a cursor) or exposes **multiple OUT parameters**, you need explicit metadata — that's what `@NamedStoredProcedureQuery` provides. ## `@NamedStoredProcedureQuery` (JPA) `jakarta.persistence.NamedStoredProcedureQuery`, placed on an `@Entity`: - **`name`** — logical name Spring's `@Procedure(name=...)` references (convention: `Entity.method`). - **`procedureName`** — the real DB procedure name. - **`parameters`** — array of `@StoredProcedureParameter`. - **`resultClasses`** and/or **`resultSetMappings`** — how returned rows map to entities/DTOs. ## `@StoredProcedureParameter` and modes Each parameter declares: - **`mode`** (`jakarta.persistence.ParameterMode`): - `IN` — input only. - `OUT` — output only (single scalar result). - `INOUT` — passed in and returned. - `REF_CURSOR` — the procedure returns a **cursor / result set**. Widely used on **Oracle** and **PostgreSQL**; support and positioning are DB/driver specific. - **`name`** — parameter name (for named binding). - **`type`** — Java type. ## REF_CURSOR mapping A `REF_CURSOR` parameter yields rows, so you tell JPA how to turn rows into objects via `resultClasses = Employee.class` (map to an entity) or `resultSetMappings` (custom `@SqlResultSetMapping` for DTO/scalar projections). Example: ```java @NamedStoredProcedureQuery( name = "Employee.findByDept", procedureName = "find_employees_by_dept", resultClasses = Employee.class, parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "dept_id", type = Long.class), @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class) }) ``` Repository: ```java @Procedure(name = "Employee.findByDept") List<Employee> findByDept(@Param("dept_id") Long deptId); ``` ## Multiple OUT parameters `@Procedure` returns a single value. If the procedure has several OUT params, use `outputParameterName` to pick one: ```java @Procedure(procedureName = "stats", outputParameterName = "total") Long total(@Param("dept_id") Long id); ``` To read **all** of them, bypass the annotation and use the JPA API directly with an injected `EntityManager`: ```java StoredProcedureQuery q = em.createNamedStoredProcedureQuery("Employee.stats"); q.setParameter("dept_id", id); q.execute(); Long total = (Long) q.getOutputParameterValue("total"); BigDecimal avg = (BigDecimal) q.getOutputParameterValue("avg_salary"); ``` ## Gotchas - REF_CURSOR is **not portable** — MySQL historically doesn't support it; behavior differs by DB. - Named vs positional binding depends on driver support; some DBs require positional order matching the procedure signature. - The procedure must exist (via migration); annotations don't create it. - Mixing entity `resultClasses` with detached/read-only concerns still applies as with any JPA query. ## When to use Use the `@NamedStoredProcedureQuery` route whenever the call is non-trivial: result sets (REF_CURSOR), INOUT, or multiple OUT parameters. For a single IN→OUT scalar, inline `@Procedure(procedureName=...)` is enough.

Which ParameterMode models a procedure that returns a result set, and on which databases is it common?
REF_CURSOR. It's common on Oracle and PostgreSQL; support is DB/driver specific and, for example, MySQL historically does not support it.
How do you read several OUT parameters when @Procedure only returns one value?
Bypass @Procedure and use the JPA StoredProcedureQuery API (em.createNamedStoredProcedureQuery, execute(), then getOutputParameterValue(name) for each OUT parameter).

saying these in an interview costs you the question

Assuming REF_CURSOR works uniformly on all databases (it doesn't; e.g. MySQL)
Trying to get multiple OUT parameters directly from a single @Procedure return value
Forgetting resultClasses/resultSetMappings so REF_CURSOR rows can't be mapped
Thinking @NamedStoredProcedureQuery creates the procedure

How do you map a stored procedure that returns a result set (REF_CURSOR) or multiple OUT parameters in Spring Data JPA?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~2 min

The problem

@NamedStoredProcedureQuery (JPA)

@StoredProcedureParameter and modes

REF_CURSOR mapping

Multiple OUT parameters

Gotchas

When to use

▾common follow-ups2 questions

▾red flags4 flags

§ context

▾related questions4 questions

▾related topics3 topics

The problem

@NamedStoredProcedureQuery (JPA)

@StoredProcedureParameter and modes

REF_CURSOR mapping

Multiple OUT parameters

Gotchas

When to use

answer

go deeper

`@NamedStoredProcedureQuery` (JPA)

`@StoredProcedureParameter` and modes

context

`@NamedStoredProcedureQuery` (JPA)

`@StoredProcedureParameter` and modes