How to prepare an SQL query with optional clauses in java?

I encounter a little issue with the preparedStatement object in java. I have a GUI with several JTextField (and other thing), a JTable, and a button. The JTable is constructed using an SQL request on my database, something like this :

SELECT date, item, vendor FROM Sells;

I want to use the JTextFields to filter the results when the user click the button. Say we have a JTextField for the date, an other for the item, another for the vendor.

If all the fields are filled, I can use a prepared query, like this:

String query = "SELECT date, item, vendor FROM Sells WHERE date > ? AND item = ? AND vendor = ?";
PreparedStatement prepared = this.connect.prepareStatement(query);
prepared.setString(1, (String)dateField.getText());
prepared.setString(2, (String)itemField.getText());
prepared.setString(3, (String)vendorField.getText());

This (with some adjustements like a try/catch, variables declarations etc) works well. My issue is to manage the possibility for the user to filter using only a few fields (for example only the date, or the date and the item but not the vendors). For now, I can’t find an other way than:

1) test the content of each field with an if statement, and modify the “query” variable in consequence.

2) use another complicated if/else structure in order to determine which variables are to filter with, and which “prepared.set….” should be used. It’s tricky, since sometimes it will be a String, but sometimes an int or anything else. And of course, determine the index is also a little problem cause it wont be the same eveytime.

I feel like I’m missing something, and I shouldn’t use this weird solution with multiple if/else. Can anyone help me ? Is there a simpler solution, or is it just a situation where I have to do it the hard way ?

Many thanks!

Answer

Constructing the query statement with just the columns you need is probably the most efficient approach. But you can also do something like this:

WHERE (date > ? OR ? IS NULL) AND
      (item = ? OR ? IS NULL) AND
      (vendor = ? OR ? IS NULL)

This requires providing each parameter twice and assumes that the “empty” value is the SQL NULL.

This is a case where named parameters simplify things:

WHERE (date > :date OR :date IS NULL) AND
      (item = :item OR :item IS NULL) AND
      (vendor = :vendor OR :vendor IS NULL)

Advertisement

Answer