Java JDBC reference: parameters, resources and transaction ownership

Java 8+. This program requires the H2 JDBC driver; the downloadable Maven project declares it.

Choose an owner before issuing a statement

A repository borrowing a connection from a larger unit of work should not independently commit that caller’s transaction. A project that exclusively owns the connection can define its own commit boundary. Document which model a method requires.

Close result sets and statements at the scope that consumes them. A returned lazy iterator over a ResultSet also returns a resource lifetime problem; it needs a close contract and error handling after the calling method returns.

The parameter probe sends a quote-containing string as one value to H2. No concatenation changes the SQL text. Parameter markers ordinarily cannot replace a table or column identifier, so user-selected identifiers require a separately validated query choice.

Review failed work explicitly

On an update, check the affected-row count when the invariant expects exactly one row. On an exception, preserve both the primary error and rollback errors. Do not send a failed connection back to a pool with an assumption that its transaction state is healthy.

Working program

import java.sql.*;
public class BoundValueReference {
    public static void main(String[] args) throws Exception {
        try(Connection connection=DriverManager.getConnection("jdbc:h2:mem:parameter_reference");
            PreparedStatement query=connection.prepareStatement("SELECT ?")){
            query.setString(1,"customer'42");
            try(ResultSet result=query.executeQuery()){
                result.next();System.out.println(result.getString(1));
            }
        }
    }
}

Output

customer'42

Costs and boundaries

This probe checks one bound value with H2 2.3.232. It does not measure query preparation or pool throughput. General result consumption costs depend on the number and size of rows, network transfer and driver buffering.

Common Mistakes

• Do not concatenate values into SQL.
• Parameter binding does not select arbitrary identifiers safely.
• A helper must not commit a transaction owned by its caller.

Read next

Prepared statements, Transactional repository.