java - 如何在 SQL 字符串中插入多个 where 子句

Question

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

下面的代码查询一个属性(一个 WHERE 语句)，另一段代码查询 2 个属性(两个 WHERE 语句)。涉及很多硬编码。带有 queryString 的行是我遇到麻烦的地方。

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

给出

SELECT * FROM %s f WHERE 1 = 1 AND f.%s = @val1 AND f.%s = @val2
SELECT * FROM %s f WHERE 1 = 1 AND f.%s = @val1
SELECT * FROM %s f WHERE 1 = 1