java - 使用多个 JTextfield 搜索 JTable

Question

我有一个 JFrame，它有 3 个 JTextfields 和 2 个 JDatechooser，我想要做的是如果只有一个 JTextfield 输入了一些内容，然后我按下搜索按钮，然后我将能够将数据检索到 JTable，但问题是我必须填写所有 JTextFileds 和 JDatechooser 以检索数据。我的想法是忽略 null JTextfields 和 JTdatechooser 如果只有一个 JTextfield 具有我想要的关键字？有什么建议 ？？提前致谢，

public ArrayList<BillsRecord> getBillRecordByID(int EmpCode, String Fname, String Lname, String sDate, String eDate) throws SQLException {

    String sql = "SELECT B.DATE AS DT, B.EMP_ID, E.FNAME, E.LNAME, MONEY_SENT, RENT, PHONE, GAS, ELECTRICITY, INTERNET, OTHER"
            + " FROM EMPLOYEE E INNER JOIN BILLS B ON E.EMP_ID = B.EMP_ID"
            + " WHERE B.EMP_ID = ? "
            + " OR E.FNAME = ? "
            + " OR E.LNAME = ? "
            + " OR DATE BETWEEN ? AND ? "
            + " ORDER BY B.DATE";
    DBConnection con = new DBConnection();
    Connection connect = con.getConnection();
    PreparedStatement ps = null;

    ArrayList<BillsRecord> records = new ArrayList<>();

    try {

        ps = connect.prepareStatement(sql);

        ps.setInt(1, EmpCode);
        ps.setString(2, Fname);
        ps.setString(3, Lname);
        ps.setString(4, sDate);
        ps.setString(5, eDate);

        ResultSet rs = ps.executeQuery();

        if (rs.next()) {

            BillsRecord billrec = new BillsRecord();

            billrec.setDATE(rs.getString("DT"));
            billrec.setEMP_ID(rs.getInt("EMP_ID"));
            billrec.setFNAME(rs.getString("FNAME"));
            billrec.setLNAME(rs.getString("LNAME"));
            billrec.setMONEY_SENT(rs.getDouble("MONEY_SENT"));
            billrec.setRENT(rs.getDouble("RENT"));
            billrec.setPHONE(rs.getDouble("PHONE"));
            billrec.setGAS(rs.getDouble("GAS"));
            billrec.setELECTRICITY(rs.getDouble("ELECTRICITY"));
            billrec.setINTERNET(rs.getDouble("INTERNET"));
            billrec.setOTHER(rs.getDouble("OTHER"));

            records.add(billrec);
            return records;
        }

    } catch (SQLException e) {
        System.out.println(e.toString());

    } finally {
        if (ps != null) {
            ps.close();
        }

        if (connect != null) {
            connect.close();

        }

    }
    return null;
}

<小时/>

private void search() {

        try {
            JTextField stxt = ((JTextField) startdatetxt.getDateEditor().getUiComponent());
            String sDATE = stxt.getText().trim();

            JTextField etxt = ((JTextField) enddatetxt.getDateEditor().getUiComponent());
            String eDATE = etxt.getText().trim();

            int EMP_ID = Integer.parseInt(this.empidtxt.getText().trim());
            String FNAME = this.firstnametxt.getText().trim();
            String LNAME = this.lastnametxt.getText().trim();

            BillRecordDao billrecdao = new BillRecordDao();

            ArrayList<BillsRecord> records = billrecdao.getBillRecordByID(EMP_ID, FNAME, LNAME, sDATE, eDATE);

            Object[] tableColumnName = new Object[11];

            tableColumnName[0] = "Date";
            tableColumnName[1] = "H.License";
            tableColumnName[2] = "First Name";
            tableColumnName[3] = "Last Name";
            tableColumnName[4] = "MONEY SENT";
            tableColumnName[5] = "RENT";
            tableColumnName[6] = "PHONE";
            tableColumnName[7] = "GASE";
            tableColumnName[8] = "ELECTRICITY";
            tableColumnName[9] = "INTERNET";
            tableColumnName[10] = "OTHER";

            DefaultTableModel tbd = new DefaultTableModel();

            tbd.setColumnIdentifiers(tableColumnName);

            this.BillsSummaryTable.setModel(tbd);

            Object[] RowRec = new Object[11];

            for (int i = 0; i < records.size(); i++) {

                RowRec[0] = records.get(i).getDATE();
                RowRec[1] = records.get(i).getEMP_ID();
                RowRec[2] = records.get(i).getFNAME().toUpperCase();
                RowRec[3] = records.get(i).getLNAME().toUpperCase();
                RowRec[4] = records.get(i).getMONEY_SENT();
                RowRec[5] = records.get(i).getRENT();
                RowRec[6] = records.get(i).getPHONE();
                RowRec[7] = records.get(i).getGAS();
                RowRec[8] = records.get(i).getELECTRICITY();
                RowRec[9] = records.get(i).getINTERNET();
                RowRec[10] = records.get(i).getOTHER();

                tbd.addRow(RowRec);
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        }

    }

Answer 1

基本上，您需要根据可用值创建变量/动态查询

现在，您可以使用 StringBuilder 之类的方法来完成此操作，甚至可以将每个查询元素存储在 List 或数组中，但最终总是会得到“尾随 OR”问题(您需要知道何时到达最后一个元素，而不是将“OR”附加到String或从结果String中删除尾随的“OR” )。虽然不难，但只是很痛苦。

但是，如果您使用的是 Java 8，则可以使用 StringJoiner!

    StringJoiner sj = new StringJoiner(" OR ");
    String sql = "SELECT B.DATE AS DT, B.EMP_ID, E.FNAME, E.LNAME, MONEY_SENT, RENT, PHONE, GAS, ELECTRICITY, INTERNET, OTHER"
                    + " FROM EMPLOYEE E INNER JOIN BILLS B ON E.EMP_ID = B.EMP_ID"
                    + " WHERE ";
    List values = new ArrayList();
    // EmpCode MUST be a Integer, so it can be null
    if (EmpCode != null) {
        sj.add("B.EMP_ID = ?");
        values.add(EmpCode);
    }
    if (FName != null) {
        sj.add("E.FNAME = ?");
        values.add(FName);
    }
    if (LName != null) {
        sj.add("E.LNAME = ?");
        values.add(LName);
    }
    if (sDate != null && eDate != null) {
        sj.add("DATE BETWEEN ? AND ?");
        values.add(sDate);
        values.add(eDate);
    }

    sql += sj.toString();

    Connection connect = null;
    try (PreparedStatement ps = connect.prepareStatement(sql)) {

        for (int index = 0; index < values.size(); index++) {
            ps.setObject(index + 1, values.get(index));
        }

        try (ResultSet rs = ps.executeQuery()) {

            if (rs.next()) {
                //...
            }

        }

    } catch (SQLException exp) {
        exp.printStackTrace();
    }

您可能还想查看The try-with-resources Statement并通读 Code Conventions for the Java TM Programming Language ，这将使人们更容易阅读您的代码，也让您更轻松地阅读其他人