Creating variable sql statements possibly with empty values?

huangapple go评论38阅读模式
英文:

Creating variable sql statements possibly 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.

我正在使用Java的JDBC驱动程序来创建Postgresql的PreparedStatement。

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

我有一个名为listings的表,其中包含类似listing_titlelisting_descriptionlisting_location等列。

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.

我正在编写一个findListings()方法,它将使用各种不同的过滤器,这些过滤器由像String titleString description等变量声明。这些变量可能为null,在这种情况下,我不想在SQL语句中使用过滤器来筛选这些行。

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

为此,我目前有一长串的if-else语句,不断检查过滤器以添加WHERE .... = ...AND语句,并连接字符串以获取语句。

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 doesn't help, just using it as an example in this case)

我想知道是否有一种更简单的方法,一种像事先将所有可能的过滤器放入语句中而不使用像ANY这样的关键字的方法(我知道ANY关键字不起作用,只是在这种情况下用它作为示例)。

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

可能的语句可能是: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 way.

我使用了许多if-else语句来连接字符串以生成语句,我正在寻找一种更简单的方法。

英文:

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.

答案1

得分: -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.

英文:

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.

答案2

得分: -1

如我在评论中所说,“筛选一些数据库行”比精确的字符串匹配复杂得多。您可能希望根据时间戳列匹配年份X,或者匹配以“foo”开头的名称等等。

首先,我们抽象出“我想要筛选的值的性质”的概念:

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

// 不确定您是否需要这个,但只是为了展示这样一个系统可以多么灵活:
@lombok.Value
class DummyMatch FilterValue {
  @Override
  public void apply(String colName, StringBuilder query, List<Object> params) {} // 故意什么也不做
}

您可以考虑其他许多筛选方式 - 正则表达式匹配、字符串以某内容开头等等。在这里编写这些应该相当简单。

接下来,我们编写一个系统,它将列与筛选值的映射转换为查询:

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()) {
        // 在这里处理结果
      }
    }
  }
}

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);
    // 还有很多其他类型
    default -> throw new IllegalArgumentException("无法将此类型放入 PreparedStatement:" + arg.getClass());
  }
}

您现在正在编写一个小型库,它很快就会扩展成一个更大的库,而且在某些时候,您可能会后悔没有选择一个现成的解决方案,它可以为您处理所有这些工作 - 可以考虑查看JOOQJDBI,它们可以为您提供更多功能。

英文:

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.

huangapple
  • 本文由 发表于 2023年5月14日 22:30:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/76247995.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定