JDBC Transaction

JDBC Transaction let you control how and when a transaction should commit into database.

1.  
   //transaction block start
2. //SQL insert statement
3. //SQL update statement
4. //SQLdelete statemetn
5. //transaction block end
6. 
   

In simple, JDBC transaction make sure SQL statements within a transaction block are all executed successful, if either one of the SQL statement within transaction block is failed, abort and rollback everything within the transaction block.

See below two examples to understand how JDBC transaction works.

1. Without JDBC Transaction

By default, data will be committed into database when executeUpdate() is called.

1. String insertTableSQL="INSERT INTO DBUSER"
   
2.             + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
   
3.             + "(?,?,?,?)";
   
4.  
   
5. String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
   
6.             + "WHERE USER_ID = ?";
   
7.  
   
8. preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
   
9. preparedStatementInsert.setInt(1, 999);
   
10. preparedStatementInsert.setString(2,"mkyong101");
    
11. preparedStatementInsert.setString(3,"system");
    
12. preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
    
13. preparedStatementInsert.executeUpdate();//data COMMITTED into database.
    
14.  
    
15. preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
    
16. preparedStatementUpdate.setString(1,"A very very long string caused DATABASE ERROR");
    
17. preparedStatementUpdate.setInt(2, 999);
    
18.  
    
19. preparedStatementUpdate.executeUpdate();//Error, value too big, ignore this update statement,
    
20.                                                
    

When this code is executed, the USER_ID = ’999′ is inserted but the username is not update.

2. With JDBC Transaction

To put this in a transaction, you can use

1. dbConnection.setAutoCommit(false); to start a transaction block.
2. dbConnection.commit(); to end a transaction block.

See code snippets :

1. dbConnection.setAutoCommit(false);//transaction block start
   
2.  
   
3. String insertTableSQL = "INSERT INTO DBUSER"
   
4.             + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
   
5.             + "(?,?,?,?)";
   
6.  
   
7. String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
   
8.             + "WHERE USER_ID = ?";
   
9.  
   
10. preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
    
11. preparedStatementInsert.setInt(1, 999);
    
12. preparedStatementInsert.setString(2,"mkyong101");
    
13. preparedStatementInsert.setString(3,"system");
    
14. preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
    
15. preparedStatementInsert.executeUpdate();//data IS NOT commit yet
    
16.  
    
17. preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
    
18. preparedStatementUpdate.setString(1,"A very very long string caused DATABASE ERROR");
    
19. preparedStatementUpdate.setInt(2, 999);
    
20. preparedStatementUpdate.executeUpdate();//Error, rollback, including the first insert statement.
    
21.  
    
22. dbConnection.commit();

When this code is executed, update statement is hits error, and make both insert and update statements rollback together.