Spring NamedParameterJdbcTemplate Example
July 21, 2022
NamedParameterJdbcTemplate class.
1. Spring
NamedParameterJdbcTemplate is template class with a basic set of JDBC operations, allowing the use of named parameters rather than traditional '?' placeholders.
2. After substituting named parameters to JDBC style, the
NamedParameterJdbcTemplate delegates to wrapped JdbcTemplate.
3. The
NamedParameterJdbcTemplate can also expand a List of values to the appropriate number of placeholders.
4. The
NamedParameterJdbcTemplate can be instantiated using following constructors.
NamedParameterJdbcTemplate(DataSource dataSource) NamedParameterJdbcTemplate(JdbcOperations classicJdbcTemplate)
Methods
Here we will discuss few methods ofNamedParameterJdbcTemplate class.
1. update : Issues an update via a prepared statement, binding the given arguments.
int update(String sql, Map<String,?> paramMap) int update(String sql, SqlParameterSource paramSource) int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder) int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, @Nullable String[] keyColumnNames)
paramSource : Container of arguments and SQL types to bind to the query
generatedKeyHolder : A
KeyHolder that will hold the generated keys.
keyColumnNames : Names of the columns that will have keys generated for them.
2. queryForObject : Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query.
<T> T queryForObject(String sql, Map<String,?> paramMap, Class<T> requiredType) <T> T queryForObject(String sql, Map<String,?> paramMap, RowMapper<T> rowMapper) <T> T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType) <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
sql : SQL query to execute.
paramMap : Map of parameters to bind to the query.
requiredType : Type that the result object is expected to match.
rowMapper : Object that will map one object per row.
paramSource : Container of arguments to bind to the query.
3. queryForList : Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query.
List<Map<String,Object>> queryForList(String sql, Map<String,?> paramMap) <T> List<T> queryForList(String sql, Map<String,?> paramMap, Class<T> elementType) List<Map<String,Object>> queryForList(String sql, SqlParameterSource paramSource) <T> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> elementType)
Example
Find the Maven dependencies.<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.5</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.1.1</version> </dependency> </dependencies>
NamedParameterJdbcTemplate class.
PersonDAO.java
package com.concretepage;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;
@Repository
public class PersonDAO {
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public void addPerson(Person p) {
GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
String sql = "insert into person (name, age) values (:name, :age)";
SqlParameterSource paramSource = new MapSqlParameterSource().addValue("name", p.getName()).addValue("age",
p.getAge());
namedParameterJdbcTemplate.update(sql, paramSource, generatedKeyHolder);
int id = generatedKeyHolder.getKey().intValue();
System.out.println(id);
p.setId(id);
}
public int getPersonCount() {
String sql = "select count(*) from person";
SqlParameterSource namedParameters = new MapSqlParameterSource();
return namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}
public void updatePerson(Person p) {
String sql = "update person set name = :name, age = :age where id = :id";
SqlParameterSource paramSource = new MapSqlParameterSource().addValue("name", p.getName())
.addValue("age", p.getAge()).addValue("id", p.getId());
namedParameterJdbcTemplate.update(sql, paramSource);
}
public List<Map<String, Object>> getAllPersonsByAge(int age) {
String sql = "select * from person where age=:age";
SqlParameterSource namedParameters = new MapSqlParameterSource("age", age);
List<Map<String, Object>> list = namedParameterJdbcTemplate.queryForList(sql, namedParameters);
return list;
}
public void deletePersonById(int id) {
String sql = "delete from person where id = :id";
SqlParameterSource paramSource = new MapSqlParameterSource("id", id);
namedParameterJdbcTemplate.update(sql, paramSource);
}
}
package com.concretepage;
public class Person {
private int id;
private String name;
private int age;
public Person(String name, int age) {
this.name = name;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public int getAge() {
return age;
}
}
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean id="personDAO" class="com.concretepage.PersonDAO">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/concretepage"/>
<property name="username" value="root"/>
<property name="password" value="cp"/>
</bean>
</beans>
Table: person
CREATE TABLE `person` ( `id` BIGINT(5) NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `age` INT(3) NOT NULL, PRIMARY KEY (`id`) )
package com.concretepage;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class SpringApp {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("app-config.xml");
PersonDAO personDAO = (PersonDAO)context.getBean("personDAO");
Person p = new Person("Mohan", 25);
personDAO.addPerson(p);
System.out.println(personDAO.getPersonCount());
p = new Person("Rakesh", 30);
p.setId(1);
personDAO.updatePerson(p);
personDAO.deletePersonById(2);
personDAO.getAllPersonsByAge(25)
.forEach(e-> {
System.out.println(e);
});
}
}
