Spring NamedParameterJdbcTemplate Examples

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:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.