import java.sql.*;
import javax.swing.*;
import java.util.*;

//數據庫操作類
public class DBManager {
  //定義返回結果集
  ResultSet rs;
  //創建指定數據庫的URL
  String strurl = "jdbc:odbc:house";
  //缺省構造方法
  public DBManager() {
  }

  //查詢方法，返回查詢結果集
  public ResultSet getResult(String sql) {
    try {
      //加載驅動程序
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      //創建連接
      Connection conn = DriverManager.getConnection(strurl);
      //創建statement對象
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      //執行SQL語句，返回結果集
      ResultSet rs = stmt.executeQuery(sql);
      System.out.println("executeSql------" + sql);
      //返回
      return rs;
    }
    //異常處理
    catch (Exception e) {
      System.out.println("getResult------" + e.toString());
      //若有異常，返回null
      return null;
    }
  }

  //執行更新，刪除語句方法
  public boolean executeSql(String sql) {
    try {
      //加載驅動程序
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      //創建連接
      Connection conn = DriverManager.getConnection(strurl);
      //創建statement對象
      Statement stmt = conn.createStatement();
      //執行SQL語句，返回結果集
      stmt.executeUpdate(sql);
      System.out.println("executeSql------" + sql);
      //提交到數據庫
      conn.commit();
      //返回true，表示操作成功
      return true;
    }
    catch (Exception e) {
      System.out.println("executeSql------" + e.toString());
      //返回false，表示操作失敗
      return false;
    }
  }

  //代碼值與代碼內容互換，當direction=1時，傳入代碼值得到代碼內容；當direction=0時，傳入代碼內容得到代碼值
  public String CodeDesConvert(String table, String code, String des,
                               int direction) {
    String sql = new String();
    String result = new String();
    if (code.equals("") && des.equals("")) {
      return "";
    }
    if (direction == 1) {
      sql = "select DESCRIPTION from " + table + " where CODE=" + code;
    }
    else if (direction == 0) {
      sql = "select CODE from " + table + " where DESCRIPTION='" + des + "'";
    }
    System.out.println(sql);
    try {
      ResultSet rs = getResult(sql);
      if (rs.first()) {
        if (direction == 1) {
          result = rs.getString("DESCRIPTION");
        }
        else if (direction == 0) {
          result = rs.getString("CODE");
        }
      }
      else {
        rs.close();
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    finally {
      return result;
    }
  }

  public int getID(String table) {
    String sql = "select * from " + table + " order by ID desc";
    int id = 0;
    try {
      ResultSet rs = getResult(sql);
      System.out.println(rs.first());
      if (rs.first()) {
        id = Integer.parseInt(rs.getString("ID"));
      }
      else {
        rs.close();
      }
      id++;
      System.out.println(id);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return id;
  }

  public void fullCombo(JComboBox cbo, String table) {
    String sql = "select * from " + table + " order by CODE";
    String content = new String();
    try {
      ResultSet rs = getResult(sql);
      System.out.println(rs.first());
      if (rs.first()) {
        rs.beforeFirst();
        while (rs.next()) {
          content = rs.getString("DESCRIPTION");
          System.out.println(content);
          cbo.addItem(content);
        }
      }
      else {
        rs.close();
      }
      cbo.setSelectedItem(null);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  public Vector getCode(String table) {
    String sql = "select * from " + table + " order by CODE";
    Vector vecResult = new Vector();
    try {
      ResultSet rs = getResult(sql);
      System.out.println(rs.first());
      if (rs.first()) {
        rs.beforeFirst();
        while (rs.next()) {
          String[] code = new String[2];
          code[0] = rs.getString("CODE");
          code[1] = rs.getString("DESCRIPTION");
          vecResult.add(code);
        }
      }
      else {
        rs.close();
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return vecResult;
  }

  public String getByField(String table, String wantedField, String condition) {
    String sql = "select * from " + table + " where " + condition;
    String value = "";
    Vector vecResult = new Vector();
    try {
      ResultSet rs = getResult(sql);
      System.out.println(rs.first());
      if (rs.first()) {
        value = rs.getString(wantedField);
      }
      else {
        rs.close();
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return value;
  }

}