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:
- 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)