JDBC Transaction Management

Posted in: JDBC Most major RDMBS systems support the concept of transactions. A transaction allows you to group multiple SQL statements together. Using a transaction-aware RDBMS, you can begin a transaction, perform any number of actions, and either commit the results to the database or roll back all of your SQL statements.

A transaction is isolated from the rest of the database until finished. As far as the rest of the database is concerned, everything takes place at once (in other words, transactions are atomic). This means that other users accessing the database will always see a valid view of the data, although not necessarily an up-to-date view. If a user requests a report on widgets sold before your widget sales transaction is completed, the report will not include the most recent sale.

This is done because transactions are linked to connections and, therefore, connections using transactions cannot be shared.

conn.setAutoCommit(false);
try{
PreparedStatement updateSales = _
conn.prepareStatement(
    "UPDATE PRODUCT_SALES SET NUMBER_OF_SALES=NUMBER _
_OF_SALES+1 WHERE
PRODUCT_ID = ?");
updateSales.setString(1, productId);
updateSales.executeUpdate();
PreparedStatement updateInventory = _
conn.prepareStatement(
    "UPDATE INVENTORY SET STOCK=STOCK-1 _
WHERE PRODUCT_ID = ?");
updateInventory.setString(1, productId);
updateInventory.executeUpdate();
conn.commit();
}
catch(SQLException se){
  conn.rollback();
}
conn.setAutoCommit(true);