20-CS-694 Advanced Programming Techniques Spring 2012
JDBC

Interfaces, Exceptions, Graphics, Animation, Threads, Reflection, Networking, RMI, JDBC, JNI

     Previous      Next      PostgreSQL Docs      MySQL Docs      PostgreSQL      MySQL      All lectures

Update a Table and Transaction Pairs

// Transaction pair: set of statements - either all or none finish
import java.sql.*;

public class TransactionPairs {
   public static void main(String args[]) {
      String url = "jdbc:mySubprotocol:myDataSource";
      Connection con = null;
      Statement stmt;
      PreparedStatement updateSales;  // Precompiled statement
      PreparedStatement updateTotal;  // Precompiled statement
      String updateString = "update COFFEES " +  // Arguments for prepared statement (?)
                  "set SALES = ? where COF_NAME like ?";
   
      String updateStatement = "update COFFEES " +  // Arguments for prepared statement (?)
            "set TOTAL = TOTAL + ? where COF_NAME like ?";
      String query = "select COF_NAME, SALES, TOTAL from COFFEES";
   
      try {
         Class.forName("myDriver.ClassName");
      }
      catch(java.lang.ClassNotFoundException e) {
         System.err.print("ClassNotFoundException: "); 
         System.err.println(e.getMessage());
      }

      try {
         con = DriverManager.getConnection(url, "myLogin", "myPassword");
   
         // Precompiled statement
         updateSales = con.prepareStatement(updateString);
         updateTotal = con.prepareStatement(updateStatement);
         int [] salesForWeek = {175, 150, 60, 155, 90};
         String [] coffees = {"Colombian", "French_Roast", 
                              "Espresso", "Colombian_Decaf",
                              "French_Roast_Decaf"};
         int len = coffees.length;
		   
         // Disable commit to allows groups of statements
         con.setAutoCommit(false);
         for (int i = 0; i < len; i++) {
            // Use precompiled statement - first set parameters
            updateSales.setInt(1, salesForWeek[i]);
            updateSales.setString(2, coffees[i]);
            updateSales.executeUpdate();

            // Use precompiled statement - first set parameters
            updateTotal.setInt(1, salesForWeek[i]);
            updateTotal.setString(2, coffees[i]);
            updateTotal.executeUpdate();
				
            // Above two statements are committed as single transaction
            con.commit();
         }
   
         // Return to default mode
         con.setAutoCommit(true);
   
         updateSales.close();
         updateTotal.close();
   
         stmt = con.createStatement();                     
         ResultSet rs = stmt.executeQuery(query);
            
         while (rs.next()) {
            String c = rs.getString("COF_NAME");
            int s = rs.getInt("SALES");
            int t = rs.getInt("TOTAL");
            System.out.println(c + "     " +  s + "    " + t);
         }
   
         stmt.close();
         con.close();
   
      }
      catch(SQLException ex) {
         System.err.println("SQLException: " + ex.getMessage());
         if (con != null) {
            try {
               System.err.print("Transaction is being ");
               System.err.println("rolled back");
					// Abort transaction - restore values
               con.rollback();
            }
            catch(SQLException excep) {
               System.err.print("SQLException: ");
               System.err.println(excep.getMessage());
            }
         }
      }   
   }
}