SimpleJdbcTemplate and null parameters

Question

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?

Answer 1

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.