Java SE 8 Programmer II: Building Database Applications with JDBC

Building Database Applications with JDBC tests connections, statements, result sets, transactions, and resource cleanup.

Core rules

• DriverManager.getConnection(url, user, password) returns a Connection. JDBC 4 auto-loads drivers from the classpath.
• Statement executes static SQL; PreparedStatement is precompiled, uses ? placeholders, prevents SQL injection, and is generally preferred. Parameter indexes start at 1.
• executeQuery() returns a ResultSet for SELECT; executeUpdate() returns an affected-row count for DML and 0 for DDL. execute() returns a boolean.
• The ResultSet cursor starts before the first row; call next() before reading. Column labels are case-insensitive by default.
• Use getInt, getString, getObject, etc.; wasNull() checks whether the last read SQL NULL was returned as a Java primitive.
• Transactions: set autoCommit(false), perform work, then commit() or rollback(). Savepoint allows partial rollback.
• Try-with-resources closes ResultSet, Statement, and Connection in reverse order.

Worked example

try (Connection c = DriverManager.getConnection(url); PreparedStatement ps = c.prepareStatement("UPDATE emp SET name=? WHERE id=?")) { ps.setString(1, "Ada"); ps.setInt(2, 7); int rows = ps.executeUpdate(); } The first placeholder is index 1, the second index 2. executeUpdate() returns the number of rows changed.

Traps

• Using index 0 for a PreparedStatement parameter throws SQLException.
• Reading a ResultSet before calling next() is invalid.
• getInt() on SQL NULL returns 0; use wasNull() to distinguish it from a real 0.
• Leaving autoCommit true means each statement commits immediately.
• Concatenating user input into SQL invites injection; use parameters.
• SQLException is checked and may carry a SQLState and vendor error code.