Using try-with-resources

The following is best-practice with any JDBC application, regardless of the underlying database.

The try-with-resources statement is a try statement that declares one or more resources that implement Java’s AutoCloseable interface (and so must have implemented a close() method).

In JDBC terms, a resource may be a Connection, Statement, ResultSet, or any other class that implements java.io.AutoCloseable. The try-with-resources statement ensures that each resource is closed (its close() method is automatically invoked) at the end of the try block.

Note that any object created in a try-with-resources statement is scoped within that try statement, it is not available in any catch or finally block belonging to the same try block.

    try (Connection dbConnection = dataSource.getConnection()) {
        dbConnection.setAutoCommit(false);

        try (PreparedStatement stmt = dbConnection.prepareStatement( //
                "insert into accounts (id, name, balance) values (?, ?, 0)")) {
            stmt.setInt(1, id);
            stmt.setString(2, name);
            stmt.addBatch();
            stmt.executeBatch();
            dbConnection.commit();
            /* stmt is closed at end of block */
        } catch (SQLException e) {
            /* Note stmt is out of scope here */
            dbConnection.rollback();
            throw new RuntimeException("Insert failed", e);
        }
        finally {
            /* stmt is out of scope here too */
        }
    } /* dbConnection is closed at end of block */