I'm using SimpleJdbcTemplate and MapSqlParameterSource in the folowing way:
MapSqlParameterSource parameterSource = new MapSqlParameterSource();
parameterSource.addValue("typeId", typeId, Types.BIGINT);
List<Long> ids = _jdbcTemplate.query(_selectIdByParameters, new EntityIdRowMapper(), parameterSource);
When typeId
( which is a Long
) is null
, then the query looks in the following way:
SELECT id FROM XXX WHERE typeId = null
whereas I would expect it to generate
SELECT id FROM XXX WHERE typeId IS NULL
I've reported this issue and the response was that
You will have to provide the appropriate SQL statement based on your query parameters.
and as a consequence my code is littered with null checks.
Is there a more elegant way of handling null parameters sent to the SimpleJdbcTemplate
?
Use two commas (,,) to give a parameter variable a null value when it is followed by other non-null parameters. After the last non-null parameter, all remaining parameter variables up to &31 are automatically given null values. Null parameters are useful when a value is not required.
jdbcTemplate queryForObject return 0 In queryForObject() if the results not found, JdbcTemplate does not returns null, instead it throws EmptyResultDataAccessException. But it's good to return null , otherwise it may breaks the application flow. The developer assumes it will return a null when record not found.
NamedParameterJdbcTemplate class is a template class with a basic set of JDBC operations, allowing the use of named parameters rather than traditional '?' placeholders. This class delegates to a wrapped JdbcTemplate once the substitution from named parameters to JDBC style '?' placeholders is done at execution time.
SqlParameterSource implementation that holds a given Map of parameters. This class is intended for passing in a simple Map of parameter values to the methods of the NamedParameterJdbcTemplate class. The addValue methods on this class will make adding several values easier.
They have a point - JdbcTemplate isn't a SQL interpreter, it just replaces your placeholders.
I suggest you construct your clause with a utility method, and concat it to the query string:
String createNullCheckedClause(String column, Object value) {
String operator = (value == null ? "is" : "=");
return String.format("(%s %s ?)", column, operator);
}
...
String query = "select * from table where " + createNullCheckedClause("col", x);
Not very pretty. Alternatively, perhaps you can configure MySQL to allow "= NULL", but I don't think that's an option.
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