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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s