Oli's Blog

If you want to save something in a database with pure JDBC you would normally

1. take a connection,
2. create a statement with it,
3. execute the statement and
4. close the connection.

Have I something forgotten? Don't know - so lets try it. Point 1 could be done by asking the Driver class, a connection pool or another kind of provider:

Connection connection = connectionProvider.getConnection();

With this connection you can call a method which uses this connection to save e.g. an address:

private static void save(Address address,
        Connection connection) throws SQLException {
    PreparedStatement stmt = connection.prepareStatement(
            "INSERT INTO addressbook (email, name, "
            + "city) VALUES (?, ?, ?)");
    try {
        stmt.setString(1, address.getEmail());
        stmt.setString(2, address.getName());
        stmt.setString(3, address.getCity());
        int rc = stmt.executeUpdate();
        if (rc < 1) {
            throw new SQLWarning("cannot save " + address);
        }
    } finally {
        stmt.close();
    }
}

As last point you close the connection in the calling method:

connection.close();

Does it work? In most cases yes, because normally the auto-commit flag is on, if you get a connection. But what happens, if auto-commit is off?

Auto-Commit Off = Lost Data

If auto-commit for a connecion is off you must call excplicit the commit:

connection.commit();

If you don't do it your changes are lost (there are some exeptions from that rule - the Oracle DB does a commit before it closes the connection).

The problem with this "forgotten" commit is, that no warning or exceptions comes up and you do not notice the lost data. This makes it hard to find the error - especially if the auto-commit is off only in same special cases.

The PatternTesting Proxy-Driver

PatternTesting provides you a wrapper around the original driver which allows you to log SQL statements but which warns you also if you forgot the commit statement for a connection with auto-commit off:

1 entries were updated with 'INSERT INTO addressbook (email, name, city) VALUES ('ob@aosd.de', 'Oli B.', 'nowhere')' but not committed.

This is the message you will see in this case. You only have to change the JDBC-URL and put ":proxy" after the jdbc prefix:

DriverManager.getConnection("jdbc:proxy:hsqldb:mem:testdb")

This is the URL for an HSQL memory DB. For more information see the Wiki article about SQL Logging and Monitoring.

Happy Debugging...