Creating variable sql statements possibly with empty values?

Question

I tried researching about this but couldn't find any answer probably because I don't really know how to tell what I'm looking for properly.

I am using JDBC driver of Java to create a PreparedStatement for Postgresql.

I have a table listings with columns like listing_title, listing_desription, listing_location etc.

I am working on a findListings() method that will work with varied filters, declared with variables like String title, String description etc. Those variables might be null and in those cases I don't want to use filter for those rows in the sql statement.

For this, I right now have a very long spagetti of if-else statements that check for filters continously to put WHERE .... = ... and AND statements and concatenate strings to get a statement.

I wonder if there is an easier way, a way like putting all possible filters to the statement beforehand and not using them with a keyword like ANY (I know ANY keyword doesnt help, just using it as an example in this case)

A possible statement would be: SELECT * FROM listings WHERE listing_title = 'title', listing description = ANY, ORDER BY ANY

Is there a keyword for this? Thanks a lot

I used many if else statements to concatenate strings to make the statement, I am looking for an easier to do way.

Answer 1

String concatenation to build SQL query is not a good solution. You are correct. Instead you can use IS NULL check for every of your filtering attribute in SQL call.

SELECT * FROM listings
WHERE
    (title_param IS NULL OR listings.listing_title = title_param)
    AND (description_param IS NULL OR listings.listing_description = description_param)

This SQL call will check if filter parameters values provided (title_param, description_param) and if they not (they are null in this case) then it will not use them as a filtering condition.

Java code would look something like:

String query = "SELECT * FROM listings WHERE (? IS NULL OR listings.listing_title = ?) AND (? IS NULL OR listings.listing_description = ?);";

stmt = conn.prepareStatement(query);
stmt.setString(1, titleParam);
stmt.setString(2, titleParam);
stmt.setString(3, descriptionParam);
stmt.setString(4, descriptionParam);

ResultSet rs = stmt.executeQuery();

Note PreparedStatement used here, but not popular-used Statement. This makes the code to be safe against SQL injection attacks. Also note building SQL queries with String concatenation is usually bad practice as it leads to bloated code, which way harder to test.

Update. Note if you use same technique: "param IS NULL" check but your param is a Collection or Array, this solution will likely fail and may be database - engine dependent. It is likely you will have to make a dynamic SQL statement in this case.