如何将多个 where 子句插入到 SQL 字符串中

Question

我正在使用具有SQL语法的Azure Cosmos NoSQL数据库。如何创建可以动态添加更多 WHERE 语句的 SQL 字符串？例如，如果我有两个 where 语句，我希望不必在"WHERE f.%s=@val"中编写代码，然后用"WHERE f.%s=@val1 AND f.%s=@val2"对另一个函数进行硬编码。我想要最多 7 个 WHERE 语句。

下面我有查询一个属性(一个 WHERE 语句(和另一个查询 2 个属性(两个 WHERE 语句(的代码。涉及很多硬编码。带有查询字符串的行是我遇到问题的地方。

public Iterable<Document> queryForOneProperty(String databaseName, String 
    collectionName, String key, String val) {
    FeedOptions queryOptions = getDefaultFeedOptions();
    String collectionLink = String.format("/dbs/%s/colls/%s", databaseName, collectionName);
    SqlParameterCollection paramCollection = new SqlParameterCollection();
    paramCollection.add(new SqlParameter("@val", val));
    SqlQuerySpec query = new SqlQuerySpec(String.format("SELECT * FROM %s f WHERE f.%s = @val", collectionName, key), paramCollection);
    return query;
}
public Iterable<Document> queryForTwoProperties(String databaseName, String 
    collectionName, String[] keys, String[] vals) {
    FeedOptions queryOptions = getDefaultFeedOptions();
    String collectionLink = String.format("/dbs/%s/colls/%s", databaseName, collectionName);
    SqlParameterCollection paramCollection = new SqlParameterCollection();
    paramCollection.add(new SqlParameter("@val1", vals[0]));
    paramCollection.add(new SqlParameter("@val2", vals[1]));
    String queryString = String.format("SELECT * FROM %s f WHERE f.%s = @val1 AND f.%s = @val2", collectionName, keys[0], keys[1]);
    SqlQuerySpec query = new SqlQuerySpec(queryString, paramCollection);
    return query;
}

'

''

Answer 1

您可以创建一个虚假的第一个条件，然后只添加 AND ...

String condition = "WHERE 1 = 1";
if (some logic) {
    condition += " AND x = 42";
}

下面是查询工厂方法的简单示例

private final static String BASE_QUERY = "SELECT * FROM %s f WHERE 1 = 1";
public static String queryBuilder(String ... conditions) {
    StringBuilder builder = new StringBuilder(BASE_QUERY);
    for (String condition : conditions) {
        builder.append(" ");
        builder.append(condition);      
     }
     return builder.toString();
}

例

System.out.println(queryBuilder("AND f.%s = @val1", "AND f.%s = @val2"));
System.out.println(queryBuilder("AND f.%s = @val1"));
System.out.println(queryBuilder());

给

从 %S F 中选择 *，其中 1 = 1 且 f.%s = @val1 且 f.%s = @val2
从 %S F 中选择 *，其中 1 = 1 且 f.%S = @val1
从 %S F 中选择 *，其中 1 = 1

Answer 2

试试这个用法是空

的

SELECT * FROM %s f WHERE (@val1 is null or f.%s = @val1) AND (@val2 is null or f.%s = @val2)