JbdcTemplate - PreparedStatements with Dynamic SQL Query

Question

I know jdbcTemplate can be used to create PreparedStatements if you set it up to do so:

i.e.

private JdbcTemplate jdbcTemplate;
String sqlQuery = "Select * from table where column = ?";
String value = "value";
this.jbdcTemplate.query(sqlQuery, new Object[] { value }, rs, rowNum -> {
  System.out.println(rs.getString("column"));
});

However, I have a query with many AND operators in the Where clause, and depending on some condition, a specific AND statement may or may not be added to the query string.

i.e.

private JdbcTemplate jdbcTemplate;
StringBuilder sqlQuery = new StringBuilder("Select * from table where 1=1 "); //base query
if(someCondition)
   sqlQuery.append("And column1 = '" + value1 + "'");
if(someCondition2)
   sqlQuery.append("And column2 = '" + value2 + "'");
if(someCondition3)
   sqlQuery.append("And column3 = '" + value3 + "'");
//etc...

With this type of dynamic query, I am unable to know exactly how many values to place in the new Object[] {} field at compile time, because the someCondition fields are always changing at runtime.

Is there a way to write the this.jdbcTemplate.query(sqlQuery.toString(), new Object[] {}... to accommodate these dynamic AND statements?

Answer 1

I found a solution after taking into consideration what @mustaccio said in his comment to my original question. I also took part of the solution from this stackoverflow question and used it in my own solution.

The main issue I was having was dynamically creating an Object[] array at runtime, since you can't dynamically add elements to an Object[] array. They must have a defined size when initialized.

First, I create an arraylist of strings called queryArgs. Every time one of the if conditions proves true and we add an AND statement to the query, I also add another line of code that adds the value to be plugged in the preparedStatement to the queryArgs arraylist. Once that's done, I create a new Object[] array whose size is initialized to the size of the queryArgs arraylist. Lastly, I loop through each element in the Object[] array, setting them equal to the values in queryArgs.

private JdbcTemplate jdbcTemplate;

List<QueryResults> jdbcQuery(QueryParams queryParams) {
    /* base query */
    StringBuilder sqlQuery = new StringBuilder("Select * from table where 1=1 ");
    /* stores the dynamic preparedStatement arguments */
    List<String> queryArgs = new ArrayList<>();

    if(someCondition){
       sqlQuery.append("And column1 = ? ");
       queryArgs.add(queryParams.value1);
    }
    if(someCondition2){
       sqlQuery.append("And column2 = ? ");
       queryArgs.add(queryParams.value2);
    }
    if(someCondition3){
       sqlQuery.append("And column3 = ? ");
        queryArgs.add(queryParams.value3);
    }
    //etc...

    /* this is the part I used from the above stackoverflow question */
    Object[] preparedStatementArgs = new Object[queryArgs.size()];
    for(int i = 0; i < preparedStatementArgs.length; i++){
        preparedStatementArgs[i] = queryArgs.get(i);
    }

    /* Lastly, execute the query */
    return this.jdbcTemplate.query(sqlQuery.toString(),
    preparedStatementArgs, (rs, rowNum) -> {

        QueryResults result = new QueryResults();
        /* store the results of the query... */
    });
}

The outlier is that one of the dynamic AND statements above is written like this:

AND column4 IN ('x','y','z','etc..'‌​)

, where the values inside of the parentheses are also dynamic at runtime. My service receives a string value that looks like this:

String queryParams.value4 = "x,y,z,etc...";

I can't write the preparedStatement like this: AND column4 IN (?) and then simply plug in queryParams.value4 because it will treat queryParams.value4 as a string literal, which leads to errors.

To solve this issue, I create another arraylist of strings called value4Array. I loop through every character in queryParams.value4, and I check if the current character in the loop is equal to a comma, our delimiter. If it is, then I create a substring of all the characters leading up to that comma, and add that newly created string to value4Array.

The next step is to create the dynamic AND column4 IN (?) statement. I do this by looping through each string value in the value4Array arraylist we just created, and doing a sql.append("?"), based on how many strings are in value4Array. After this, the rest of the logic is the same as my solution above.

/* this function takes the comma delimited string literal (value4 : "x,y,z,etc...")
   and parses it into an array of strings. */
private List<String> parseValue4(String value4){
    int valueIndex= 0;
    List<String> value4Array = new ArrayList<>();
    for(int i = 0; i < value4.length(); i++){
        if(value4.charAt(i) == ','){
            value4Array.add(value4.substring(valueIndex, i));
            valueIndex = i + 1;
        }
        else if(i == value4.length() - 1){
            value4Array.add(value4.substring(valueIndex, value4.length()));
        }
    }
    return value4Array;
}
if(someCondition4){
   List<String> value4Array = parseValue4(queryParams.value4);
   sqlQuery.append("And column4 IN ("); /* base AND statement */
    for(int i = 0; i < value4Array.size(); i++){
        if(i == value4Array.size() - 1)
            sqlQuery.append("?)");
        else                       /* dynamically appending ?'s */
            sqlQuery.append("?,");
        queryArgs.add(value4Array.get(i));
    }
}