Using NamedParameterJdbcTemplate

The NamedParameterJdbcTemplate class brings support for programming JDBC instructions using named parameters, as opposed to programming JDBC instructions using only classic placeholder arguments ( '?'). The NamedParameterJdbcTemplate class wraps a JdbcTemplate and delegates most of its work to the wrapped JdbcTemplate. This section describes only those areas of functionality of the NamedParameterJdbcTemplate class that differ from the JdbcTemplate itself - namely, programming JDBC instructions using named parameters. The following example shows how to use NamedParameterJdbcTemplate:

// some DAO class with JDBC support...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
    String sql = "select count(*) from T_ACTOR where first_name = :first_name";
    SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
    return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
fun countOfActorsByFirstName(firstName: String): Int {
    val sql = "select count(*) from T_ACTOR where first_name = :first_name"
    val namedParameters = MapSqlParameterSource("first_name", firstName)
    return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}

Note the use of named parameter notation in the value assigned to the sql variable and the corresponding value that is connected to the namedParameters variable (of type MapSqlParameterSource ).

You can also pass named parameters and their corresponding values to a NamedParameterJdbcTemplate instance using a Map-based style. The remaining methods exposed by NamedParameterJdbcOperations and implemented by the NamedParameterJdbcTemplate class follow a similar pattern and are not discussed here.

The following example shows the use of a style based on Map:

// some DAO class with JDBC support...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
    String sql = "select count(*) from T_ACTOR where first_name = :first_name";
    Map<String, String> namedParameters = Collections.singletonMap("first_name", firstName);
    return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

// some DAO class with JDBC support...
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
fun countOfActorsByFirstName(firstName: String): Int {
    val sql = "select count(*) from T_ACTOR where first_name = :first_name"
    val namedParameters = mapOf("first_name" to firstName)
    return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}

One of the nice features associated with NamedParameterJdbcTemplate (and existing in the same Java package) is the SqlParameterSource interface. You've already seen an example implementation of this interface in one of the previous code snippets (the MapSqlParameterSource class). SqlParameterSource is the source of named parameter values for NamedParameterJdbcTemplate. The MapSqlParameterSource class is a simple implementation that is an adapter to java.util.Map, where the keys are the parameter names and the values are the parameter values.

Another implementation of SqlParameterSource is the BeanPropertySqlParameterSource class. This class wraps an arbitrary JavaBean (that is, an instance of a class that follows the conventions of JavaBean) and uses the properties of the wrapped JavaBean as a source of named parameter values.

The following example shows a typical JavaBean:

public class Actor {
    private Long id;
    private String firstName;
    private String lastName;
    public String getFirstName() {
        return this.firstName;
    }
    public String getLastName() {
        return this.lastName;
    }
    public Long getId() {
        return this.id;
    }
    // setters omitted...
}

data class Actor(val id: Long, val firstName: String, val lastName: String)

The following example uses NamedParameterJdbcTemplate to return the class member count shown in the previous example:

// some DAO class with JDBC support...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActors(Actor exampleActor) {
    // note that the named parameters correspond to the properties of the above-mentioned "Actor" class
    String sql = "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";
    SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
    return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

// some DAO class with JDBC support...
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
fun countOfActors(exampleActor: Actor): Int {
    // note that the named parameters correspond to the properties of the above-mentioned "Actor" class
    val sql = "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName"
    val namedParameters = BeanPropertySqlParameterSource(exampleActor)
    return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Int::class.java)!!
}

Remember that the NamedParameterJdbcTemplate class wraps the classic JdbcTemplate template. If you need access to an instance of the wrapped JdbcTemplate to access functionality that is only present in the JdbcTemplate class, you can use the getJdbcOperations() method to accessing the wrapped JdbcTemplate through the JdbcOperations interface.

See also sections "Best practices for working with JdbcTemplate" for guidance on using the NamedParameterJdbcTemplate class in an application context.