Java: Rollback Database updates?

Question

I have three methods that, together, save a node to the database. Currently, this does not achieve the desired effect of not committing the changes unless all the writes are successful.

    conn.setAutoCommit(false);
    writeNodeTable(node, newNodeNID);
    writeContentTypeBoutTable(node, newNodeNID);
    writeTerms(node, newNodeNID);
    conn.commit();

If there is a problem (such as an uncaught exception is thrown) in the second or third method, I'd like to roll back all the changes. Is there some way I can use transactions to do this?

This is one such DB writing function:

private void writeTerms(DrupalNode node, int newNodeNID) throws SQLException {
    String sql = "INSERT INTO term_node (nid, vid, tid) VALUES (?, ?, ?)";
    PreparedStatement prep = conn.prepareStatement(sql);
    prep.setInt(1, newNodeNID);
    prep.setInt(2, newNodeNID);

    String schoolName = termHelp.schoolOfAgainst(node.get(BoutField.against));
    prep.setString(3, schoolName);

    prep.execute();

    String fencerName = termHelp.fencerOfAgainst(node.get(BoutField.against));
    prep.setString(3, fencerName);
    prep.execute();
    prep.close();
}

Answer 1

You can use rollback method in Connection interface. http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html#rollback()

Also you have to set setAutoCommit to false and call commit and rollback methods as appropriately.

Answer 2

Upul is correct, you would need to implement the Connection.setAutoCommit(false) and the Connection.rollback() methods. One thing you might want to try for each transaction is to have this sort of flow:

Connection conn = null; // or have the connection sent in
try {
    conn = getConnection(username, password);
    conn.setAutoCommit(false);
    // do stuff with connection
    conn.commit();
} catch (SQLException sqle) {
    // handle error appropriately
    conn.rollback();
} finally {
    // cleanup
    conn.close(); // depends on when the connection was allocated
}