Java JDBC CallableStatement

A CallableStatement invokes a stored procedure or function that lives inside the database. It extends PreparedStatement, so it has the same ? placeholder binding — plus the ability to register OUT parameters that the procedure writes back to you. Use it when business logic is implemented in the database rather than in Java.

The JDBC escape syntax

You write the call using portable brace syntax, which each driver translates to its vendor's dialect:

// procedure with two IN parameters
CallableStatement cs = conn.prepareCall("{call add_customer(?, ?)}");

// function returning a value, with one IN parameter
CallableStatement fn = conn.prepareCall("{? = call total_orders(?)}");

The braces mean you do not have to know whether the vendor spells it CALL, EXEC, or BEGIN ... END.

IN, OUT, and INOUT parameters

A procedure parameter has a direction:

• IN — you supply it: cs.setInt(1, customerId), exactly like a PreparedStatement.
• OUT — the procedure fills it; you must register its type first, then read it after execution.
• INOUT — both: set it, then register it, then read it.

CallableStatement cs = conn.prepareCall("{call get_balance(?, ?)}");
cs.setInt(1, accountId);                              // IN
cs.registerOutParameter(2, java.sql.Types.DECIMAL);   // OUT — declare its type
cs.execute();
BigDecimal balance = cs.getBigDecimal(2);             // read it back

Why registerOutParameter needs a type

JDBC has to know how to interpret the bytes the database sends back before the call runs, so you name the SQL type with a java.sql.Types constant. Get this type wrong and the getXxx afterward will fail or mis-convert. The order is fixed: register OUT → set IN → execute → getXxx.

A worked example: building the call and registering OUT

This program builds both escape-syntax call strings and walks the OUT-parameter registration with the java.sql.Types codes it needs — the full call protocol, expressed without a live database.

What to take from the run:

• The {call proc(?, ?)} braces are the portable escape syntax. You write the same string regardless of vendor, and the driver rewrites it — this is what keeps stored-procedure calls database-agnostic.
• The {? = call fn(?)} form is for functions that return a value: the leading ? is the return slot, registered as an OUT parameter at index 1, with the real arguments following.
• registerOutParameter takes a java.sql.Types constant (INTEGER is 4, DECIMAL is 3). That is the same type vocabulary from earlier chapters — JDBC reuses it everywhere it must name a SQL type without a Java value in hand.
• The type you register must match what the procedure actually returns; otherwise the later getInt/getBigDecimal mis-reads the bytes. Registering is a promise about the shape of the result.
• The protocol order is strict and worth memorising: register OUT parameters, set IN parameters, execute(), then read OUT values with getXxx(index). The example spells out that sequence because doing it out of order is the usual cause of "parameter not registered" errors.

Practice