JDBCTemplate optional parameters

Question

I am using spring JDBCTemplate.

---

I have a scenario, where the parameters that need to be passed into my query function, are conditional/optional. For example, I have the following code:

List<RealTimeDTO> result = jdbcTemplate.query(sql, new Object[] {custId, 
number, requestType, startDate, endDate}, new CCCRowMapper());

In the code, I passed in custId, number, requestType, etc. However, requestType is an optional parameter that may come back as null or empty so I don't want it to be passed into the Object[] if it is either null or empty.

What can I do to handle this type of situation?

I could introduce logic where I only pass in the parameters I want into the Object[], however, I was wondering if there is an already built in functionality that handles this instead of me reinventing the wheel.

Anther

"What can I do to handle this type of situation?" if statement!! – Illegal

@Illegal yes genius i know that. Read the last sentence. – Anther

not passing null requestType to Object[] array is simple. How do you dynamically remove the where condition AND requestType = ? ? – Seroka

When i face with dynamic sql, i create list and add param to the list in if statement and pass list as parameters for jdbctemplate. – Zackaryzacks

@Maxim thats exactly what i was doing but it turned out to be too messy. Was wondering if there was a more cleaner way to do it. – Anther

is it springboot application? – Thimbleful

@ShaunakPatel yes, it's a spring boot applicaiton – Anther

Can I assume all parameters you are using are belongs to same entity(or related entity)? – Thimbleful

@Anther I agree with you. For this case i wrote addition function in SQL class from Mybatis. If are you interesting in this class, i could upload code to github – Zackaryzacks

@ShaunakPatel yes, all the parameters i am using is related across to all the entities. All entities will have request type, custid, number, etc. – Anther

Answer 1

One option is to use NamedParameterJdbcTemplate, so the parameter "list" (now a Map) doesn't need to be modified, only the SQL does:

List<RealTimeDTO> query(String name) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    String sql = "SELECT foo, bar" +
                  " FROM FooBar" +
                 " WHERE name" + (name == null ? " IS NULL" : "= :name");
    Map<String, Object> params = new HashMap<>();
    params.put("name", name);
    return jdbcTemplate.query(sql, params, new CCCRowMapper());
}

UPDATE

If you have many conditions that may need to be skipped, and all conditions might be eliminated, then use a StringJoiner to build the WHERE clause:

List<RealTimeDTO> query(String name, String phone, int age) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    StringJoiner where = new StringJoiner(" AND ", " WHERE ", "").setEmptyValue("");
    if (name != null)
        where.add("name = :name");
    if (phone != null)
        where.add("phone = :phone");
    if (age != 0)
        where.add("age = :age");
    String sql = "SELECT foo, bar" +
                  " FROM FooBar" +
                  where;
    Map<String, Object> params = new HashMap<>();
    params.put("name", name);
    params.put("phone", phone);
    params.put("age", age);
    return jdbcTemplate.query(sql, params, new CCCRowMapper());
}

Illegal

Hmm i see, I was using NamedParameter before but not for this type of situation . K will try this and will report back. – Anther

@Anther when you do pass name as null, do you want to check for "name is null" or you do not want to filter by name? what is your requirement? So in the example above for the latter case query should be without a where condition. – Seroka

@gagansingh Correct, if the name is null then it shouldn't do any filtering for name. The where statement for 'name' should not happen – Anther

then this answer will not work. You will have to generate sql dynamically if you stick with jdbc template. If you switch to something like jpa or hibernate, you can use criteria queries which are suited for this kind of usecase. – Seroka

@gagansingh What do you mean "this answer will not work"? Of course it will, you just change the SQL to exclude the conditions you don't want, in a similar way to how the name condition is dynamically changed right now. – Illegal

@Illegal if you have more than one parameter, you not only have to make the filter conditional, even WHERE and AND has to become conditional. I will paste a sample in the next comment with param name and custId – Seroka

String sql = "SELECT foo, bar FROM FooBar " + (name != null || custId != null ? " WHERE " : "") + (name == null ? "" : "name= :name") + (name != null && custId != null ? " AND " : "") + (custId == null ? "" : "custId= :custId"); – Seroka

As you can see that it will work but this approach is not readable. – Seroka

@Illegal much better dynamic sql. just ensure that query does not end with WHERE when all the params are null or 0. – Seroka

@gagansingh Perhaps you should look at the updated answer, where I already covered all that, before all your comments. – Illegal

@Illegal I looked at your update and that's when I mentioned that this is a much better approach but it also has a flaw. See my last comment. find out the sql for query(null,null,0). Even in the earlier comments, I mentioned that we would need to generate a dynamic query and you proposed a good way of doing the same. – Seroka

@gagansingh Even the first example is a dynamic query, so saying we "need to generate a dynamic query" when it's been a dynamic query the entire time is ... weird? confusing? redundant? ---- The entire reason I used StringJoiner is so WHERE and AND would be conditional, without all those ternary conditional operators. Sure, I missed calling setEmptyValue("") to suppress the WHERE, so you last comment was right, but all the others were off, since I think the code is very readable. – Illegal

Answer 2

You can use a static SQL by checking the condition like ? IS NULL OR name = ?. But you have to pass the argument twice AND pass the argument type (at.sql.Types) twice.

String sql = "SELECT foo, bar" +
              " FROM FooBar" +
             " WHERE (? IS NULL OR name = ?) ";
jdbcTemplate.query(sql, new Object[]{name, name}, new int[]{Types.VARCHAR, Types.VARCHAR}, new CCCRowMapper());

IMO not really better than using conditions.

Varga

it has e error for some type of data (instead of varchar(name)) – Gyn

What do you mean? What has a error for "some type of data"? – Varga

This does not give correct result, if the parameter has null in db but your checking for a value still the row is selected. The selected answer will only give correct result. – Vivien

Answer 3

I'd like to add NamedParameter to @Snozzlebert' answer with the next example.

You have to specify the parameters' type if you want to pass null values otherwise you will get a NullPointerException.

@Autowired
NamedParameterJdbcTemplate namedJdbcTemplate;



MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("name", name, Types.VARCHAR);
parameters.addValue("phone", null, Types.VARCHAR);
parameters.addValue("age", null, Types.SMALLINT);

List<RealTimeDTO> list = namedJdbcTemplate.query(
        "SELECT foo, bar\n" +
          " FROM FooBar\n" +
         " WHERE (:name IS NULL OR name = :name) AND \n" +
         " (:phone IS NULL OR phone = :phone) AND \n" +
         " (:age IS NULL OR age = :age)" +,
        parameters,
        new RealTimeDTOMapper());

Huckster

Answer 4

To improve an existing answer, Keep a static query with only bind parameters. Don't 'build' a sql to avoid the pitfall of caching issues and sql injection.

List<RealTimeDTO> query(String name, String phone, int age) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    String sql = "SELECT foo, bar FROM FooBar" +
                 " WHERE (:name IS NULL OR name = :name)" +
                 " AND (:phone IS NULL OR phone = :phone)" +
                 " AND (:age = 0 OR age = :age)";
    params.put("name", name);
    params.put("phone", phone);
    params.put("age", age);
    return jdbcTemplate.query(sql, params, new CCCRowMapper());
}

Titulary