How to insert mutliple where clauses into an SQL string

19
June 12, 2019, at 10:00 AM

I'm using an Azure Cosmos NoSQL database with SQL syntax. How do I create an SQL string that can dynamically add more WHERE statements? For example, I want to be able to not have to code in "WHERE f.%s=@val" for one and then hardcode another function with "WHERE f.%s=@val1 AND f.%s=@val2" if I have two where statements. I want to have up to 7 WHERE statements.

Below I have code that queries for one property (one WHERE statement) and another one that queries for 2 properties (two WHERE statements). There is a lot of hard coding involved. The lines with queryString is where I'm having trouble.

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

You can create a bogus first condition and then only add AND ...

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

Here is a simple example of a query factory method

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

Example

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

gives

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

Answer 2

Try this use is null

SELECT * FROM %s f WHERE (@val1 is null or f.%s = @val1) AND (@val2 is null or f.%s = @val2) 
READ ALSO
Spring framework and Project Loom (Fibers) [on hold]

Spring framework and Project Loom (Fibers) [on hold]

Is there any plan to integrate Project Loom (Fibers) with Spring Framework?

35
How can I access &ldquo;message&rdquo; or &ldquo;added&rdquo;? It is in JSON format

How can I access “message” or “added”? It is in JSON format

I need to print "message" and "added" from this JSON in Java

40
How to configure NatTable FilterComboBox to have a different DataProvider for each column

How to configure NatTable FilterComboBox to have a different DataProvider for each column

In my NatTable I am using a ComboBoxFilterRowHeaderComposite similar to ExcelLikeFilterRowCustomTypesExampleIt works fairly well however the combo box filtering adds too many items in large tables to allow for easy filtering

39
How to pass data to BottomSheetDialogFragment?

How to pass data to BottomSheetDialogFragment?

I have a BottomSheetDialogFragment classLooks like this:

38