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

//數(shù)據(jù)庫(kù)操作類(lèi)
public class DBManager {
  //定義返回結(jié)果集
  ResultSet rs;
  //創(chuàng)建指定數(shù)據(jù)庫(kù)的URL
  String strurl = "jdbc:odbc:house";
  //缺省構(gòu)造方法
  public DBManager() {
  }

  //查詢方法，返回查詢結(jié)果集
  public ResultSet getResult(String sql) {
    try {
      //加載驅(qū)動(dòng)程序
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      //創(chuàng)建連接
      Connection conn = DriverManager.getConnection(strurl);
      //創(chuàng)建statement對(duì)象
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      //執(zhí)行SQL語(yǔ)句，返回結(jié)果集
      ResultSet rs = stmt.executeQuery(sql);
      //返回
      return rs;
    }
    //異常處理
    catch (Exception e) {
      //若有異常，返回null
      return null;
    }
  }

  //執(zhí)行更新，刪除語(yǔ)句方法
  public boolean executeSql(String sql) {
    try {
      //加載驅(qū)動(dòng)程序
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      //創(chuàng)建連接
      Connection conn = DriverManager.getConnection(strurl);
      //創(chuàng)建statement對(duì)象
      Statement stmt = conn.createStatement();
      //執(zhí)行SQL語(yǔ)句，返回結(jié)果集
      stmt.executeUpdate(sql);
      //提交到數(shù)據(jù)庫(kù)
      conn.commit();
      //返回true，表示操作成功
      return true;
    }
    catch (Exception e) {
      //返回false，表示操作失敗
      return false;
    }
  }

  //代碼值與代碼內(nèi)容互換，當(dāng)direction=1時(shí)，傳入代碼值得到代碼內(nèi)容；當(dāng)direction=0時(shí)，傳入代碼內(nèi)容得到代碼值
  public String CodeDesConvert(String table, String code, String des,
                               int direction) {
    String sql = new String();
    String result = new String();
    //code和des均為空時(shí)，返回空值
    if (code.equals("") && des.equals("")) {
      return "";
    }
    //生成查詢語(yǔ)句
    if (direction == 1) {
      sql = "select DESCRIPTION from " + table + " where CODE='" + code + "'";
    }
    else if (direction == 0) {
      sql = "select CODE from " + table + " where DESCRIPTION='" + des + "'";
    }
    try {
      //得到結(jié)果集
      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;
    }
  }

//獲取新紀(jì)錄的ID
  public String getID(String table, String IDItem) {
    //生成根據(jù)ID項(xiàng)從高到底排序的查詢語(yǔ)句
    String sql = "select * from " + table + " order by " + IDItem + " desc";
    String id = new String();
    try {
      //得到結(jié)果集
      ResultSet rs = getResult(sql);
      if (rs.first()) {
        //如果數(shù)據(jù)庫(kù)非空，得到第一條記錄，也就是ID值最大的記錄
        id = rs.getString(IDItem);
        //ID值增加1，得到新ID值
        id = Integer.toString(Integer.parseInt(id) + 1);
      }
      else {
        rs.close();
        //若數(shù)據(jù)庫(kù)為空，新ID值為1
        id = "1";
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return id;
  }

//傳入組合框和數(shù)據(jù)庫(kù)表，填充組合框信息，用于初始化代碼項(xiàng)
  public void fullCombo(JComboBox cbo, String table) {
    //生成查詢語(yǔ)句
    String sql = "select * from " + table + " order by CODE";
    String content = new String();
    try {
      //得到結(jié)果集
      ResultSet rs = getResult(sql);
      if (rs.first()) {
        rs.beforeFirst();
        //循環(huán)加載代碼描述
        while (rs.next()) {
          content = rs.getString("DESCRIPTION");
          cbo.addItem(content);
        }
      }
      else {
        rs.close();
      }
      cbo.setSelectedItem(null);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

//得到代碼表內(nèi)容
  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();
        //循環(huán)加載代碼表信息
        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;
  }

//傳入數(shù)據(jù)庫(kù)表名，要得到的字段名及查詢條件，從數(shù)據(jù)庫(kù)表中得到所需字段值
  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;
  }
}