CallableStatement is the JDBC interface for invoking code that lives inside the database itself, stored procedures and stored functions. It extends PreparedStatement, so you keep the parameter-binding and SQL-injection safety from the previous lesson, but adds two new ideas: output parameters that the database fills in for you, and a special escape syntax for the call. This lesson covers how to call procedures and functions, how to pass IN, OUT, and INOUT parameters, how to read a ResultSet back from a procedure, and when calling into the database is actually worthwhile.

Most SQL covered so far runs from the application: build a query as text, send it to the server, read the rows back, process them in Java. That model works well for straightforward reads and writes. It starts to hurt when a single business operation needs many queries glued together with branching logic.

Consider the discount on an order. The workflow has to look up the customer's loyalty tier, count how many past orders they have placed, check whether any items in the cart are already on sale, apply a percentage based on the total, and return the final price. With five separate queries, every query is a round trip to the database server. On a connection that is 5 milliseconds away from the application, that is 25 milliseconds of pure wait before any real work happens. Multiply by every page load and the latency adds up fast.

A stored procedure moves that logic onto the database server. The application sends one call, the database runs all five queries locally, and one answer comes back. The round-trip count drops from five to one. The database also gets to plan and cache the procedure once, instead of parsing five separate statements per request.

The diagram shows the trade. On the left, the application drives the workflow and pays for every hop. On the right, the procedure drives it and pays only for the final answer. The bigger the chain of dependent queries, the more obvious the win.

Procedures also give a few other benefits. The database can enforce permissions at the procedure level, so an application user can run place_order without having raw INSERT access to the orders table. That tightens the blast radius if the application is compromised. The procedure body can also be the single source of truth for "how is a discount calculated," instead of every microservice reimplementing the same rules.

Each network round trip to the database costs at least one network RTT. On a typical cloud setup that is around 1-5 ms inside a data center and tens of milliseconds across regions. A workflow that needs 10 dependent queries from the application takes 10x longer than the same logic in one procedure call.

There is a cost on the other side. Procedure bodies are written in a database-specific language (PL/pgSQL for Postgres, PL/SQL for Oracle, T-SQL for SQL Server, Java for H2 in this lesson). The logic does not live in the application's Git repository alongside the rest of the application code unless put there deliberately. It also does not move between database vendors without a rewrite.

The database world has two flavors of stored code, and JDBC handles them with the same CallableStatement interface but with slightly different syntax.

A stored procedure is a routine invoked for its effects. It can update tables, return zero or more values through output parameters, and produce result sets. It does not have a "return value" in the function sense. It's called like a void method.

A stored function is a routine that returns a single value. It's called as part of an expression. SQL functions and procedures are not interchangeable in standard SQL; calling syntax, where they can appear, and how the result comes back are all different.

JDBC has a vendor-neutral escape syntax for both forms. The braces tell the driver "this is a callable, translate it to whatever this database expects." A procedure call looks like {call calculateDiscount(?, ?)} and a function call looks like {? = call calculateDiscount(?)} where the leading ? is bound as the return value placeholder. Drivers rewrite this to the native syntax (CALL calculateDiscount(...), EXEC calculateDiscount(...), SELECT calculateDiscount(...), depending on the vendor) so the same Java code keeps working across databases.

The escape syntax is a portability tool. The procedure body itself, written in PL/pgSQL or PL/SQL or T-SQL, is not portable. Only the call site is.

To run the examples without installing a real database, the examples use H2 in embedded in-memory mode. H2 ships with the JVM toolchain in many setups and is one Maven dependency away otherwise. It supports JDBC the same way Postgres and Oracle do, and it has a feature called CREATE ALIAS that registers a static Java method as a stored function or procedure. That keeps the examples self-contained: the "stored function" is just a Java method H2 points at.

To make the lessons runnable, every example uses the URL jdbc:h2:mem:shop;DB_CLOSE_DELAY=-1. The mem:shop part says "in-memory database named shop." The DB_CLOSE_DELAY=-1 flag keeps the database alive for the lifetime of the JVM even after the last connection closes; without it, H2 drops the data the moment the connection ends.

Here is the bootstrap class that other examples assume. It creates the schema, seeds a few rows, and registers two H2 aliases that act as the stored function and stored procedure.

The two aliases point to a ShopFunctions class that holds the actual logic. Real production code would write these in PL/pgSQL or PL/SQL, but with H2 the body is plain Java. This lesson is about the JDBC client side, not procedure authoring, so the Java body keeps the focus on the client API.

The calculateDiscount function takes a total and returns a discount percentage. The getOrderStatus function takes an order id and reads the status out of the database. H2 passes the current Connection as the first argument automatically when a method declares one, which is how the function reaches into the same database it is running inside.

CREATE ALIAS in H2 is a runtime registration. The class must be on the classpath the JVM is running with. Real deployments ship a JAR that contains both the application and the "stored" code.

The simplest case is a procedure that takes input values, does something on the server, and returns nothing through OUT parameters. Register one that updates an order's status.

The body for updateOrderStatus lives in the same ShopFunctions class. It runs an UPDATE, returns nothing useful, and would be a CREATE PROCEDURE block in Postgres.

From the Java client side, calling this procedure looks almost exactly like a PreparedStatement. Ask the connection for a CallableStatement with the escape syntax, bind the IN parameters, then call execute.

The escape syntax {call update_order_status(?, ?)} is the portable form. The parameter binders are the same setInt, setString, setBigDecimal, and so on from PreparedStatement, because CallableStatement extends it. Parameter indexes start at 1, not 0, just like every other JDBC index.

execute is the generic call. executeUpdate works for procedures that primarily change data and executeQuery for ones that return a ResultSet. When the kind isn't known, execute is the safe choice; check the return values afterward to figure out what came back.

A procedure can return values through output parameters. The pattern has three steps. Before executing, tell JDBC the SQL type of each OUT parameter with registerOutParameter. Then execute the call. After execution, read each OUT parameter with the matching getXxx method.

To demonstrate, register a procedure that takes an order id, calculates the discount, and returns both the discount percentage and the final price as OUT parameters.

H2's quirk is that it uses single-element arrays to model OUT parameters in Java, because plain Java parameters are pass-by-value. In a real PL/pgSQL or PL/SQL procedure, OUT parameters are a first-class language feature. The JDBC side is identical either way.

Register and call:

The three new lines compared to a regular PreparedStatement are the two registerOutParameter calls and the two getXxx calls after execute. The shape of the call is:

The java.sql.Types constant tells JDBC the SQL type, not the Java type. Types.DECIMAL means a fixed-precision decimal, read with getBigDecimal. Types.INTEGER is read with getInt, Types.VARCHAR with getString, Types.TIMESTAMP with getTimestamp, and so on. The mapping is the standard JDBC SQL-to-Java type table.

The sequence diagram shows why OUT parameters need the explicit registerOutParameter step. The driver has to allocate space and tell the database what to send back. Without the registration call, the driver does not know which positional parameters are inputs versus outputs.

OUT parameters do not save round trips compared to inputs alone, but they let one CALL return many values without a result set. A procedure that returns five OUT parameters is one round trip; the same data as five SELECT queries is five round trips.

An INOUT parameter is one that the application initializes with a value, the procedure reads it, modifies it, and the application reads the modified value back. JDBC handles INOUT with both setXxx and registerOutParameter on the same index.

Use case: a procedure that takes the customer's current cart total, applies a loyalty multiplier in place, and returns the adjusted value through the same parameter.

Java side:

Both calls on parameter 2 are required. setBigDecimal provides the input value the procedure starts with; registerOutParameter reserves space for the value it leaves behind. Skip either one and the procedure will see a missing input or there will be nothing to read after execute.

INOUT parameters are less common than pure IN or OUT in modern code. They show up in legacy stored procedures and in cases that amend a value rather than compute a fresh one. Treat them as a notation, not a different mechanism: an INOUT is just an IN and an OUT that share an index.

A function returns a single value, and the JDBC escape syntax for one looks like {? = call funcName(?)}. The leading ? is the return value slot, bound at index 1; the regular ? parameters start at index 2.

The earlier setup registered calculate_discount and get_order_status. Call them from JDBC:

Index 1 is the return value, index 2 is the first declared parameter. The return slot still needs registerOutParameter because, from JDBC's perspective, it is an OUT parameter that the function fills in.

A second example with a different return type:

Types.VARCHAR plus getString is the standard pairing for text values. The H2 getOrderStatus body queries the orders table on the same connection, which is why order 2's original status reads as PLACED, not the SHIPPED set earlier (that change happened in a separate JVM/connection in the previous example, so this fresh in-memory database starts from the seed data).

The flow for a function is shorter than for a procedure with OUT parameters: only one slot to register, one slot to read. The shape stays the same as procedures otherwise. This is why CallableStatement covers both.

Procedures can also return rows. The pattern is the same as a SELECT: call executeQuery and iterate the ResultSet. The example here is minimal.

Register a procedure that returns every order for a customer.

H2 alias and JDBC call:

The procedure returns rows even though no OUT parameter is declared. The result set comes back through executeQuery directly, the same way it does from a PreparedStatement. A procedure can also return both a ResultSet and OUT parameters. In that case call execute, walk the result set first (or use getResultSet), and only read the OUT parameters after.

Procedures that return large result sets do not save bytes on the wire compared to a plain SELECT. The win is hiding the SQL behind a stable interface and centralizing logic, not bandwidth.

Stored procedures bundle multiple steps into one call. Here is a slightly larger example: a procedure that takes an order id, looks up the order's total, applies the discount function, updates the order's final_total column, and returns both the discount percentage and the new total to the caller through OUT parameters.

First, add the column the workflow uses:

The procedure body, in ShopFunctions:

And the JDBC call:

One call replaced three round trips: read the order, compute the price, write the result. The application still gets the answer it needs, but the network cost dropped from three RTTs to one, and the discount rules now live in one place.

Compare this to the same workflow done from the application: select, then update, then return. Each arrow in that version goes to the database and back. One CALL versus three queries is the small-scale version of why high-volume systems still find a place for stored procedures.

A workflow that needs N dependent queries from the application takes N round trips. The same workflow in one stored procedure takes 1. The savings are largest when the client and database sit in different data centers.

CallableStatement is a tool, not a default. The decision to push logic into the database is bigger than the decision to use JDBC at all.

Reasons to use stored procedures:

Reasons to avoid them:

The pragmatic answer in modern application stacks is to prefer plain SQL via PreparedStatement for most queries, and use CallableStatement only when one of the "reasons to use" above clearly applies. A discount calculation that needs five queries is a fair candidate. A simple SELECT * FROM products WHERE id = ? is not.

Forgetting `registerOutParameter`.

The fix is to register every OUT parameter before execute. JDBC has no way to guess which positional placeholders are outputs.

Wrong `java.sql.Types` constant.

Types.INTEGER paired with getInt truncates a decimal to its integer part on most drivers, and may throw on others. Match the SQL type of the underlying value. Types.DECIMAL with getBigDecimal, Types.VARCHAR with getString, Types.TIMESTAMP with getTimestamp.

Using native syntax instead of the escape syntax.

The string is hard-coded to PostgreSQL/H2 syntax. Oracle wants BEGIN price_order(2, ?, ?); END;, SQL Server wants EXEC price_order ?, ?. Use {call price_order(?, ?, ?)} and let the driver translate.

Reading OUT parameters before `execute`.

The registration only reserves the slot. The value does not exist until execute runs. Always order the calls register, set inputs, execute, read outputs.

Not closing the `CallableStatement`. Like every JDBC resource, CallableStatement holds server-side cursors and driver-side memory. Wrap it in try-with-resources so it closes whether the call succeeds or throws.