1

JbdcTemplate - PreparedStatements with Dynamic SQL Query is exactly what I have done however jdbctemplate.query is depricated and you cannot pass it in an object instead you should use the varargs method. JdbcTemplate "queryForObject" and "query" is deprecated in Spring. What should it be replaced by?

But in terms of a dynamic query I have unknown number of parameters so what should I do in this case

FYI the code is working it is just deprecated.

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... */
    });
}

This is how the code is as of now

Zoe
  • 27,060
  • 21
  • 118
  • 148
Myvin Barboza
  • 43
  • 1
  • 7
  • Without knowing which method you are using it is hard to tell. But it looks like you should have been using the `query` method that takes a `PreparedStatementCreator` and `PreparedStatementSetter` from the start. – M. Deinum Mar 16 '21 at 07:21
  • Does this answer your question? [JdbcTemplate "queryForObject" and "query" is deprecated in Spring. What should it be replaced by?](https://stackoverflow.com/questions/65301011/jdbctemplate-queryforobject-and-query-is-deprecated-in-spring-what-should-i) – Zoe Mar 16 '21 at 07:34
  • As stated use the `query` method taking a `PreparedStatementCreator` and `PreparedStatementSetter` instead of what you have now. – M. Deinum Mar 16 '21 at 07:34
  • @Zoe no it is not what not I need – Myvin Barboza Mar 16 '21 at 07:37
  • 1
    It is. As outlined in the answer, the old functions were deprecated in favor of vararg equivalents. That means you need to change the order or the arguments. Varargs support a single array (in addition to any number of arguments, but not in combination; it's either an array or any number of arguments. You can google this separately), and varargs are _always_ last. All you need to do is move the `preparedStatementArgs` to be the last argument instead of the second. – Zoe Mar 16 '21 at 07:43
  • Just simply change the order and it should work as you expected. ```jdbcTemplate.query( sql, mapperFunction, sqlStatementArgs)``` In your case: this.jdbcTemplate.query(sqlQuery.toString(), (rs, rowNum) -> { QueryResults result = new QueryResults(); /* store the results of the query... */ }, preparedStatementArgs); – bh4r4th Apr 05 '22 at 03:06

0 Answers0