- Details
- Written by Nam Ha Minh
- Last Updated on 02 September 2019   |   Print Email
In this Spring JDBC tutorial, you will learn how to use the
NamedParameterJdbcTemplate class to execute SQL statements which contain parameters.You know, when
using JdbcTemplate class, we have to specify question marks (?) as placeholders for the parameters in a SQL statement like this:
String sql = "INSERT INTO contact (name, email, address) VALUES (?, ?, ?)";
Then the values for these parameters are passed like this:
jdbcTemplate.update(sql, "Tom", "tomea@mail.com", "USA");
Using placeholder parameters seems to be convenient, but it causes readability issues, i.e. which value is for which parameter? It is also error-prone because the code could still work if the order of the values changes incidentally.Therefore, Spring JDBC provides the
NamedParameterJdbcTemplate class to solve the issues which may be caused by using placeholder parameters with
JdbcTemplate class. Consider the following code example:
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
String sql = "INSERT INTO Contact (name, email, address) VALUES (:name, :email, :address)";
Map<String, String> params = new HashMap<>();
params.put("name", "Tom");
params.put("email", "tomea@gmail.com");
params.put("address", "USA");
template.update(sql, params);
As you can see, instead of question marks (?), we can specify meaningful names for the parameters in the SQL statement. And we set value for each parameter by their name, which greatly improves the readability of the code, right?Actually, the
NamedParameterJdbcTemplate class is a wrapper of
JdbcTemplate class so it has the same methods names. The differences are in the parameters that allow you to use named parameters for SQL statements.You can use the
MapSqlParameterSource class to hold the parameter names and values in more convenient way, as its
addValue() method can be chained together. For example:
String sql = "INSERT INTO Contact (name, email, address) VALUES (:name, :email, :address)";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("name", "Nam Ha Minh")
.addValue("email", "nam@codejava.net")
.addValue("address", "Da Nang, Vietnam");
template.update(sql, params);
If you have domain model class, you can use the
BeanPropertySqlParameterSource class that can inspect the properties of a given domain model class to generate parameter names and fill values accordingly. Let’s see the following method:
public void save(Contact contact) {
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
String sql = "INSERT INTO Contact (name, email, address) VALUES (:name, :email, :address)";
BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(contact);
template.update(sql, paramSource);
}
Code of the
Contact class is as follows:
public class Contact {
private Integer id;
private String name;
private String email;
private String address;
// constructors
// getters
// setters
}
So as long as the named parameters have the same name as the properties in the domain model class, you can use the
BeanPropertySqlParameterSource conveniently.
NOTE: You can use
SimpleJdbcInsert class that simplifies the code even more, e.g. you don't have to write SQL Insert statement. Here’s another code example to use
NamedParameterJdbcTemplate to execute a query that returns an object:
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
String sql = "SELECT * FROM Contact WHERE name=:name";
SqlParameterSource param = new MapSqlParameterSource("name", "Nam Ha Minh");
Contact result = template.query(sql, param, new ResultSetExtractor<Contact>() {
@Override
public Contact extractData(ResultSet rs) throws SQLException, DataAccessException {
if (rs.next()) {
Contact contact = new Contact();
contact.setId(rs.getInt("contact_id"));
contact.setName(rs.getString("name"));
contact.setEmail(rs.getString("email"));
contact.setAddress(rs.getString("address"));
return contact;
}
return null;
}
});
Note that you can use the BeanPropertyRowMapper class instead of ResultSetExtractor to simplify the code. For example: NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
String sql = "SELECT * FROM Contact WHERE name=:name";
SqlParameterSource param = new MapSqlParameterSource("name", "Nam Ha Minh");
Contact result = template.queryForObject(sql, param, BeanPropertyRowMapper.newInstance(Contact.class));
Provided that the Contact class has the field names exactly same as the column names in the database table.
And the following code example illustrates how to use NamedParameterJdbcTemplate to execute query that returns a list of objects:NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
String sql = "SELECT * FROM Contact WHERE email LIKE :email";
SqlParameterSource param = new MapSqlParameterSource("email", "%gmail.com%");
List<Contact> result = template.query(sql, param, new RowMapper() {
@Override
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Contact contact = new Contact();
contact.setId(rs.getInt("contact_id"));
contact.setName(rs.getString("name"));
contact.setEmail(rs.getString("email"));
contact.setAddress(rs.getString("address"));
return contact;
}
});
You can also use the
BeanPropertyRowMapper class to simplify the code, for example:
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
String sql = "SELECT * FROM Contact WHERE email LIKE :email";
SqlParameterSource param = new MapSqlParameterSource("email", "%gmail.com%");
List<Contact> result = template.query(sql, param, BeanPropertyRowMapper.newInstance(Contact.class));
Those are several code examples which you can follow to use the
NamedParameterJdbcTemplate class. To understand how to configure your project to use Spring JDBC, please refer to the following tutorials:
Related Spring and Database Tutorials:
Other Spring Tutorials:
About the Author:
Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on
Facebook and watch
his Java videos you YouTube.