Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Java
Expertise: Beginner
Dec 11, 2001

Using Database Transactions with JDBC


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 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);
Santhosh Ananthakrishnan
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap