1. 首页
  2. 问题列表
  3. 可能使用空值创建变量sql语句？
提问者：小点点

可能使用空值创建变量sql语句？


我试图对此进行研究，但找不到任何答案，可能是因为我真的不知道如何正确地说出我在寻找什么。

我使用Java的JDBC驱动程序创建Postgreql的准备语句。

我有一个表列表，其中包含listing_title、listing_desription、listing_location等列。

我正在研究一个findListings（）方法，该方法将与各种过滤器一起使用，使用String title，String描述等变量声明。这些变量可能是null，在这些情况下，我不想在sql语句中为这些行使用过滤器。

为此，我现在有一长串if-else语句，它们不断检查过滤器以放置WHERE……=…和AND语句并连接字符串以获取语句。

我想知道是否有一种更简单的方法，一种方法，比如事先将所有可能的过滤器放在语句中，而不是将它们与ANY这样的关键字一起使用（我知道ANY关键字没有帮助，在这种情况下只是将其用作示例）

一个可能的语句是：SELECT*from列表WHERElisting_title='title'，列表描述=ANY， ORDER BY ANY

这个有关键词吗？多谢

我使用了许多if else语句来连接字符串来制作语句，我正在寻找一种更简单的方法。


共2个答案

匿名用户

正如我在评论中所说，“过滤一些db行”比精确的字符串匹配要复杂得多。您可能希望专门过滤“X年的任何内容”，将X与TIMESTAMP列匹配。或者“名称以“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);
  }
}

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

你可以想到几十个其他的——正则表达式匹配、字符串开始等等。在这里编写这些应该相当简单。

接下来，我们编写一个系统，将列到过滤器值的映射转换为查询：

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

你现在正在编写一个小库，它很快就会膨胀成一个更大的库，在某个时候，你会后悔没有使用一个完成所有这些的现有解决方案——看看JOOQ或JDBI，它们为你做了很多这样的事情，甚至更多。

匿名用户

字符串连接来构建SQL查询不是一个好的解决方案。您是正确的。相反，您可以对调用中的每个过滤属性使用IS NULL检查SQL。

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)

此SQL调用将检查是否提供了过滤器参数值（title_param、description_param），如果没有（在本例中为空），则不会将它们用作过滤条件。

Java代码看起来像：

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

注意这里使用的是准备语句，但不是流行的语句。这使得代码可以安全地抵御SQL注入攻击。另请注意，使用字符串连接构建SQL查询通常是不好的做法，因为它会导致代码臃肿，这更难测试。

相关问题