how to populate all items in JTable when corresponding JTextfield is empty

Question

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.

Query I was using is:

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));

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.

Answer 1

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);

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;
    }
}

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();

---

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.