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);
      //返回
      return rs;
    }
    //異常處理
    catch (Exception e) {
      //若有異常，返回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);
       //提交到數據庫
      conn.commit();
      //返回true，表示操作成功
      return true;
    }
    catch (Exception e) {
      //返回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();
    //code和des均為空時，返回空值
    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 + "'";
    }
    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;
    }
  }
//獲取新紀錄的ID
  public String getID(String table,String IDItem) {
    //生成根據ID項從高到底排序的查詢語句
    String sql = "select * from " + table + " order by "+IDItem+" desc";
    String id = new String();
    try {
      //得到結果集
      ResultSet rs = getResult(sql);
      if (rs.first()) {
        //如果數據庫非空，得到第一條記錄，也就是ID值最大的記錄
        id = rs.getString(IDItem);
        //ID值增加1，得到新ID值
        id = Integer.toString(Integer.parseInt(id) + 1);
      }
      else {
        rs.close();
        //若數據庫為空，新ID值為1
        id = "1";
      }
    }
    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);
      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);
      if (rs.first()) {
        value = rs.getString(wantedField);
      }
      else {
        rs.close();
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return value;
  }

}