When a connection is created using JDBC, by default it is in auto-commit mode. This means that each SQL statement is treated as a transaction and will be automatically committed immediately after it is executed. Sometimes, you want a group of statements to execute together or fail together. Transactions are used to group a set of statements so that they all execute successfully, or all fail. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. The following line of code will do this:
conn.setAutoCommit(false);Once auto-commit mode is disabled, no SQL statement will be committed until the commit method is called explicitly. All statements starting from the previous call to the commit will be committed together. If there is a problem, the entire set of statements can be rolled back, without committing. The following code illustrates this:
conn.setAutoCommit(false);try{PreparedStatement updateSales = _conn.prepareStatement( "UPDATE PRODUCT_SALES SET NUMBER_OF_SALES=NUMBER __OF_SALES+1 WHEREPRODUCT_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);
