Stored Procedures with JPA

Let's actually build the bank's month-end example, piece by piece, so this stops being abstract.

First, here's what the stored procedure itself might look like

Somewhere in the bank's PostgreSQL database, this already exists — written by a DBA, long before your Spring Boot service was ever a thought:

CREATE OR REPLACE PROCEDURE calculate_monthly_interest(
    IN p_month INT,
    IN p_year INT,
    OUT p_accounts_processed INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts
    SET balance = balance + (balance * interest_rate / 12)
    WHERE EXTRACT(MONTH FROM last_updated) = p_month
      AND EXTRACT(YEAR FROM last_updated) = p_year;

    GET DIAGNOSTICS p_accounts_processed = ROW_COUNT;
END;
$$;

Don't worry about mastering PL/pgSQL syntax here — just notice the shape of it. It takes in a month and a year (IN parameters — values you're handing it), does its work, and hands something back out (p_accounts_processed, an OUT parameter — a value it computed and is returning to you). This is exactly the "month-end closing" logic from our story, just written in real SQL.

Now, how do you call this from Java, using JPA?

The quickest way — good for a one-off call — is createStoredProcedureQuery, where you register each parameter's direction (IN, OUT, or INOUT) explicitly, by hand:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("calculate_monthly_interest");

query.registerStoredProcedureParameter("p_month", Integer.class, ParameterMode.IN);
query.registerStoredProcedureParameter("p_year", Integer.class, ParameterMode.IN);
query.registerStoredProcedureParameter("p_accounts_processed", Integer.class, ParameterMode.OUT);

query.setParameter("p_month", 3);
query.setParameter("p_year", 2026);

query.execute();

Integer processed = (Integer) query.getOutputParameterValue("p_accounts_processed");
System.out.println("Accounts updated: " + processed);

Read that top to bottom: you get a query object, you tell it exactly what parameters exist and which direction each one flows, you set the IN values, you run it, and then you pull the OUT value back out. Nothing magical — just JPA giving you a structured, Java-friendly way to do what a raw JDBC CallableStatement would otherwise make you do more manually.

The reusable version: @NamedStoredProcedureQuery

If this same procedure is going to be called from three or four different places in your codebase, writing out all that parameter registration every single time gets repetitive and error-prone. So JPA lets you declare it once, as an annotation on an entity class:

@Entity
@NamedStoredProcedureQuery(
    name = "Account.calculateMonthlyInterest",
    procedureName = "calculate_monthly_interest",
    parameters = {
        @StoredProcedureParameter(name = "p_month", mode = ParameterMode.IN, type = Integer.class),
        @StoredProcedureParameter(name = "p_year", mode = ParameterMode.IN, type = Integer.class),
        @StoredProcedureParameter(name = "p_accounts_processed", mode = ParameterMode.OUT, type = Integer.class)
    }
)
public class Account {
    // ... entity fields
}

Now, calling it anywhere else in your codebase is much shorter:

StoredProcedureQuery query = entityManager
    .createNamedStoredProcedureQuery("Account.calculateMonthlyInterest");
query.setParameter("p_month", 3);
query.setParameter("p_year", 2026);
query.execute();

Same procedure, same result — just declared once and reused by name, the same way a @NamedQuery works for JPQL.

What if the procedure returns rows, not just an OUT value?

Some procedures don't just return one number — they return an entire result set, like a SELECT would. For that case, JPA can map the returned rows straight into your entities or a DTO, using @SqlResultSetMapping, in much the same way a native query's result gets mapped (a tool you already saw two chapters ago). This is the one place where calling a procedure still asks you to do a little of the same explicit mapping work a native query would.