dbmanager.java

java房产管理系统

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);
      if (rs.first()) {
        rs.beforeFirst();
        //循环加载代码描述
        while (rs.next()) {
          content = rs.getString("DESCRIPTION");
          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;
  }
}