JDBC - Transactions

Sometimes you have multiple Database interactions which are related.

For example, suppose we are doing a money transfer between two accounts. It will involve deleting the amount from one account, adding the amount to the other account and updating the transfer log.

If any of these individual database interactions fail, then all three should be failed. Otherwise there will be an inconsistency between the accounts and the logs.

This is called a Transaction - when a series of database interactions all succeed or fail together.

JDBC supports transactions, through its commit() and rollback() methods.

By default, all JDBC query executions are auto-commit.

So to do a transaction, we first set the autocommit to false.
Then if all the individual query executions pass, we do a commit.
Otherwise we do a rollback.

Let us see this with an example.

We will write a method in UserDAO which persists several users at a time, as a transaction. Either all the users persist, or none of them.

Update your UserDAO.java, like this:

 package org.confucius;  
   
 import java.sql.Connection;  
 import java.sql.PreparedStatement;  
 import java.sql.ResultSet;  
 import java.sql.SQLException;  
 import java.util.ArrayList;  
 import java.util.Iterator;  
 import java.util.List;  
   
 import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;  
   
 public class UserDAO {  
      private static MysqlConnectionPoolDataSource dataSource = null;  
        
      // Static initialization block  
      static {  
           dataSource = new MysqlConnectionPoolDataSource();            
           dataSource.setUser("confucius");  
           dataSource.setPassword("changeit");  
           dataSource.setServerName("localhost");  
           dataSource.setPort(3306);  
           dataSource.setDatabaseName("confuciusDB");  
      }  
        
      public static void persistGroup (List<User> users) throws SQLException{  
     Connection conn = dataSource.getConnection();   
     conn.setAutoCommit(false);  
   
     String sqlQuery = "insert into users value(?, ?)";   
     PreparedStatement ps = conn.prepareStatement(sqlQuery);   
       
     try {  
       for (Iterator<User> iter = users.iterator(); iter.hasNext(); ){  
            User user = iter.next();  
         ps.setString(1, user.getFirstName());   
         ps.setString(2, user.getLastName());   
         ps.execute();   
       }  
       conn.commit();  
     }  
     catch (SQLException e){  
          conn.rollback();  
          throw e;  
     }  
       
     conn.setAutoCommit(true);  
       
     ps.close();   
     conn.close();   
      }  
        
        
 }  
   

Now, update TestPersistence.java, like this:

 package org.confucius;  
   
   
 import java.sql.SQLException;  
 import java.util.ArrayList;  
 import java.util.Iterator;  
 import java.util.List;  
   
 public class TestPersistence {  
   
      public static void main(String[] args) {  
           try {  
                User jack = new User();  
                jack.setFirstName("Jack");  
                jack.setLastName("Hill");  
                  
                User jill = new User();  
                jill.setFirstName("Jill");  
                jill.setLastName("WentUpTheHillToFetchAPailOfWaterFellDownBrokeCrown");  
                  
                List<User> users = new ArrayList<User>();  
                users.add(jack);  
                users.add(jill);  
                  
                UserDAO.persistGroup(users);  
                  
           } catch (SQLException e) {  
                e.printStackTrace();  
           }  
             
             
      }  
   
 }  
   

We try to persist two users - Jack and Jill.

However, we have given Jill a last name that exceeds 32 characters - so it will fail persist.

R-click on the TestPersistence.java in Eclipse- Navigator view. Select Run As-->Java Application.

You will see a DataTruncation exception in the console.

If you look at the users table in MySQL, you will not see Jack - because Jill failed to persist, Jack was not persisted either.

If you give Jill a shorter last name, and re-run the application, they will both be persisted.