Transaction Pairs 2 : Transaction « Database SQL JDBC « Java






Transaction Pairs 2

 
/*
 * Copyright (c) 2006 Sun Microsystems, Inc. All Rights Reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * -Redistribution of source code must retain the above copyright notice, this
 *  list of conditions and the following disclaimer.
 *
 * -Redistribution in binary form must reproduce the above copyright notice,
 *  this list of conditions and the following disclaimer in the documentation
 *  and/or other materials provided with the distribution.
 *
 * Neither the name of Sun Microsystems, Inc. or the names of contributors may
 * be used to endorse or promote products derived from this software without
 * specific prior written permission.
 *
 * This software is provided "AS IS," without a warranty of any kind. ALL
 * EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
 * ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE
 * OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MIDROSYSTEMS, INC. ("SUN")
 * AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
 * AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
 * DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
 * REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL,
 * INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY
 * OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE,
 * EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
 *
 * You acknowledge that this software is not designed, licensed or intended
 * for use in the design, construction, operation or maintenance of any
 * nuclear facility.
 */


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TransactionPairs {

  public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    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("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
      System.err.print("ClassNotFoundException: ");
      System.err.println(e.getMessage());
    }

    try {

      con = DriverManager.getConnection(url, "myLogin", "myPassword");

      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;
      con.setAutoCommit(false);
      for (int i = 0; i < len; i++) {
        updateSales.setInt(1, salesForWeek[i]);
        updateSales.setString(2, coffees[i]);
        updateSales.executeUpdate();

        updateTotal.setInt(1, salesForWeek[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());
        }
      }
    }
  }
}


           
         
  








Related examples in the same category

1.Demo MySql Transaction
2.Test Supports Transactions
3.Transaction Pairs
4.Transaction Info
5.JDBC Transaction
6.Using a database transaction with JDBC