当对应的JTextfield为空时如何填充JTable中的所有项目 [英] how to populate all items in JTable when corresponding JTextfield is empty

问题描述

我正在使用NetBeans中的sqlite在Java项目中设计一个高级搜索选项. 有5个不同的JTextfields和5列，我想用相应的匹配条件填充JTable.如果JTextfield为空，则应选择该列的所有项目.

I am designing a advanced search option in a Java project with sqlite in NetBeans. There are 5 different JTextfields and 5 columns, I want to populate JTable with corresponding matching criteria. If a JTextfield is empty then it should select all items of that column.

我正在使用的查询是:

String val1 = txt_billing2.getText();
String val2 = txt_station2.getText();
String val3 = txt_invoice2.getText();
String val4 = txt_amonth2.getText();
String val5 = txt_umonth2.getText();

String sql = "SELECT * from airindia_sqlite WHERE BILLING = '"+val1+"' and STATION =    '"+val2+"' and INVOICE = '"+val3+"' and AMONTH = '"+val4+"' and UMONTH = '"+val5+"'";

pst = conn.prepareStatement(sql);
rs = pst.executeQuery();

Table_airindia.setModel(DbUtils.resultSetToTableModel(rs));

但是当我将JTextfield留空时，它在JTable中没有显示任何数据. 我知道的唯一方法是使用if else条件，但是会生成5！= 120个条件.

But when i leave a JTextfield empty it shows no data in JTable. Only method I know is to use if else conditions but that generates 5!= 120 conditions.

推荐答案

首先请注意，您的查询容易受到 SQL注入攻击.为避免这些问题，您需要使用 PreparedStatement 正确地:

First off please note your query is vulnerable to SQL injection attacks. To avoid these issues you need to use PreparedStatement properly:

String val1 = txt_billing2.getText();
...
String val5 = txt_umonth2.getText();

String sql = "SELECT * from airindia_sqlite WHERE BILLING = ? and STATION = ? and INVOICE = ? and AMONTH = ? and UMONTH = ?";
ps = con.prepareStatement(sql);
ps.setObject(1, val1);
...
ps.setObject(5, val5);

话虽如此，我将使用辅助类编写给定许多参数的WHERE子句.像这样:

Having said this, I'd use an auxiliar class to write WHERE clause given a number of parameters. Something like:

public class WhereClause {

    private Integer currentIndex = 0;
    private Map<Integer, Object> parameters = new HashMap<>();
    private StringBuilder whereClause = null;

    public void addParameter(String columnName, Object value) {
        if(whereClause == null) {
            whereClause = new StringBuilder(" WHERE ");
        } else if (currentIndex > 0) {
            whereClause.append(" AND ");
        }
        whereClause.append(columnName).append(" = ?");
        paramenters.put(++currentIndex, value);
    }

    public String getWhereClause() {
        return whereClause != null ? whereClause.toString() : "";
    }

    public Map<Integer, Object> getParamenters() {
        return parameters;
    }
}

然后，您可以执行以下操作以获取适当的SQL语句:

Then you could do something like this to get the appropriate SQL statement:

WhereClause whereClause = new WhereClause();

if(!(txt_billing2.getText().trim().isEmpty())) {
    whereClause.addParameter("BILLING", txt_billing2.getText().trim());
}

...

if(!(txt_umonth2.getText().trim().isEmpty())) {
    whereClause.addParameter("UMONTH ", txt_umonth2.getText().trim());
}

String sql = "SELECT * FROM airindia_sqlite" + whereClause.getWhereClause();
ps = con.prepareStatement(sql);

Map<Integer, Object> parameters = whereClause.getParameters();
for (Integer key : parameters.keySet()) {
    ps.setObject(key, parameters.get(key));
}

rs = ps.executeQuery();

---

题外话

当心数据库调用是耗时的任务，可能会阻塞事件调度线程(又名EDT)导致GUI停止响应. EDT是一个单独的特殊线程，在该线程中可以创建和更新Swing组件.为避免阻塞该线程，请考虑使用 SwingWorker 在后台线程中执行数据库调用并更新EDT中的Swing组件.有关更多信息，请参见 Swing跟踪中的并发性.

---

Off-topic

Beware database calls are time consuming tasks and may block the Event Dispatch Thread (a.k.a. EDT) causing the GUI become unresponsive. The EDT is a single and special thread where Swing components creation and update take place. To avoid block this thread consider use a SwingWorker to perform database calls in a background thread and update Swing components in the EDT. See more in Concurrency in Swing trail.

这篇关于当对应的JTextfield为空时如何填充JTable中的所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！