Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 119 ยท Databases with JDBC

Statements, Stored Procedures and ResultSet Types

Compare Statement, PreparedStatement and CallableStatement, call stored procedures with IN and OUT parameters, and use scrollable ResultSets and RowSets.

So far you have sent SQL with PreparedStatement and read rows forward with next(). That covers most data-access code, but JDBC has more tools, and you will meet all of them in older code bases and database-heavy companies: CallableStatement for stored procedures, the general-purpose execute() method for SQL whose shape you don't know, updatable result sets that insert and delete rows through the cursor, and RowSet objects that keep working after the connection is closed.

This lesson compares the three statement types and shows when each one is the right choice, then calls stored procedures and functions with IN and OUT parameters. Every runnable example uses H2 in memory. H2 can't run MySQL- or PostgreSQL-style procedure code, but it can turn a static Java method into a database function, which is perfect for practicing the JDBC side. The real procedure syntax for MySQL and PostgreSQL is shown read-only. You should already be comfortable with CRUD with JDBC and transactions.

Three statement types

All three interfaces live in java.sql, and each one extends the previous: CallableStatement extends PreparedStatement extends Statement. So everything a Statement can do, the other two can do as well.

Statement PreparedStatement CallableStatement
Created with connection.createStatement() connection.prepareStatement(sql) connection.prepareCall(sql)
SQL known when at execution: execute(sql) at creation, with ? placeholders at creation, usually {call name(?, ?)}
Parameters none (values glued into the text) IN parameters IN, OUT and INOUT parameters
Reuse parsed each time can be planned once and run many times same as prepared
Typical use fixed DDL, admin scripts, SQL consoles every query or update with values stored procedures and functions

The rules of thumb:

  • PreparedStatement is the default. Any SQL with a variable part goes through it, for the safety reasons in PreparedStatement and SQL Injection and because drivers and pools can cache its plan.
  • Statement is fine for SQL with no variables at all: CREATE TABLE, a migration script, SET commands, or a tool that runs SQL typed by an administrator who already has full access.
  • CallableStatement is for calling code that lives inside the database: stored procedures and functions, especially ones with OUT parameters.
Java The same work with all three statement types

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

CREATE ALIAS ... FOR "Main.priceWithTax" is H2's way of publishing a public static Java method as an SQL function. That's why this example uses a classic public class Main instead of a compact void main() file: H2 needs a class name to find the method.

Stored procedures and functions

A stored procedure is a named block of code stored and run inside the database server. It can contain several statements, variables, loops and error handling, and it runs next to the data, so a ten-step operation costs one network round trip instead of ten. A stored function is similar but returns a value and can usually be used inside a SELECT.

Why teams use them:

  • Fewer round trips for multi-step operations, which matters on slow networks.
  • One implementation for many clients: a Java service, a reporting tool and a nightly script all call the same close_month() procedure.
  • Permissions: an application account can be allowed to EXECUTE transfer_funds without being allowed to UPDATE account directly.

Why many teams avoid putting business logic in them: procedure code is hard to unit test, version and debug, it ties you to one database vendor, and it splits the business rules between Java and SQL. A common compromise is to keep procedures for data-heavy batch work and legacy integration, and keep business rules in Java.

Calling syntax and parameter modes

JDBC defines a vendor-neutral escape syntax in curly braces, which the driver translates into its own SQL:

Java
CallableStatement proc = db.prepareCall("{call transfer_funds(?, ?, ?, ?)}");  // procedure
CallableStatement func = db.prepareCall("{? = call account_balance(?)}");      // function: the first ? is the result

Each parameter has a mode:

Mode You call Meaning
IN setXxx(index, value) a value going into the procedure (the same as in PreparedStatement)
OUT registerOutParameter(index, Types.X) before executing, then getXxx(index) after a value the procedure sends back
INOUT both setXxx and registerOutParameter on the same index goes in, and comes back changed

Forgetting registerOutParameter is the classic mistake: the driver then doesn't know the parameter is an output and throws when you call getXxx. Indexes count every ? in the call, including the result placeholder of a function, starting at 1. Many drivers also accept parameter names (cs.setBigDecimal("p_amount", ...)), but support varies, so check before relying on it.

Stored procedures in MySQL and PostgreSQL (read-only)

Here is a money transfer as a MySQL procedure with three IN parameters and one OUT parameter. DELIMITER is a command of the MySQL client, needed so the ; inside the body doesn't end the CREATE statement:

SQL
-- MySQL 8
DELIMITER //
CREATE PROCEDURE transfer_funds(
    IN  p_from        BIGINT,
    IN  p_to          BIGINT,
    IN  p_amount      DECIMAL(12, 2),
    OUT p_new_balance DECIMAL(12, 2))
BEGIN
    START TRANSACTION;
    UPDATE account SET balance = balance - p_amount WHERE id = p_from;
    UPDATE account SET balance = balance + p_amount WHERE id = p_to;
    SELECT balance INTO p_new_balance FROM account WHERE id = p_from;
    COMMIT;
END //
DELIMITER ;

PostgreSQL 14 and later support OUT parameters in procedures, written in PL/pgSQL. Functions (CREATE FUNCTION ... RETURNS) have existed for much longer and are still more common there:

SQL
-- PostgreSQL 14+
CREATE PROCEDURE transfer_funds(
    p_from BIGINT, p_to BIGINT, p_amount NUMERIC(12, 2),
    OUT p_new_balance NUMERIC(12, 2))
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE account SET balance = balance - p_amount WHERE id = p_from;
    UPDATE account SET balance = balance + p_amount WHERE id = p_to;
    SELECT balance INTO p_new_balance FROM account WHERE id = p_from;
END;
$$;

The Java side is the same for both databases:

Java
try (CallableStatement cs = db.prepareCall("{call transfer_funds(?, ?, ?, ?)}")) {
    cs.setLong(1, 1L);
    cs.setLong(2, 2L);
    cs.setBigDecimal(3, new BigDecimal("75.00"));
    cs.registerOutParameter(4, Types.DECIMAL);
    cs.execute();
    BigDecimal newBalance = cs.getBigDecimal(4);
}

The PostgreSQL driver translates {call ...} into SELECT * FROM name(...) by default, which works for functions but fails for procedures. For a procedure, either write the native CALL transfer_funds(?, ?, ?, ?) in prepareCall, or add escapeSyntaxCallMode=callIfNoReturn to the JDBC URL.

Practicing with H2

H2 has no procedural SQL language. Instead, CREATE ALIAS maps a function name to a public static Java method. If the method's first parameter is a java.sql.Connection, H2 passes in the current connection, so the method can run SQL in the caller's transaction, just like a real procedure. If the method returns a ResultSet, the function returns rows. H2 has no separate OUT parameters, so the function's return value plays that role.

Java A stored transfer function and a function that returns rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The second call fails inside the function, and the exception reaches Java as an ordinary SQLException with the SQLState 45000 the function chose (MySQL's SIGNAL SQLSTATE '45000' produces the same code for user-defined errors). Because the function checked the balance before touching the second account, nothing was half done. Real procedures should be just as careful, or run inside a transaction that the caller rolls back.

Procedures that return several result sets (common in SQL Server and MySQL) are read with the loop in the next section. Call getResultSet() for the current one and getMoreResults() to move to the next. Read all result sets before calling getXxx on OUT parameters: some drivers only fill OUT values once every result has been consumed.

execute(), executeQuery() and executeUpdate()

CRUD with JDBC introduced the three execute methods. The interesting one is execute(), because it is the only one that works when you don't know what the SQL will return. It returns true if the first result is a ResultSet and false if it is an update count (or there is no result). After that, a loop walks through every result:

  • getResultSet() returns the current result set, or null;
  • getUpdateCount() returns the current update count, or -1 if the current result is a result set or there are no more results;
  • getMoreResults() moves to the next result, closes the current result set, and again returns true for a result set.

The end is reached when getMoreResults() returns false and getUpdateCount() returns -1. Both checks are needed, because false alone only means "the next result is not a result set".

This is exactly the loop a SQL console, a migration tool or a database IDE runs:

Java A tiny SQL console built on execute()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

CREATE TABLE reports 0 row(s) affected, because DDL returns an update count of 0, and the DELETE that matches nothing also reports 0. The final typo shows the error path: SQLState 42001 is H2's code for a syntax error. Edit the Input box to try your own statements.

Two related methods are worth knowing. getMoreResults(Statement.KEEP_CURRENT_RESULT) moves on without closing the current result set, for drivers that support it. And for statements that may change more than two billion rows, executeLargeUpdate() and getLargeUpdateCount() return long.

Which method should normal code use? The most specific one: executeQuery() for SELECT, executeUpdate() for changes. They fail fast when the SQL is not what you expected, and they make the code easier to read. Use execute() only for SQL you can't predict, and for procedure calls that may return result sets and update counts.

Scrollable and updatable result sets

A ResultSet has three properties you pick when you create the statement:

Java
Statement st = db.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,      // type: which directions the cursor can move
    ResultSet.CONCUR_UPDATABLE,             // concurrency: read-only or updatable
    ResultSet.HOLD_CURSORS_OVER_COMMIT);    // holdability: does it survive commit()?
// the same arguments exist for prepareStatement(sql, type, concurrency[, holdability])
  • Type: TYPE_FORWARD_ONLY (the default), TYPE_SCROLL_INSENSITIVE (move anywhere, see a snapshot) or TYPE_SCROLL_SENSITIVE (move anywhere and see other sessions' changes; few drivers support it).
  • Concurrency: CONCUR_READ_ONLY (the default) or CONCUR_UPDATABLE.
  • Holdability: HOLD_CURSORS_OVER_COMMIT keeps the result set open after a commit, CLOSE_CURSORS_AT_COMMIT closes it. The default is driver-specific; db.getMetaData().getResultSetHoldability() tells you.

If a driver can't give you what you asked for, it usually downgrades silently and adds an SQLWarning to the connection, so check rs.getType() and rs.getConcurrency() when it matters. Cursor movement (first(), last(), absolute(n), relative(n), previous()) is demonstrated in the next lesson. Here the focus is on changing data through the cursor.

An updatable result set offers three kinds of change:

  • Update the current row: updateXxx(column, value) for each column, then updateRow(). Calling cancelRowUpdates() instead throws the changes away.
  • Delete the current row: deleteRow().
  • Insert a new row: moveToInsertRow() moves to a special staging row, updateXxx fills it, insertRow() sends it, and moveToCurrentRow() returns to where you were.
Java Inserting, updating and deleting through a ResultSet

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Updatable result sets only work for queries the driver can map back to one table with its primary key in the select list: no joins, no GROUP BY, no computed columns. They were popular in desktop applications that showed a table in a grid. In server code, plain UPDATE, DELETE and batched INSERT statements are clearer and usually faster, so treat this mainly as something to recognize when you see it.

RowSet: result sets that travel

A javax.sql.RowSet is a ResultSet that is also a JavaBean: you configure it with setters (setUrl, setCommand, parameter setters) and call execute(). The standard sub-interfaces live in javax.sql.rowset (module java.sql.rowset), and you create them with RowSetProvider.newFactory():

Type Connected? What it adds
JdbcRowSet yes a scrollable, updatable wrapper around a live ResultSet
CachedRowSet no copies all rows into memory, works offline, writes changes back later with acceptChanges()
WebRowSet no a CachedRowSet that reads and writes itself as XML
FilteredRowSet no hides rows that don't match a Predicate, without a new query
JoinRowSet no joins several cached row sets in memory

The disconnected types are the interesting ones. A CachedRowSet loads the rows, and then you can close the connection, move the object around (it is Serializable), edit it, and reconnect only to write the changes. When it writes back, it checks that each changed row still holds the values it originally read, and reports a conflict if someone else changed it in the meantime: optimistic locking without a version column.

Java A disconnected CachedRowSet

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

acceptChanges commits on its own, which is why it wants autocommit off: then it can write all changes in one transaction. One quirk of the JDK's implementation is worth knowing. To detect conflicts, it appends a WHERE clause to your command, so a command that ends in ORDER BY produces invalid SQL during acceptChanges. Keep write-back commands to a simple SELECT ... FROM table.

Filtering in memory and the connected JdbcRowSet

A FilteredRowSet applies a javax.sql.rowset.Predicate while you iterate, which is handy when a screen shows the same cached data with different filters. A JdbcRowSet is the opposite: it stays connected and simply makes any driver's result set scrollable and bean-like.

Java FilteredRowSet and JdbcRowSet

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

RowSets came from the era of desktop clients and remote objects, and modern server code rarely uses them: a list of records is easier to pass around than a CachedRowSet. You will still meet them in older code, and a CachedRowSet remains a quick way to snapshot a small query result and close the connection early.

Fetch size and large results

A forward-only, read-only result set is the cheapest kind, and the right choice for reading big tables. What matters then is how many rows the driver pulls per network trip, which you hint with setFetchSize(n) on the statement (or on the result set). A few more limits are useful in the same place:

Java
try (PreparedStatement ps = db.prepareStatement(
        "SELECT id, payload FROM event ORDER BY id",
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
    ps.setFetchSize(500);        // rows per round trip (a hint)
    ps.setMaxRows(100_000);      // safety cap: the driver silently drops rows beyond this
    ps.setQueryTimeout(30);      // seconds; then SQLTimeoutException
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            // process one row at a time, never collect them all
        }
    }
}

Driver defaults differ a lot. H2 reports a fetch size of 100. The PostgreSQL driver reads the whole result into memory unless autocommit is off and a fetch size is set. MySQL Connector/J also buffers everything by default, and streams only with setFetchSize(Integer.MIN_VALUE) or useCursorFetch=true in the URL. The next lesson returns to big data from the writing side with batch inserts and large objects.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You call a procedure {call get_total(?, ?)} whose second parameter is OUT. What must happen before execute()?

    The driver must know that index 2 is an output and which SQL type to expect. Without registerOutParameter, reading the value with getBigDecimal(2) throws.
  2. 2. In {? = call account_balance(?)}, which index do you use for the function's argument?

    Every question mark counts, starting at 1. The first one is the return value, so the argument is index 2.
  3. 3. st.execute(sql) returned false. What do you know?

    execute returns false when the first result is not a ResultSet. Failures throw SQLException, and a SELECT with no rows still returns true with an empty ResultSet. Check getUpdateCount() to tell an update count (0 or more) from no result (-1).
  4. 4. Which query can back a CONCUR_UPDATABLE result set in most drivers?

    The driver must map every row back to one table and its primary key. Aggregates, joins and computed columns make that impossible.
  5. 5. A CachedRowSet loaded rows, the connection was closed, and you changed two rows. What writes them to the database?

    updateRow only changes the in-memory copy. acceptChanges reconnects, checks each changed row for conflicts and writes the changes, committing when it is done.

Summary

  • Statement runs fixed SQL, PreparedStatement runs SQL with ? parameters and is the default, and CallableStatement (prepareCall) calls stored procedures and functions.
  • The escape syntax is {call proc(?, ?)} for procedures and {? = call func(?)} for functions. IN parameters use setXxx, OUT parameters need registerOutParameter before executing and getXxx after, INOUT needs both.
  • Stored procedures save round trips and centralize data logic, but are harder to test and tie you to one database. H2 maps static Java methods to functions with CREATE ALIAS.
  • execute() handles SQL of unknown kind; loop with getResultSet(), getUpdateCount() and getMoreResults() until there is no result set and the update count is -1.
  • Result sets have a type (forward-only or scrollable), a concurrency (read-only or updatable) and a holdability. Updatable ones support updateRow(), deleteRow() and insertRow() on single-table queries.
  • RowSetProvider.newFactory() creates JdbcRowSet (connected), CachedRowSet (disconnected, acceptChanges()), FilteredRowSet and others.
  • For big reads use forward-only, read-only result sets, a sensible fetch size, and know your driver's buffering defaults.

Next: Batch Updates, Metadata and Large Objects