Home  >  Spring

Spring NamedParameterJdbcTemplate Example

By Arvind Rai, July 21, 2022
On this page we will learn using Spring 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 of NamedParameterJdbcTemplate 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) 
sql : SQL containing named parameters.
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> 
Now find the complete code. Here we are performing CRUD operations using 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);
  }
} 
Person.java
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;
	}
} 
app-config.xml
<?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> 
Find the MySQL table.
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`)
) 
SpringApp.java
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);
		  });
	}
} 
Output
Spring NamedParameterJdbcTemplate Example

Reference

Class NamedParameterJdbcTemplate

Download Source Code

POSTED BY
ARVIND RAI
ARVIND RAI











©2022 concretepage.com | Privacy Policy | Contact Us