Named Parameters with JDBC Template in Spring

Although in many cases a powerful ORM tool such as Hibernate is the best way access data in your application, sometimes dropping to SQL makes sense.

The Spring frameworks are a very popular set of libraries for building java software. A very handy class provided by the spring-jdbc library is JdbcTemplate which can be used to help in issuing SQL commands to a database server.

JdbcTemplate can be injected and removes much of the boiler-plate involved and its use looks a bit like this;

jdbcTemplate.query(
  "SELECT x, y from z WHERE z.a > ? OR z.b < ?",
  new Object[] { threshold, threshold },
  (row, rowNum) -> new Zee(rs.getString("x"), rs.getString("y"))
);

This is a very simple SQL statement but you can see that the parameter threshold was used more than once. In a large SQL statement where the parameter were used many times mixed with other parameters, this positional provision of parameters could get difficult to manage.

Another utility class NamedParameterJdbcTemplate can help out here. To create one of these, simply wrap the already-injected JdbcTemplate like so;

NamedParameterJdbcTemplate namedParameterJdbcTemplate =
  new NamedParameterJdbcTemplate(jdbcTemplate);

This instance can be used like this;

namedParameterJdbcTemplate.query(
  "SELECT x, y from z WHERE z.a > :threshold OR z.b < :threshold",
  Map.of("threshold", threshold),
  (row, rowNum) -> new Zee(rs.getString("x"), rs.getString("y"))
);

In this case the parameter threshold is referenced by name and is only provided once.