import java.sql.*;
public class TransactionPairs {
  public static void main( String args[] ) {
    String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    Connection con = null;
    Statement stmt;
    PreparedStatement updateSales;
    PreparedStatement updateTotal;
    
    String updateString = "UPDATE coffees " +
      "SET sales = ? WHERE cof_name LIKE ?";
    
    String updateStatement = "UPDATE coffees " +
      "SET total = total + ? WHERE cof_name LIKE ?";
    
    String query = "SELECT cof_name, sales, total FROM coffees";
    
    try {
      Class.forName( "oracle.jdbc.driver.OracleDriver" );
    } catch( java.lang.ClassNotFoundException e ) {
      System.err.print( "ClassNotFoundException: " );
      System.err.println( e.getMessage() );
    }
    
    try {
      con = DriverManager.getConnection( url, "scott", "tiger" );
      updateSales = con.prepareStatement( updateString );
      updateTotal = con.prepareStatement( updateStatement );
      
      int [] sales = {175, 150, 60, 155, 90};
      int [] totals = {1350, 1230, 2160, 2355, 3290};
      
      String [] coffees = {"Colombian", "French_Roast", 
        "Espresso", "Colombian_Decaf",
        "French_Roast_Decaf"};
      
      int len = coffees.length;
      
      con.setAutoCommit( false );
      
      for ( int i = 0; i < len; i++ ) {
        updateSales.setInt( 1, sales[i] );
        updateSales.setString( 2, coffees[i] );
        updateSales.executeUpdate();
        updateTotal.setInt( 1, sales[i] );
        updateTotal.setString( 2, coffees[i] );
        updateTotal.executeUpdate();
        con.commit();
      }
      
      for ( int i = 0; i < len; i++ ) {
        updateTotal.setInt( 1, totals[i] );
        updateTotal.setString( 2, coffees[i] );
        updateTotal.executeUpdate();
        updateTotal.setInt( 1, totals[i] );
        updateTotal.setString( 2, coffees[i] );
        updateTotal.executeUpdate();
        con.commit();
      }
      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" );
          con.rollback();
        } catch( SQLException excep ) {
          System.err.print( "SQLException: " );
          System.err.println( excep.getMessage() );
        }
      } 
    }    
  } 
}