CallableStatement, Stored Procedures & ResultSet Metadata

Stored procedures and functions live in the database and are called from Java using CallableStatement: a specialisation of PreparedStatement. Parameters can be IN (input), OUT (output the caller reads back), or INOUT (both). ResultSetMetaData lets you inspect column names and types at runtime without knowing the schema in advance. This article covers all of these with practical PostgreSQL examples.

Stored Procedure vs Function

A stored procedure is a named block of SQL logic saved inside the database and invoked by client code. Rather than sending multiple SQL statements over the network, you call the procedure once and let the database execute the logic close to the data, reducing round-trips and centralising business rules. A stored function is similar but always returns exactly one value and is typically used inline in a SQL expression. From Java, both are called through CallableStatement using the JDBC escape syntax, but the syntax differs slightly depending on which you are calling.

Concept	Stored Procedure	Stored Function
Returns	Zero or more OUT params / ResultSets	Exactly one return value
JDBC escape syntax	`{call proc_name(?, ?)}`	`{? = call func_name(?, ?)}`
Transaction control	Can COMMIT/ROLLBACK inside (DB-dependent)	Typically read-only (DB-dependent)
Side effects	Allowed	Discouraged (pure functions preferred)

Sample PostgreSQL Stored Procedure

Create this procedure once in your database to follow the examples below:

SQL

-- Procedure with IN and OUT parameters
CREATE OR REPLACE PROCEDURE transfer_funds(
    IN  p_from_account  BIGINT,
    IN  p_to_account    BIGINT,
    IN  p_amount        NUMERIC,
    OUT p_status        VARCHAR,
    OUT p_new_balance   NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - p_amount
    WHERE  id = p_from_account AND balance >= p_amount;

    IF NOT FOUND THEN
        p_status      := 'INSUFFICIENT_FUNDS';
        p_new_balance := NULL;
        RETURN;
    END IF;

    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;

    SELECT balance INTO p_new_balance FROM accounts WHERE id = p_from_account;
    p_status := 'OK';
END;
$$;

Calling a Stored Procedure: IN and OUT Parameters

Use the JDBC escape syntax {call proc(?, ?, ...)} and register OUT parameters with registerOutParameter() before executing:

Java

String sql = "{call transfer_funds(?, ?, ?, ?, ?)}";

try (Connection conn = dataSource.getConnection();
     CallableStatement cs = conn.prepareCall(sql)) {

    // IN parameters — use setXxx()
    cs.setLong(1, fromAccountId);
    cs.setLong(2, toAccountId);
    cs.setBigDecimal(3, new BigDecimal("500.00"));

    // OUT parameters — register before execute
    cs.registerOutParameter(4, Types.VARCHAR);    // p_status
    cs.registerOutParameter(5, Types.NUMERIC);   // p_new_balance

    cs.execute();   // or executeUpdate() for procedures that modify data

    // Read OUT parameters by index after execute
    String status     = cs.getString(4);
    BigDecimal newBal = cs.getBigDecimal(5);

    System.out.println("Status:      " + status);
    System.out.println("New balance: " + newBal);
}

Always call registerOutParameter() for every OUT or INOUT parameter before execute(). The driver needs the SQL type mapping to allocate the right network buffer.

Named Parameters (Some Drivers)

Some JDBC drivers support named parameter access using the column name from the procedure definition:

Java

// Named access — driver support varies
cs.registerOutParameter("p_status",      Types.VARCHAR);
cs.registerOutParameter("p_new_balance", Types.NUMERIC);
cs.execute();

String status = cs.getString("p_status");

Named parameter support is driver-specific. The PostgreSQL JDBC driver does not support named parameters on CallableStatement. Always prefer positional (index-based) access for maximum portability.

Calling a Stored Function with a Return Value

Functions return a single value via the special ? at position 1:

SQL

-- PostgreSQL function
CREATE OR REPLACE FUNCTION get_account_balance(p_id BIGINT)
RETURNS NUMERIC LANGUAGE sql AS $$
    SELECT balance FROM accounts WHERE id = p_id;
$$;

Java

// {? = call func(?)}  — leading ? is the return value
try (CallableStatement cs = conn.prepareCall("{? = call get_account_balance(?)}")) {

    cs.registerOutParameter(1, Types.NUMERIC);  // return value at index 1
    cs.setLong(2, accountId);

    cs.execute();
    BigDecimal balance = cs.getBigDecimal(1);
    System.out.println("Balance: " + balance);
}

Stored Procedure Returning Multiple ResultSets

Some procedures return multiple cursors. Use getMoreResults() to advance through them:

Java

try (CallableStatement cs = conn.prepareCall("{call get_orders_and_items(?)}")) {
    cs.setLong(1, customerId);

    boolean hasResults = cs.execute();

    while (hasResults) {
        try (ResultSet rs = cs.getResultSet()) {
            ResultSetMetaData meta = rs.getMetaData();
            System.out.println("=== Result set: " + meta.getColumnCount() + " columns ===");
            while (rs.next()) {
                for (int i = 1; i <= meta.getColumnCount(); i++) {
                    System.out.print(meta.getColumnName(i) + "=" + rs.getString(i) + "  ");
                }
                System.out.println();
            }
        }
        hasResults = cs.getMoreResults();
    }
}

ResultSetMetaData: Dynamic Column Inspection

ResultSetMetaData describes the shape of a ResultSet at runtime: useful for generic query tools, CSV exporters, or code that doesn't know the schema at compile time:

Java

try (Connection conn = dataSource.getConnection();
     Statement  stmt = conn.createStatement();
     ResultSet  rs   = stmt.executeQuery("SELECT * FROM orders LIMIT 5")) {

    ResultSetMetaData meta = rs.getMetaData();
    int cols = meta.getColumnCount();

    // Print header
    for (int i = 1; i <= cols; i++) {
        System.out.printf("%-20s", meta.getColumnName(i));
    }
    System.out.println();

    // Print rows
    while (rs.next()) {
        for (int i = 1; i <= cols; i++) {
            System.out.printf("%-20s", rs.getString(i));
        }
        System.out.println();
    }
}

Key ResultSetMetaData methods:

Method	Returns
`getColumnCount()`	Number of columns in the result
`getColumnName(i)`	Column name from the query
`getColumnLabel(i)`	Column alias (from `AS`), falls back to name
`getColumnType(i)`	`java.sql.Types` constant
`getColumnTypeName(i)`	Database-specific type name (e.g., `varchar`, `int8`)
`getPrecision(i)`	Column precision for numeric types
`isNullable(i)`	`columnNoNulls / columnNullable / columnNullableUnknown`
`getTableName(i)`	Source table name (driver-dependent accuracy)

ParameterMetaData: Inspecting PreparedStatement Parameters

ParameterMetaData describes the ? parameters of a PreparedStatement:

Java

PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO orders (customer_id, amount, status) VALUES (?, ?, ?)");

ParameterMetaData pmd = ps.getParameterMetaData();

for (int i = 1; i <= pmd.getParameterCount(); i++) {
    System.out.printf("Param %d — SQL type: %d, nullable: %d%n",
        i, pmd.getParameterType(i), pmd.isNullable(i));
}

Not all JDBC drivers fully implement ParameterMetaData. PostgreSQL supports it. For portability, do not rely on it in production code: use it for debugging and tooling only.

Stored Procedure vs Function

Sample PostgreSQL Stored Procedure

Calling a Stored Procedure: IN and OUT Parameters

Named Parameters (Some Drivers)

Calling a Stored Function with a Return Value

Stored Procedure Returning Multiple ResultSets

ResultSetMetaData: Dynamic Column Inspection

ParameterMetaData: Inspecting PreparedStatement Parameters

Related articles