Java – simplejdbctemplate and null parameters

I use simplejdbctemplate and mapsqlparametersource in a simple way:

MapsqlParameterSource parameterSource = new MapsqlParameterSource();
parameterSource.addValue("typeId",typeId,Types.BIGINT);

List<Long> ids = _jdbcTemplate.query(_selectIdByParameters,new EntityIdRowMapper(),parameterSource);

When typeID (this is a long) is empty, the query method is as follows:

SELECT id FROM XXX WHERE typeId = null

And I expect it to happen

SELECT id FROM XXX WHERE typeId IS NULL

I have reported this issue, and the answer is

So my code is interspersed with blank checks

Is there a more elegant way to handle empty parameters sent to simplejdbctemplate?

Solution

They have one thing - jdbctemplate is not an SQL interpreter, it just replaces your placeholder

I recommend that you use the utility method to construct your clause and concatenate it with 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 beautiful, or maybe you can configure Mysql to allow "= null", but I don't think this is an option