package ch03.section02;

import java.sql.*;

public class CreateDropTableTest {
  private static String dTableSQL = "CREATE TABLE digest " +
      "(id INTEGER NOT NULL," +
      " title VARCHAR(64) NOT NULL," +
      " author VARCHAR(64) NOT NULL)";

  private static String mTableSQL = "CREATE TABLE messages " +
      "(id INTEGER NOT NULL," +
      " title VARCHAR(64) NOT NULL," +
      " author VARCHAR(64) NOT NULL," +
      " message VARCHAR(500))";

  private static String aTableSQL = "CREATE TABLE authors " +
      "(author VARCHAR(64) NOT NULL," +
      " photo VARCHAR(500))";
  private static String dDropSQL =
      " if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[digest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " +
      "DROP TABLE digest";
  private static String mDropSQL =
      " if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " +
      "DROP TABLE messages";
  private static String aDropSQL =
      " if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[authors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " +
      "DROP TABLE authors";
  private static String baseInsertSQL = "Insert INTO digest VALUES(";

  private static int[] ids = {
      1, 2, 3, 4, 5};
  private static String[] authors = {
      "javaScript", "Java", "JSP", "J2EE", "J2ME"};
  private static String[] titles = {
      " javaScript百例",
      " Java百例",
      " JSP百例",
      " J2EE百例",
      " J2ME百例"};
  private static String querySQL = "SELECT id, author, title FROM digest";

  private static void CreateTable(Statement stmt) throws SQLException {
    System.out.println("----------------------------------------");
    stmt.executeUpdate(dTableSQL);
    stmt.executeUpdate(mTableSQL);
    stmt.executeUpdate(aTableSQL);
    System.out.println("Tables Created Successfully");
  }

  private static void InsertTable(Statement stmt) throws SQLException {
    System.out.println("----------------------------------------");
    for (int i = 0; i < ids.length; i++) {
      stmt.executeUpdate(baseInsertSQL
                         + ids[i]
                         + ", '"
                         + titles[i]
                         + "', '"
                         + authors[i]
                         + "')");
    }
    System.out.println("Tables Inserted Successfully");
  }

  private static void SelectTable(Statement stmt) throws SQLException {
    ResultSet rs = stmt.executeQuery(querySQL);
    ResultSetMetaData rsmd = rs.getMetaData();
    System.out.println("觀察數據庫結果");
    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
      System.out.print(rsmd.getColumnName(i) + "\t");
      if (i < 3) {
        System.out.print("|");
      }
    }
    System.out.println("\n----------------------------------------");

    while (rs.next()) {
      System.out.print(rs.getInt(1) + "\t");
      System.out.print("|");
      System.out.print(rs.getString(2) + "\t");
      System.out.print("|");
      System.out.println(rs.getString(3));
      System.out.println("\n----------------------------------------");
    }
    System.out.println("Tables Selected Successfully");
  }

  private static void DropTable(Statement stmt) throws SQLException {
    System.out.println("----------------------------------------");
    stmt.executeUpdate(dDropSQL);
    stmt.executeUpdate(mDropSQL);
    stmt.executeUpdate(aDropSQL);
    System.out.println("Tables Dropped Successfully");
  }

  public static void main(String argv[]) {

    //數據庫驅動程序的名稱
    String drivers = "sun.jdbc.odbc.JdbcOdbcDriver";
    // Odbc數據源url
    String url = "jdbc:odbc:test";
    //數據庫用戶名
    String username = "sa";
    //數據庫密碼
    String password = "sa";
    Connection con = null;
    try {

      //注冊Odbc數據庫驅動
      Class.forName(drivers);
      // 連接數據庫
      con = DriverManager.getConnection(url, username, password);
      // 建立數據庫statement對象
      Statement stmt = con.createStatement();
      DropTable(stmt);
      CreateTable(stmt);
      InsertTable(stmt);
      SelectTable(stmt);
      DropTable(stmt);
    }
    catch (Exception ex) {
      System.out.println("\nERROR:----- SQLException -----");
      System.out.println("Message: " + ex.getMessage());

    }
    finally {
      try {
        if (con != null) {
          con.close();
        }
      }
      catch (SQLException ex) {
        System.out.println("數據庫連接沒有被關閉");
      }
    }

  }
}