| 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) |
Create this procedure once in your database to follow the examples below:
-- 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;
$$;
Use the JDBC escape syntax {call proc(?, ?, ...)} and register OUT parameters with registerOutParameter() before executing:
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.
Some JDBC drivers support named parameter access using the column name from the procedure definition:
// 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.
Functions return a single value via the special ? at position 1:
-- 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;
$$;
// {? = 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);
}
Some procedures return multiple cursors. Use getMoreResults() to advance through them:
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 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:
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 describes the ? parameters of a PreparedStatement:
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.