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.
JdbcTemplate "queryForObject" and "query" is deprecated in Spring.
Functionally, there's no difference between Spring's JdbcTemplate and it's variant, NamedParameterJdbcTemplate except for : NamedParameterJdbcTemplate provides a better approach for assigning sql dynamic parameters instead of using multiple '?' in the statement.
The developer assumes it will return a null when record not found. The problem is, Spring throws an EmptyResultDataAccessException , instead of returning a null when record not found.
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());
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With