Java Reference
In-Depth Information
'Apple IIGS')");
st.executeUpdate("insert into item ( item_id, bidstartdate, createddate ,
initialprice , itemname ) values ( 1 , current_date, current_date, 100.50 ,
'Apple IIE')");
st.executeUpdate("insert into item ( item_id, bidstartdate, createddate ,
initialprice , itemname ) values ( 1 , current_date, current_date, 100.50 ,
'Apple IIC')");
This code has one important flaw: as each SQL statement is executed, the changes are com-
mitted to the database. The field
item_id
is a primary key, which means that it must be
unique. When the third state attempts to execute, it fails because a record with the same
item_id
has already been inserted and committed. To make all of these statements ex-
ecute within a transaction, you can turn off auto-commit and then explicitly commit and
roll back the changes if an error occurs. This is demonstrated in the following improved
code snippet:
Class.forName("org.postgresql.Driver");
Connection con =
DriverManager.getConnection("jdbc:postgresql:actionbazaar","user","pw");
con.setAutoCommit(false);
try {
Statement st = con.createStatement();
st.executeUpdate("insert into item ( item_id, bidstartdate, createddate ,
initialprice , itemname ) values ( 0 , current_date, current_date, 100.50 ,
'Apple IIGS')");
st.executeUpdate("insert into item ( item_id, bidstartdate, createddate ,
initialprice , itemname ) values ( 1 , current_date, current_date, 100.50 ,
'Apple IIE')");
st.executeUpdate("insert into item ( item_id, bidstartdate, createddate ,
initialprice , itemname ) values ( 1 , current_date, current_date, 100.50 ,
'Apple IIC')");
con.commit();
} catch (Throwable t) {
con.rollback();
t.printStackTrace();
}
At some point, practically every Java developer has written similar code. With this code,
you're directly managing the connection to the database. It's easy to make a mistake and
not correctly handle a failure or omit a commit. If the connection object is being passed
between multiple objects that are performing operations as a part of a transaction, things
can become unmanageable. Design considerations aside, managing the connections dir-
ectly precludes this code from coordinating operations among multiple resources such as
JMS.
