SimpleJdbcTemplate and null parameters

java, jdbc, spring, spring-jdbc

Solution

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.

Problem

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`?

Original source