skip to content

How do you call a database stored procedure from a Spring Data JPA repository?

level: juniorimportance: should knowfreq 35%

answer

  1. @Procedure on repo method
  2. value/procedureName = DB name, name = NamedStoredProcedureQuery
  3. procedure must already exist in DB
  4. @NamedStoredProcedureQuery + @StoredProcedureParameter modes
  5. IN/OUT/INOUT/REF_CURSOR

basics

~10 s

Add a method to your repository and annotate it with @Procedure, giving the stored procedure's name. Spring Data JPA calls that procedure in the database and maps the result to the method's return type.

solid answer

~40 s

You put a method on a JpaRepository and annotate it with Spring Data's @Procedure. The simplest form is @Procedure("increase_salaries") or @Procedure(procedureName = "increase_salaries"), which names the actual database procedure. Method parameters become IN parameters (by position, or matched by name with @Param), and the return value maps to the procedure's OUT parameter or result. For richer mappings — multiple OUT parameters, REF_CURSOR, explicit modes — you declare a JPA @NamedStoredProcedureQuery on an entity and reference it by name from @Procedure(name = "..."). Under the hood Spring Data builds a JPA StoredProcedureQuery, so the procedure must already exist in the database (Spring does not create it). This is useful when business logic lives in the DB or you must reuse an existing procedure.

code

java · 22 lines
java
// Simple inline form
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Procedure(procedureName = "get_headcount_by_dept")
    Integer headcount(@Param("dept_id") Long deptId);
}

// Explicit mapping via JPA
@Entity
@NamedStoredProcedureQuery(
    name = "Employee.increaseSalaries",
    procedureName = "increase_salaries",
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN,  name = "dept_id", type = Long.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "updated", type = Integer.class)
    })
public class Employee { /* ... */ }

public interface EmployeeRepo2 extends JpaRepository<Employee, Long> {
    @Procedure(name = "Employee.increaseSalaries")
    Integer increaseSalaries(@Param("dept_id") Long deptId);
}

go deeper

for a junior

Know @Procedure exists and names a DB procedure the repository will call.

for a middle

Distinguish inline procedureName vs the @NamedStoredProcedureQuery/name route and bind params with @Param.

for a senior

Map multiple OUT params / REF_CURSOR via @StoredProcedureParameter modes and know a tx is needed for mutating calls.

for a principal

Weigh procedures vs application logic — testability, DB coupling, migration ownership — and decide when DB-resident logic is justified.

## What a stored procedure is A **stored procedure** is a named block of SQL/procedural code stored and executed inside the database engine (e.g. `CREATE PROCEDURE increase_salaries(...)`). Applications *call* it rather than sending ad-hoc SQL. Reasons to use one: logic that must stay in the DB, set-based bulk operations, or reusing procedures a DBA already maintains. ## `@Procedure` — the Spring Data JPA annotation `org.springframework.data.jpa.repository.query.Procedure` is placed on a repository method. It tells Spring Data to invoke a stored procedure instead of deriving a query. Its attributes: - **`value`** / **`procedureName`** — the *database* procedure name (these are aliases; `value` is the shorthand). Example: `@Procedure("increase_salaries")` or `@Procedure(procedureName = "increase_salaries")`. - **`name`** — references a JPA **`@NamedStoredProcedureQuery`** by its `name` (NOT the DB name). Use this when you need an explicit parameter mapping. - **`outputParameterName`** — the name of the OUT parameter whose value should be returned, when the procedure declares named OUT parameters. ## Two ways to map **1. Inline / by convention.** For a one-IN / one-OUT procedure you can skip the entity annotation: ```java @Procedure(procedureName = "get_employee_count_by_dept") Integer countByDepartment(@Param("dept_id") Long deptId); ``` Parameters are bound positionally, or by name when you use `@Param` and the JDBC driver/DB supports named parameters. **2. `@NamedStoredProcedureQuery`.** A JPA annotation (`jakarta.persistence.NamedStoredProcedureQuery`) placed on an `@Entity`. It declares `procedureName` (the DB name), a `name` (the logical name Spring references), and an array of **`@StoredProcedureParameter`** entries, each with a `mode` = `IN`, `OUT`, `INOUT`, or `REF_CURSOR`, plus `name`/`type`. The repository method then uses `@Procedure(name = "Employee.increaseSalaries")`. ## Key mechanics & gotchas - **The procedure must already exist** in the database — `@Procedure` calls it, it does not generate DDL. Missing procedure = runtime error. - Calls run inside a JPA `StoredProcedureQuery` (`EntityManager.createStoredProcedureQuery` / `createNamedStoredProcedureQuery`). - Like any modifying call, invoking a procedure that writes data needs a transaction; if the repository method mutates state you typically add `@Transactional`. - Parameter binding by name requires the DB/driver to support it; otherwise use positional order. - `REF_CURSOR` (returning a result set) is DB-specific (common on Oracle/PostgreSQL) and generally needs the `@NamedStoredProcedureQuery` form. ## When to use Reach for `@Procedure` when logic legitimately belongs in the database or you must integrate with existing procedures. For ordinary CRUD and queries, prefer derived queries or `@Query` — procedures are harder to test, tie you to the DB, and bypass much of JPA's mapping.

  • What is the difference between the `procedureName` and `name` attributes of @Procedure?
    `procedureName` (and its alias `value`) is the actual name of the procedure in the database. `name` instead points to a JPA @NamedStoredProcedureQuery declared on an entity, letting you define explicit parameter modes/types there.
  • Does @Procedure create the stored procedure for you?
    No. The procedure must already exist in the database (created via migration/DDL such as Liquibase). @Procedure only invokes it; a missing procedure fails at runtime.

saying these in an interview costs you the question

  • Thinking @Procedure generates/creates the procedure in the DB
  • Confusing name (logical NamedStoredProcedureQuery reference) with procedureName (DB name)
  • Believing you always need @NamedStoredProcedureQuery even for a simple one-IN/one-OUT call

context

resolving links…