query.java

此程序是在JBuilderX中开发。若采用低版本JBuilder

import javax.swing.*;
import java.awt.*;
import com.borland.jbcl.layout.*;
import java.awt.event.*;
import javax.swing.table.*;
import java.util.*;
import java.sql.*;

public class Query
    extends JFrame {

  DefaultTableModel model = new DefaultTableModel();
  //定义JTable为DefaultTableModel的实例model模式
  JTable tblResult = new JTable(model);
  //定义链表，用于建立组合框序号与表名、字段等的对应关系
  //数据库操作变量
  DBManager db = new DBManager();
  //数据库表链表
  LinkedList linkTable = new LinkedList();
  //字段链表
  LinkedList linkItem = new LinkedList();
  //操作符链表
  LinkedList linkOper = new LinkedList();
  //代码值链表
  LinkedList linkCode = new LinkedList();
  //ID链表，供查看记录信息时与序号建立对应关系
  LinkedList linkID = new LinkedList();
  //用于加载代码表
  Vector vecCode = new Vector();
  //当前操作表
  String curTable = new String();
  //当前操作字段
  String curItem = new String();
  //判断是否是初始化，以使组合框响应事件能够正确运行
  boolean isInitTable;
  boolean isInitItem;
  //定义二维数组sTable用于存储数据库表信息
  private String[][] sTable = {
      {
      "BuildInfo", "房产信息"}
      , {
      "HouseInfo", "住房信息"}
      , {
      "OwnerInfo", "房主基本信息"}
      , {
      "FamilyInfo", "房主家属信息"}
  };
  //定义二维数组sOper用于存储数据库表信息
  private String[][] sOper = {
      {
      "=", "等于"}
      , {
      ">", "大于"}
      , {
      "<", "小于"}
      , {
      "<>", "不等于"}
      , {
      "like", "类似"}
  };
  //定义二维数组sItem用于存储数据库表信息
  //意义依次为：表名；字段名；字段意义；代码表名
  private String[][] sItems = {
      {
      "BuildInfo", "Location", "坐落", ""}
      , {
      "BuildInfo", "Time", "建成年代", ""}
      , {
      "BuildInfo", "Usage", "房屋用途", "HouseUsage"}
      , {
      "BuildInfo", "Number", "房屋套数", ""}
      , {
      "BuildInfo", "Type", "房屋类型", "HouseType"}
      , {
      "BuildInfo", "Area", "占地面积", ""}
      , {
      "BuildInfo", "UseArea", "使用面积", ""}
      , {
      "HouseInfo", "BuildID", "房屋坐落", ""}
      , {
      "HouseInfo", "Name", "房间号", ""}
      , {
      "HouseInfo", "Heading", "朝向", "Heading"}
      , {
      "HouseInfo", "Number", "间数", ""}
      , {
      "HouseInfo", "UseType", "使用方式", "UseType"}
      , {
      "HouseInfo", "BuyTime", "购房时间", ""}
      , {
      "HouseInfo", "BuyPrice", "购房价格", ""}
      , {
      "HouseInfo", "Area", "面积", ""}
      , {
      "HouseInfo", "YTArea", "阳台面积", ""}
      , {
      "HouseInfo", "OwnerID", "住户", ""}
      , {
      "OwnerInfo", "Name", "姓名", ""}
      , {
      "OwnerInfo", "Gender", "性别", "Gender"}
      , {
      "OwnerInfo", "Birthday", "出生年月", ""}
      , {
      "OwnerInfo", "WorkTime", "参加工作时间", ""}
      , {
      "OwnerInfo", "Org", "工作单位", ""}
      , {
      "FamilyInfo", "OwnerID", "房主姓名", ""}
      , {
      "FamilyInfo", "Name", "家属姓名", ""}
      , {
      "FamilyInfo", "Gender", "性别", "Gender"}
      , {
      "FamilyInfo", "Relation", "与房主关系", "Relation"}
      , {
      "FamilyInfo", "Birthday", "出生年月", ""}
      , {
      "FamilyInfo", "WorkTime", "参加工作时间", ""}
  };

  JButton btnExit = new JButton();
  JLabel jLabel8 = new JLabel();
  JScrollPane jScrollPane1 = new JScrollPane();
  JLabel jLabel2 = new JLabel();
  JButton btnView = new JButton();
  JComboBox cboTable = new JComboBox();
  JComboBox cboOper = new JComboBox();
  JButton btnQuery = new JButton();
  JLabel jLabel11 = new JLabel();
  JPanel jPanel1 = new JPanel();
  JComboBox cboValue = new JComboBox();
  XYLayout xYLayout2 = new XYLayout();
  JLabel jLabel10 = new JLabel();
  JComboBox cboItem = new JComboBox();
  XYLayout xYLayout1 = new XYLayout();

  public Query() {
    try {
      jbInit();
      initForm();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  public Query(String sql) {
    try {
      jbInit();
      initForm();
      curTable = "FamilyInfo";
      resultList(sql);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  void jbInit() throws Exception {
    jLabel8.setText("信息集：");
    jLabel8.setRequestFocusEnabled(true);
    jLabel8.setFont(new java.awt.Font("宋体", 0, 12));
    btnExit.setText("退出");
    btnExit.addActionListener(new Query_btnExit_actionAdapter(this));
    btnExit.setFont(new java.awt.Font("宋体", 0, 12));
    this.getContentPane().setLayout(xYLayout1);
    jLabel2.setFont(new java.awt.Font("宋体", 0, 12));
    jLabel2.setText("操作符：");
    btnView.setText("查看");
    btnView.addActionListener(new Query_btnView_actionAdapter(this));
    btnView.setFont(new java.awt.Font("宋体", 0, 12));
    cboTable.setFont(new java.awt.Font("宋体", 0, 12));
    cboTable.setEditable(false);
    cboTable.addActionListener(new Query_cboTable_actionAdapter(this));
    cboOper.setFont(new java.awt.Font("宋体", 0, 12));
    cboOper.setEditable(false);
    btnQuery.setFont(new java.awt.Font("宋体", 0, 12));
    btnQuery.setText("查询");
    btnQuery.addActionListener(new Query_btnQuery_actionAdapter(this));
    jLabel11.setFont(new java.awt.Font("宋体", 0, 12));
    jLabel11.setText("查询值：");
    jPanel1.setBorder(BorderFactory.createEtchedBorder());
    jPanel1.setLayout(xYLayout2);
    cboValue.setFont(new java.awt.Font("宋体", 0, 12));
    cboValue.setEditable(true);
    jLabel10.setText("查询项：");
    jLabel10.setFont(new java.awt.Font("宋体", 0, 12));
    cboItem.setFont(new java.awt.Font("宋体", 0, 12));
    cboItem.setEditable(false);
    cboItem.addActionListener(new Query_cboItem_actionAdapter(this));
    xYLayout1.setWidth(434);
    xYLayout1.setHeight(325);
    this.setState(Frame.NORMAL);
    this.setTitle("住户信息查询");
    tblResult.setFont(new java.awt.Font("宋体", 0, 12));
    tblResult.setVerifyInputWhenFocusTarget(false);
    jPanel1.add(jLabel8, new XYConstraints(12, 18, 68, -1));
    jPanel1.add(jLabel2, new XYConstraints(12, 57, 50, -1));
    jPanel1.add(jScrollPane1, new XYConstraints(13, 96, 366, 132));
    jScrollPane1.getViewport().add(tblResult, null);
    jPanel1.add(cboItem, new XYConstraints(235, 14, 136, -1));
    jPanel1.add(cboTable, new XYConstraints(62, 13, 112, -1));
    jPanel1.add(jLabel10, new XYConstraints(182, 18, 50, -1));
    jPanel1.add(cboOper, new XYConstraints(62, 52, 111, -1));
    jPanel1.add(jLabel11, new XYConstraints(182, 57, 50, -1));
    jPanel1.add(cboValue, new XYConstraints(234, 51, 138, -1));
    jPanel1.add(btnView, new XYConstraints(253, 243, 61, 27));
    jPanel1.add(btnExit, new XYConstraints(318, 243, 61, 27));
    jPanel1.add(btnQuery, new XYConstraints(14, 243, 61, 27));
    this.getContentPane().add(jPanel1, new XYConstraints(16, 16, 400, 289));
  }

//初始化窗体
  void initForm() {
    isInitTable = true;
    //初始化数据库表信息
    for (int i = 0; i < sTable.length; i++) {
      cboTable.addItem(sTable[i][1]);
      linkTable.add(sTable[i][0]);
    }
    //初始化操作符
    for (int i = 0; i < sOper.length; i++) {
      cboOper.addItem(sOper[i][1]);
      linkOper.add(sOper[i][0]);
    }
    //设置选定项为空
    cboTable.setSelectedItem(null);
    cboOper.setSelectedIndex(0);
    isInitTable = false;
  }

//查询按钮响应事件
  void btnQuery_actionPerformed(ActionEvent e) {
    String sql = new String();
    //判断查询条件是否完整
    if (cboTable.getSelectedIndex() < 0 || cboItem.getSelectedIndex() < 0 ||
        (cboValue.getSelectedIndex() < 0 &&
         cboValue.getEditor().getItem().toString().equals(""))) {
      CommonDialog.showDialog(CommonDialog.OK, "房产管理系统", "请构建完整的查询条件！");
      return;
    }
    //调用函数createSql构造查询语句
    sql = createSql();
    //调用函数resultList，传入查询语句显示结果
    resultList(sql);
  }

//构造查询语句
  String createSql() {
    String sql = "";
    //用于存储查询值
    String value = "";
    String queryValue = "";
    //基本语句
    sql = "select * from " + curTable + " where " + curItem + " " +
        linkOper.get(cboOper.getSelectedIndex());
    //代码值
    if (linkCode.size() > 0) {
      value = linkCode.get(cboValue.getSelectedIndex()).toString();
    } //对坐落和房主姓名，根据描述得到ID值
    else if (curItem.equals("BuildID")) {
      value = db.getByField("BuildInfo", "ID", "Location='" +cboValue.getEditor().getItem().toString() + "'");
    }
    else if (curItem.equals("OwnerID")) {
      value = db.getByField("OwnerInfo", "ID","Name='" + cboValue.getEditor().getItem().toString() + "'");
    }
    else {
      value = cboValue.getEditor().getItem().toString();
    }
    //对like操作做特殊处理
    if (linkOper.get(cboOper.getSelectedIndex()).equals("like")) {
      queryValue = " '%" + value + "%'";
    }
    else {
      queryValue = "'" + value + "'";
    }
    //生成最终SQL语句
    sql += queryValue;
    return sql;
  }
//显示查询结果
  void resultList(String sql) {
    Vector tempvector;
    //根据不同的查询信息初始化不同的表头信息