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.