Calling Stored Procedures from Spring Data JPA: The Real Deal

If you've been working with Spring Data JPA for a while, you've probably hit the wall where simple CRUD operations aren't enough. Maybe you need to execute a complex financial calculation that's already implemented as a stored procedure in your Oracle database, or you're migrating a legacy system where business logic lives in SQL Server stored procedures. I've been there—debugging a production outage at 3 AM because a stored procedure call returned unexpected results. In this article, I'll show you how to call stored procedures from Spring Data JPA repositories the right way, with the gotchas that only come from years of experience. We'll cover the @Procedure annotation, EntityManager-based calls, handling multiple result sets, and most importantly, what the official documentation conveniently leaves out. By the end, you'll be able to integrate stored procedures into your Spring Boot application without the pain.

The Basics: @Procedure Annotation

Spring Data JPA provides the @Procedure annotation to call stored procedures directly from repository interfaces. This is the cleanest approach when your procedure returns a single result set or a simple value. Here's the real deal: the annotation has three ways to specify the procedure name: implicit mapping (method name), explicit procedureName, and the value attribute (alias for procedureName). I've seen teams get burned by relying on implicit mapping because Hibernate's naming strategy might not match the database's case sensitivity. Always use explicit procedureName.

Let's look at a realistic example. Suppose we have a payment reconciliation system with a stored procedure that calculates daily totals:

public interface PaymentRepository extends JpaRepository<Payment, Long> {

    @Procedure(procedureName = "calculate_daily_totals",
               outputParameterName = "total_amount")
    BigDecimal getDailyTotals(@Param("input_date") LocalDate date);
}

Handling Output Parameters Beyond Simple Returns

Stored procedures often have OUTPUT parameters that return values via the parameter list, not as a result set. The @Procedure annotation supports this with the outputParameterName attribute, but there's a catch: it only works if the procedure has exactly one output parameter and returns it as the method's return value. If you have multiple output parameters, you're out of luck with this approach. In that case, you need to use EntityManager directly.

Here's a procedure that updates a payment status and returns both a success flag and a message:

@Repository
public class PaymentService {

    @PersistenceContext
    private EntityManager entityManager;

    public void updatePaymentStatus(Long paymentId, String status) {
        StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("update_payment_status")
            .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(3, Boolean.class, ParameterMode.OUT)
            .registerStoredProcedureParameter(4, String.class, ParameterMode.OUT)
            .setParameter(1, paymentId)
            .setParameter(2, status);

        query.execute();

        Boolean success = (Boolean) query.getOutputParameterValue(3);
        String message = (String) query.getOutputParameterValue(4);

        if (!success) {
            throw new PaymentException("Failed to update payment: " + message);
        }
    }
}

Dealing with Multiple Result Sets

Some stored procedures return multiple result sets—for example, a report that returns summary data and detail data. The @Procedure annotation cannot handle this; it only expects a single result set. If you try, you'll get a ClassCastException or just the first result set. The correct approach is to use StoredProcedureQuery and iterate through the result sets.

Here's a procedure that returns both a list of payments and a summary row:

public class ReportService {

    @PersistenceContext
    private EntityManager entityManager;

    public ReportData getPaymentReport(LocalDate date) {
        StoredProcedureQuery query = entityManager
            .createStoredProcedureQuery("get_payment_report")
            .registerStoredProcedureParameter(1, LocalDate.class, ParameterMode.IN)
            .setParameter(1, date);

        boolean isResultSet = query.execute();
        List<Payment> payments = new ArrayList<>();
        PaymentSummary summary = null;

        // First result set: payments
        if (isResultSet) {
            List<Object[]> rows = query.getResultList();
            for (Object[] row : rows) {
                payments.add(new Payment((Long) row[0], (BigDecimal) row[1]));
            }
        }

        // Second result set: summary
        if (query.hasMoreResults()) {
            Object[] summaryRow = (Object[]) query.getSingleResult();
            summary = new PaymentSummary((BigDecimal) summaryRow[0], (Long) summaryRow[1]);
        }

        return new ReportData(payments, summary);
    }
}

What the Official Docs Won't Tell You

The Spring Data JPA documentation makes it look easy, but here are the gotchas I've learned the hard way:

1. Hibernate Caching: When you call a stored procedure via @Procedure, Hibernate might cache the result in its second-level cache. This can lead to stale data if the procedure is not deterministic. Always mark the query as non-cacheable if needed: @ProcedureHints({@QueryHint(name = "org.hibernate.cacheable", value = "false")}).
2. Transaction Boundaries: Stored procedures often manage their own transactions. If your procedure commits internally, and then your Spring transaction rolls back, you'll have inconsistent state. Use REQUIRES_NEW propagation to isolate the procedure call.
3. Database-Specific SQL: @Procedure generates a JDBC call like {call procedure_name(?,?)}. This works on most databases, but some (like PostgreSQL) require a different syntax. Test on your target database.
4. Null Parameters: Passing null to a stored procedure parameter can cause unexpected behavior. Some databases treat null differently than an empty string. Always validate inputs before calling.
5. Multiple Result Sets and JPA: The @Procedure annotation simply cannot handle multiple result sets. The docs don't mention this limitation clearly. You must use EntityManager.
6. Output Parameters Order: The order of output parameters in registerStoredProcedureParameter must match the procedure definition exactly. If the procedure has IN, OUT, INOUT parameters, the order matters. I've seen bugs where the developer mixed up the order.
7. Timeout Settings: By default, there's no timeout on stored procedure calls. If your procedure hangs, it will block the connection pool. Always set a timeout: @Procedure(timeout = 30) or via JPA properties.

@Procedure(procedureName = "long_running_proc", timeout = 30)
List<Object[]> runLongProcedure(@Param("input") String input);

// Or globally:
// spring.jpa.properties.javax.persistence.query.timeout=30000

When to Use JDBC Template Instead

Sometimes JPA is overkill for stored procedure calls. If you're dealing with complex procedures that return multiple result sets, have many output parameters, or need fine-grained control over the JDBC connection, consider using JdbcTemplate or even plain JDBC. JPA adds overhead and abstraction that can get in the way.

Example with JdbcTemplate:

@Repository
public class PaymentDao {

    private final JdbcTemplate jdbcTemplate;

    public PaymentDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void callProcedure(Long id) {
        jdbcTemplate.execute(
            con -> {
                CallableStatement cs = con.prepareCall("{call update_payment(?)}");
                cs.setLong(1, id);
                return cs;
            }
        );
    }
}

Testing Stored Procedure Calls

Testing stored procedure calls is tricky because you need a real database. Unit tests with mocked repositories are useless—they don't validate the actual SQL. Use Testcontainers to spin up a real database in your tests. This catches issues like parameter mismatches, case sensitivity, and missing procedures early.

Here's a test example with Testcontainers:

@SpringBootTest
@Testcontainers
class PaymentRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:15");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    private PaymentRepository paymentRepository;

    @Test
    void testGetDailyTotals() {
        // Given: insert test data and create procedure
        // When:
        BigDecimal total = paymentRepository.getDailyTotals(LocalDate.of(2024, 1, 1));
        // Then:
        assertThat(total).isNotNull();
        assertThat(total).isGreaterThan(BigDecimal.ZERO);
    }
}