querydb.java

物业管理系统

package wuYeGuanLi;

import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;
import java.io.*;

//import javax.swing.JComboBox;
//import java.awt.GridBagConstraints;

public class QueryDB
    extends JFrame
    implements ActionListener {

  private JComboBox yeZhu;
  private JComboBox buildingNumber;
  private JComboBox roomNumber;
  private JComboBox yueFen;
  private JTextField priceChange;
  private JTextArea result;
  private Connection con;
  private Statement stmt;
  private PreparedStatement authorQueryStmt;
  private PreparedStatement authorPublisherQueryStmt;
  private PreparedStatement publisherQueryStmt;
  private PreparedStatement allQueryStmt;
  JButton queryButton;

  public static void main(String[] args) {
    QueryDB qu = new QueryDB();
    qu.setVisible(true);
  }

  public QueryDB() {
    setTitle("QueryDB");
    setSize(600, 400);
    /*addWindowListener(new WindowAdapter() {
      public void windowClosing(WindowEvent e) {
        System.exit(0);
      }
         });*/

    this.getContentPane().setLayout(new GridBagLayout());
    GridBagConstraints gbc = new GridBagConstraints();

    yeZhu = new JComboBox();
    yeZhu.setEditable(false);
    yeZhu.addItem("选择姓名");

    buildingNumber = new JComboBox();
    buildingNumber.setEditable(false);
    buildingNumber.addItem("选择楼号");

    roomNumber = new JComboBox();
    roomNumber.setEditable(false);
    roomNumber.addItem("选择房间号");

    result = new JTextArea(8, 100);
    result.setEnabled(true);

    priceChange = new JTextField(8);
    priceChange.setText("");

    try {
      con = getConnection("wang", "");
      stmt = con.createStatement();

      String query = "SELECT 姓名 FROM yeZhu";
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        yeZhu.addItem(rs.getString(1));
      }
      query = "SELECT 楼号 FROM yeZhu";
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        buildingNumber.addItem(rs.getString(1));
      }
      query = "SELECT 房间号 FROM yeZhu";
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        roomNumber.addItem(rs.getString(1));
      }
    }
    catch (Exception e) {
      result.setText("Error " + e);
    }

    gbc.fill = GridBagConstraints.NONE;
    gbc.weightx = 1;
    gbc.weighty = 1;
    add(yeZhu, gbc, 0, 0, 2, 1);
    add(buildingNumber, gbc, 1, 0, 2, 1);
    add(roomNumber, gbc, 3, 0, 2, 1);

    gbc.fill = GridBagConstraints.NONE;
    queryButton = new JButton("query");
    queryButton.addActionListener(this);
    add(queryButton, gbc, 3, 1, 1, 1);

    JLabel changeButton = new JLabel("输入月份");
    //changeButton.addActionListener(this);
    add(changeButton, gbc, 0, 1, 1, 1);

    gbc.fill = GridBagConstraints.HORIZONTAL;
    add(priceChange, gbc, 2, 1, 1, 1);

    gbc.fill = GridBagConstraints.BOTH;
    add(result, gbc, 0, 2, 4, 1);
  }

  public static Connection getConnection(String user, String password) throws
      SQLException, IOException {
    String url = "jdbc:odbc:wuYe";
    String tempUser = user; //用户名
    String tempPassword = password; //密码
    Connection con;
    //实例化一个Connection对象
    try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      //加载jdbc-odbc桥驱动
    }
    catch (java.lang.ClassNotFoundException e) {
      System.err.print("ClassNotFoundException: ");
      //加载jdbc-odbc桥错误
      System.err.println(e.getMessage());
      //其他错误
    }
    try {
      con = DriverManager.getConnection(url, tempUser, tempPassword);
      //上面的语句关闭声明和连接
    }
    catch (SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());
      //显示数据库连接错误或者查询错误
    }
    return DriverManager.getConnection(url, tempUser, tempPassword);
  }

  private void add(Component c, GridBagConstraints gbc,
                   int x, int y, int w, int h) {
    gbc.gridx = x;
    gbc.gridy = y;
    gbc.gridwidth = w;
    gbc.gridheight = h;
    getContentPane().add(c, gbc);
  }

  /**
   * actionPerformed
   *
   * @param e ActionEvent
   */
  /* public void actionPerformed(ActionEvent et) {
     String arg = et.getActionCommand();
     if (arg.equals("Query")) {
       ResultSet rs = null;
       try {
         String author
             = (String) yeZhu.getSelectedItem();
         String publisher
             = (String) roomNumber.getSelectedItem();
         if (!author.equals("查询所有")
             && !publisher.equals("查询所有")) {
           if (authorPublisherQueryStmt == null) {
             String authorPublisherQuery =
                 "SELECT * " +
                 "FROM building ";
             authorPublisherQueryStmt
                 = con.prepareStatement(authorPublisherQuery);
           }
           authorPublisherQueryStmt.setString(1, author);
           authorPublisherQueryStmt.setString(2,
                                              publisher);
           rs = authorPublisherQueryStmt.executeQuery();
         }
         else if (!author.equals("查询所有")
                  && publisher.equals("查询所有")) {
           if (authorQueryStmt == null) {
             String authorQuery =
                 "SELECT Books.Price, Books.Title " +
                 "FROM Books, BooksAuthors, Authors " +
                 "WHERE Authors.Author_Id = BooksAuthors.Author_Id AND " +
                 "BooksAuthors.ISBN = Books.ISBN AND " +
                 "Authors.Name = ?";
             authorQueryStmt
                 = con.prepareStatement(authorQuery);
           }
           authorQueryStmt.setString(1, author);
           rs = authorQueryStmt.executeQuery();
         }
         else if (author.equals("查询所有")
                  && !publisher.equals("查询所有")) {
           if (publisherQueryStmt == null) {
             String publisherQuery =
                 "SELECT Books.Price, Books.Title " +
                 "FROM Books, Publishers " +
                 "WHERE Books.Publisher_Id = Publishers.Publisher_Id AND " +
                 "Publishers.Name = ?";
             publisherQueryStmt
                 = con.prepareStatement(publisherQuery);
           }
           publisherQueryStmt.setString(1, publisher);
           rs = publisherQueryStmt.executeQuery();
         }
         else {
           if (allQueryStmt == null) {
             String allQuery =
                 "SELECT Books.Price, Books.Title FROM Books";
             allQueryStmt
                 = con.prepareStatement(allQuery);
           }
           rs = allQueryStmt.executeQuery();
         }

         result.setText("");
         while (rs.next()) {
           result.append(rs.getString(1)
                         + " | " + rs.getString(2) + "\n");
         }
         rs.close();
       }
       catch (Exception e) {
         result.setText("Error " + e);
       }
     }
     else if (arg.equals("update")) {
       String publisher
           = (String) roomNumber.getSelectedItem();
       if (publisher.equals("查询所有")) {
         result.setText
             ("I am sorry, but I cannot do that.");
       }
       else {
         try {
           String updateStatement =
               "UPDATE Books " +
               "SET Price = Price + " + priceChange.getText() +
               " WHERE Books.Publisher_Id = " +
               "(SELECT Publisher_Id FROM Publishers WHERE Name = '" +
               publisher + "')";
           int r = stmt.executeUpdate(updateStatement);
           result.setText(r + " records updated.");
         }
         catch (Exception e) {
           result.setText("Error " + e);
         }
       }
     }
   }*/
  public void dispose() {
    try {
      stmt.close();
      con.close();
    }
    catch (SQLException e) {}
  }

  /**
   * actionPerformed
   *
   * @param e ActionEvent
   */
  public void actionPerformed(ActionEvent e) {
    if (e.getSource() == queryButton) {
      try {
        String a
            = (String) yeZhu.getSelectedItem();
        String b
            = (String) buildingNumber.getSelectedItem();
        String c
            = (String) roomNumber.getSelectedItem();
        String stm
            =
            "SELECT 姓名,楼号,房间号,水费,电费,煤气费,供暖费,物业管理费,分期付款费,非日常维护费用,合计 FROM yueHeJi,yeZhu " +
            "WHERE yeZhu.业主代码 = yueHeJi.业主代码" +
            " AND yeZhu.姓名 = '" + a +
            "' AND yeZhu.楼号 = '" + b +
            "' AND yeZhu.房间号 = " + c +
            " AND yueHeJi.月份 = " + priceChange.getText();
        ResultSet rs = stmt.executeQuery(stm);
        result.setText("");
        while (rs.next()) {
          //result.setText("姓名");
          result.append("姓名： " + rs.getString(1) + "\n");
          //result.setText("楼号: ");
          result.append("楼号： " + rs.getString(2) + "\n");
          //result.setText("房间号: ");
          result.append("房间号： " + rs.getString(3) + "\n");
          //result.setText("水费: ");
          result.append("水费： " + rs.getString(4) + "\n");
          //result.setText("电费: ");
          result.append("电费： " + rs.getString(5) + "\n");
          //result.setText("煤气费: ");
          result.append("煤气费： " + rs.getString(6) + "\n");
          //result.setText("供暖费: ");
          result.append("供暖费： " + rs.getString(7) + "\n");
          //result.setText("物业管理费: ");
          result.append("物业管理费： " + rs.getString(8) + "\n");
          //result.setText("分期付款费: ");
          result.append("物业管理费： " + rs.getString(9) + "\n");
          //result.setText("非日常维护费用: ");
          result.append("非日常维护费用： " + rs.getString(10) + "\n");
          //result.setText("合计: ");
          result.append("合计: " + rs.getString(11) + "\n");
        }
        rs.close();
      }
      catch (Exception ee) {
        result.setText("Error " + ee);
        result.setText("你输入信息有误或者没有您查找的用户");
      }
    }
  }

}