package ch03.section07;

import java.sql.*;

import ch03.section03.*;

public class DBTransactionTest {
  private static Connection connection = null;
  private static ResultSet set = null;
  private static ResultSetMetaData rsmd;
  private static SQLTransaction sT;
  private static String insertStr1 = "insert into tickets ( " +
      "name, " +
      "startaddress, " +
      "endaddress, " +
      "pdate, " +
      "ptime " +
      ") values ( " +
      "'ivana', " +
      "'NY',  " +
      "'BOSTON', " +
      "'11-July-2002', " +
      "'9:00 AM' " +
      ")";
  private static String insertStr2 = "insert into tickets ( " +
      "name, " +
      "startaddress, " +
      "endaddress, " +
      "pdate, " +
      "ptime " +
      ") values ( " +
      "'ivana', " +
      "'BOSTON',  " +
      "'CANCUN', " +
      "'11-July-2002', " +
      "'12:00 PM' " +
      ")";
  private static String insertStr3 = "insert into tickets ( " +
      "name, " +
      "startaddress, " +
      "endaddress, " +
      "pdate, " +
      "ptime " +
      ") values ( " +
      "'ivana', " +
      "'CANCUN',  " +
      "'NY', " +
      "'12-August-2002', " +
      "'2:00 PM' " +
      ")";
  private static String insertStr4 = "insert into tickets ( " +
      "name, " +
      "startaddress, " +
      "endaddress, " +
      "pdate, " +
      "ptime " +
      ") values ( " +
      "'ivana', " +
      "'NY',  " +
      "'BOSTON', " +
      "'12-August-2002', " +
      "'6:00 PM' " +
      ")";
  private static String select =
      "select name,startaddress,endaddress,pdate,ptime from tickets";
  private static String delete = "delete from tickets";
  public static void main(String argv[]) {
    try {
      DBConnectionManager connMgr = DBConnectionManager.getInstance();
      connection = connMgr.getConnection("access");
      sT = new SQLTransaction(connection);

      //開始事務
      sT.executeUpdate(insertStr1);
      sT.executeUpdate(insertStr2);
      sT.executeUpdate(insertStr3);
      sT.executeUpdate(insertStr4);
      //提交事務
      sT.commitTransaction();

      set = sT.executeQuery(select);
      rsmd = set.getMetaData();
      for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        System.out.print(rsmd.getColumnName(i) + "\t");
      }
      System.out.println(
          "\n---------------------------------------------------");

      while (set.next()) {
        String name = set.getString(1);
        String startaddress = set.getString(2);
        String endaddress = set.getString(3);
        String pdate = set.getString(4);
        String ptime = set.getString(5);
        System.out.print(name + "\t");
        System.out.print(startaddress + "\t");
        System.out.print(endaddress + "\t");
        System.out.print(pdate + "\t");
        System.out.print(ptime + "\t" + "\n");
      }
      sT.executeUpdate(delete);
      sT.commitTransaction();
      System.out.println("操作完畢");
    }
    catch (Exception e) {
      System.out.println(e);
      try {
        //如果出現異常或錯誤,回滾事務
        sT.rollbackTransaction();
      }
      catch (SQLException ex) {
        System.out.println(ex.getMessage());
      }
    }
    finally { // clean up everything
      try {
        sT.close();
      }
      catch (SQLException ex) {
        System.out.println(ex.getMessage());
      }
    }
  }
}