JDBC · Lesson 10

Stored Procedures with CallableStatement

Calling Stored Procedures

A stored procedure is database-side code exposed as a callable operation. JDBC uses CallableStatement to bind input values and read output parameters. Procedures can centralize data-heavy operations, but they create a database-specific contract that deserves versioning and tests.

Using CallableStatement

The call syntax uses braces and parameter markers. Register output parameters before execution and read them with the matching getter afterward.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;

public class BillingRepository {
    public int recordPayment(Connection connection, long learnerId, double amount) throws Exception {
        try (CallableStatement statement = connection.prepareCall("{call record_payment(?, ?, ?)}")) {
            statement.setLong(1, learnerId);
            statement.setDouble(2, amount);
            statement.registerOutParameter(3, Types.INTEGER);
            statement.execute();
            return statement.getInt(3);
        }
    }
}

Parameter Types

Common Mistake and Practices

Common Mistake: Assuming a procedure's parameter order or SQL types are obvious from its name. A mismatch may fail only at runtime and can be difficult to diagnose across database vendors.

• Document procedure contracts beside repository code.
• Register exact SQL types for outputs.
• Test procedures against the real database engine.
• Keep procedures focused and observable.
• Avoid using vendor-specific procedures without a portability decision.

Real-World Use Case

A billing service calls a procedure that applies a payment, updates an account balance, and returns a receipt number. The repository translates the database result into a typed Java value.