DB2 JDBC statements, result sets, and transactions

Statements

executeQuery returns a ResultSet (SELECT). executeUpdate returns a row count (INSERT, UPDATE, DELETE, some DDL). execute handles mixed or unknown statement types and procedure calls that may return multiple results.

1
2
3
4
5
6
7
8
try (Statement st = con.createStatement();
     ResultSet rs = st.executeQuery(
         "SELECT EMPNO, LASTNAME FROM HR.EMPLOYEE WHERE WORKDEPT = 'A00'")) {
  while (rs.next()) {
    String empno = rs.getString(1);
    String name = rs.getString("LASTNAME");
  }
}

That form concatenates a literal department. Fine for a fixed code; unsafe for a screen field. Use a prepared statement instead.

PreparedStatement

Markers are ?, numbered from 1. Types must match the column (setInt for INTEGER, setBigDecimal for DECIMAL, setDate/setTimestamp with java.sql types).

1
2
3
4
5
6
String sql = "UPDATE HR.EMPLOYEE SET BONUS = ? WHERE EMPNO = ?";
try (PreparedStatement ps = con.prepareStatement(sql)) {
  ps.setBigDecimal(1, bonus);
  ps.setString(2, empno);
  int rows = ps.executeUpdate();
}

Reuse the same PreparedStatement object in a loop when the SQL text is fixed. The IBM driver and Db2 dynamic statement cache then see one statement. setNull(index, sqlType) for SQL NULL. Do not put quotes around markers.

CallableStatement and stored procedures

1
2
3
4
5
6
7
8
try (CallableStatement cs = con.prepareCall("CALL HR.GET_EMP(?, ?)")) {
  cs.setString(1, empno);
  cs.registerOutParameter(2, Types.VARCHAR);
  cs.execute();
  String lastName = cs.getString(2);
  ResultSet rs = cs.getResultSet();
  // drain rs, then getMoreResults() if the procedure returns more sets
}

Procedures that return result sets open WITH RETURN cursors. Walk getResultSet and getMoreResults until there are none. INOUT parameters are registered and also set before execute. Match the procedure’s PARAMETER STYLE and the driver’s handling of result-set locators.

ResultSet

createStatement and prepareStatement overloads take resultSetType, resultSetConcurrency, and resultSetHoldability:

• TYPE_FORWARD_ONLY — default; rs.next() only forward.
• TYPE_SCROLL_INSENSITIVE — random access; does not see others’ changes.
• TYPE_SCROLL_SENSITIVE — can see committed changes to rows (Db2 sensitive cursors).
• CONCUR_READ_ONLY versus CONCUR_UPDATABLE — updatable cursors need a single-table SELECT that Db2 can make updatable; then updateRow/insertRow/deleteRow.
• HOLD_CURSORS_OVER_COMMIT — WITH HOLD; CLOSE_CURSORS_AT_COMMIT closes at commit.

getString/getInt and friends: check wasNull() after getXxx for nullable columns. Column indexes are 1-based. Prefer column labels when the SELECT list is stable; indexes survive renamed labels but break when the list changes.

Transactions

JDBC default is autocommit on. Each successful statement is its own unit of work. For multi-statement business logic:

1
2
3
4
5
6
7
8
9
con.setAutoCommit(false);
try {
  ps1.executeUpdate();
  ps2.executeUpdate();
  con.commit();
} catch (SQLException e) {
  con.rollback();
  throw e;
}

Changing autocommit commits the current unit of work if you are not already on a boundary. Connections in a global / XA transaction must not call commit, rollback, or setAutoCommit(true); the JTA coordinator owns the boundary (see DDF two-phase commit).

setTransactionIsolation on the Connection. The IBM driver maps these to UR/CS/RS/RR for dynamic SQL on that connection. CS is the usual OLTP choice, matching BIND ISOLATION(CS).

Batch processing

For many similar INSERTs or UPDATEs:

1
2
3
4
5
ps.setInt(1, id);
ps.setString(2, name);
ps.addBatch();
// repeat
int[] counts = ps.executeBatch();

executeBatch returns per-statement update counts (or SUCCESS_NO_INFO). A BatchUpdateException includes counts up to the failure. Batching cuts DRDA round trips; huge batches make a single timeout or -803 harder to isolate. Keep autocommit off and commit every N rows for restartability.

LOBs, XML, and metadata

BLOB/CLOB/DBCLOB: setBlob/setClob or streams. Reading, getBlob then getBinaryStream rather than getBytes on multi-megabyte values. Progressive streaming and locators avoid pulling the whole LOB into the JVM. Free locators when the driver documents that you must.

XML: java.sql.SQLXML with getSQLXML/setSQLXML, or character streams. CCSID and XMLPARSE rules still apply on the server; the driver is only the pipe.

Metadata:

• DatabaseMetaData — getTables, getColumns, getPrimaryKeys, getProcedures; catalog/schema/table patterns. On z/OS the “catalog” is often the location; schema is the qualifier (CREATOR).
• ResultSetMetaData — column count, names, types after a query; useful for generic dumpers.
• ParameterMetaData — types of ? markers when the driver and server support DESCRIBE INPUT.

SQL exceptions

Almost every JDBC method can throw SQLException. On Db2:

• getErrorCode() — SQLCODE (negative is error, positive is warning when delivered as SQLWarning).
• getSQLState() — five-character SQLSTATE (class 08 is connection, 23 is constraint, 42 is syntax, and so on).
• getNextException() — chained exceptions (driver plus server).
• SQLWarning — Connection.getWarnings() / Statement.getWarnings() after a successful call (+ extras such as truncation).

JDBC 4 adds subclasses (SQLTransientConnectionException, SQLIntegrityConstraintViolationException for -803). Catch the subclass you can handle; log SQLCODE and SQLSTATE for operations. Do not retry -803 blindly; fix the key. Do retry some 08xxx after a pool validation failure.

Explain It Like I'm Five

A Statement is shouting a whole sentence every time. A PreparedStatement is a fill-in worksheet: the teacher keeps the printed page and you only write today’s numbers in the blanks (?). A ResultSet is the stack of answer sheets you flip through. Autocommit is handing in each worksheet the second you finish a line; turning it off means you wait until the whole test is done (commit) or you crumple it (rollback). Batch is stapling twenty worksheets and handing them in together. SQLException is the red X with a number (SQLCODE) explaining what went wrong.

Exercises

1. Rewrite a Statement that concatenates lastName into a PreparedStatement with one marker. Add a comment on injection.
2. Write setAutoCommit(false) around an INSERT plus an UPDATE and rollback on SQLException.
3. Map TRANSACTION_REPEATABLE_READ to a Db2 isolation name and say when you would use it.
4. Outline draining two result sets from a stored procedure with getMoreResults.
5. Print SQLCODE and SQLSTATE from a caught SQLException in a utility method.