Question 168683 in PostgreSQL Question posted in
The official documentation can be found here.

Can Postgresql create variable SQL statements with empty values?

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    As I said in the comments, ‘filter some db rows’ is way more complicated than exact string matches. You may want to filter on specifically ‘anything in the year X’, matching X against a TIMESTAMP column. Or ‘anything where the name STARTS WITH "foo"’, and so on.

    So, first, we abstract the notion of ‘the nature of the value I want to filter on’:

    interface FilterValue {
 void apply(String colName, StringBuilder query, List<Object> params);
}

@lombok.Value
class ExactStringMatch implements FilterValue {
  private final String match;

  @Override
  public void apply(String colName, StringBuilder query, List<Object> params) {
    query.append("AND ").append(colName).append(" = ? ");
    params.add(match);
  }
}

@lombok.Value
class DateBoundedMatch implements FilterValue {
  private final LocalDate lower, upper;

  @Override
  public void apply(String colName, StringBuilder query, List<Object> params) {
    query.append("AND ").append(colName).append(" BETWEEN ? AND ? ");
    params.add(lower);
    params.add(upper);
  }
}

// Not sure you need this, but just to show how
// flexible such a system can be:
@lombok.Value
class DummyMatch FilterValue {
  @Override
  public void apply(String colName, StringBuilder query, List<Object> params) {} // do nothing - intentionally
}

    You can think of tens of other ones – regexp match, string-starts-with,
    and so on. Writing these should be fairly simple here.

    Next, we write a system that turns a map of column-to-filtervalue and makes a query out of it:

    void query(Map<String, FilterValue> filters) throws SQLException {
  try (Connection con = getConnection()) {
    var q = new StringBuilder();
    q.append("SELECT * FROM myTable WHERE true ");
    var args = new ArrayList<Object>();
    for (var e : filters.entrySet()) {
      e.getValue().apply(e.getKey(), q, args);
    }
    try (PreparedStatement ps = con.prepareStatement(q.toString())) {
      for (int i = 0; i < args.size(); i++) {
        applyArg(ps, i + 1, args.get(i));
      }
      try (ResultSet rs = ps.executeQuery()) {
        // process here
      }
    }
  }
}

void applyArg(PreparedStatement ps, int index, Object arg) throws SQLException {
  switch (arg) {
    case Integer i -> ps.setInt(index, i.intValue());
    case String s -> ps.setString(index, s);
    // lots more types go here
    default -> throw new IllegalArgumentException("Cannot put set type into PreparedStatement: " + arg.getClass());
  }
}

    You’re now writing a little library which will soon balloon into a much bigger library, and at some point you’re going to regret not just going with an existing solution that did all this – look into JOOQ or JDBI which do lots of this stuff for you and more.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search