pass in null parameter from jdbctemplate

i have a query where some of the parameter is optional, something like

String PERMS = """
  select .....
  where ...
  and (:app is null or a.app=:app)
""";

this query and corresponding DAO method works fine when the parameter is not null, however would fail for both jdbctemplate or namedJdbcTemplate.

Turned out the trick is two folded:

1. from the sql side, tell the dbms and its driver what’s the type of the optional parameter

String PERMS = """
  select .....
  where ...
  and (:app::text is null or a.app=:app)
""";

2. with namedjdbcTemplate, pass in the type (0) to the jdbc driver

MapSqlParamterSource params = ..
params.addValue("app", null, Types.NULL)

Advertisement