NamedParameterJdbcTemplate

NamedParameterJdbcTemplate

In this tutorial, we are going to discuss NamedParameterJdbcTemplate in the spring framework. This class is the same as JdbcTemplate class (Discussed in previous tutorial), but, NamedParameterJdbcTemplate class can define and run SQL queries with Named Parameters instead of positional parameters.

E.g.

String query = "insert into student values(:sid, :sname, :saddr)";

Where :sid, :sname, :saddr are named parameters for which we have to provide values.

NamedParameterJdbcTemplate in spring DAO

In the case of NamedParameterJdbcTemplate, we can provide values to the named parameters in the following two approaches.

  1. By Using Map directly.
  2. By using SqlParameterSource interface.
1. By Using Map directly.
String query = "insert into student values(:sid, :sname, :saddr)";
Map map = new HashMap();
map.put("sid", "S-111");
map.put("sname", "Ashok Kumar");
map.put("saddr", "Bhimavaram");
namedParameterJdbcTemplate.update(query, map);
2. By using SqlParameterSource interface.

Spring has provided the following two implementation classes for the SqlParameterSoure interface to provide values to the Named parameters.

  • MapSqlParameterSource
  • BeanPropertySqlParameterSource

To provide values to the named parameters, if we want to use MapSqlParameterSource, we first have to create an object for MapSqlParameterSource, and we have to use the following method to add values to the named parameters.

public MapSqlParameterSource addValue(String name, Object val)

String query = "insert into student values(:sid, :sname, :saddr)";
SqlParameterSource paramSource = new MapSqlParameterSource("sid", "S-111");
paramSource = paramSource.addValue("sname", "Ashok Kumar");
paramSource = paramSource.addValue("saddr", "Bhimavaram");
namedParameterJdbcTemplate.update(query, paramSource);

To provide values to the named parameters, if we want to use BeanPropertySqlParameterSource, we first have to create a bean object with data. We have to create an Object for BeanPropertySqlParameterSource with the generated Bean reference, then provide the BeanPropertySqlParameterSource object to query methods.

String query = "insert into student values(:sid, :sname, :saddr)";
Student std = new Student();
std.setSid("S-111");
std.setSname("Ashok Kumar");
std.setSaddr("Bhimavaram ");
SqlParameterSource paramSource = new BeanPropertySqlParameterSource(std);
namedParameterJdbcTemplate.update(query, paramSource);

Note

JdbcTemplate allows DataSource object injection through the setter method, but the NamedParameterJdbcTemplate class allows DataSource object injection through Constructor Dependency Injection.

E.g

package com.ashok.spring.dao.namedparameter;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class Customer {
   private String cid;
   private String cname;
   private String caddr;

   public String getCid() {
      return cid;
   }

   public void setCid(String cid) {
      this.cid = cid;
   }

   public String getCname() {
      return cname;
   }

   public void setCname(String cname) {
      this.cname = cname;
   }

   public String getCaddr() {
      return caddr;
   }

   public void setCaddr(String caddr) {
      this.caddr = caddr;
   }
}
package com.ashok.spring.dao.namedparameter;

/**
 * 
 * @author ashok.mariyala
 *
 */
public interface CustomerDao {
   public String add(Customer c);
   public Customer search(String cid);
   public String update(Customer c);
   public String delete(String cid);
}
package com.ashok.spring.dao.namedparameter;

import java.util.HashMap;
import java.util.Map;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class CustomerDaoImpl implements CustomerDao {
   String status = "";
   private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

   public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
      this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;

   }

   @Override
   public String add(Customer c) {
      String query = "insert into customer values(:cid, :cname, :caddr)";
      Map<String, Object> map = new HashMap<>();
      map.put("cid", c.getCid());
      map.put("cname", c.getCname());
      map.put("caddr", c.getCaddr());
      namedParameterJdbcTemplate.update(query, map);
      return "SUCCESS";
   }

   @Override
   public Customer search(String cid) {
      String query = "select * from customer where cid=:cid";
      Map<String, Object> map = new HashMap<>();
      map.put("cid", cid);
      Customer c = namedParameterJdbcTemplate.queryForObject(query, map, new CustomerMapper());
      return c;
   }

   @Override
   public String update(Customer c) {
      String query = "update customer set CNAME=:cname, CADDR=:caddr where CID=:cid";
      SqlParameterSource paramSource = new BeanPropertySqlParameterSource(c);
      namedParameterJdbcTemplate.update(query, paramSource);
      return "SUCCESS";
   }

   @Override
   public String delete(String cid) {
      String query = "delete from customer where cid=:cid";
      SqlParameterSource paramSource = new MapSqlParameterSource("cid", cid);
      namedParameterJdbcTemplate.update(query, paramSource);
      return "SUCCESS";
   }
}
package com.ashok.spring.dao.namedparameter;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class CustomerMapper implements RowMapper {
   @Override
   public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
      Customer c = new Customer();
      c.setCid(rs.getString("CID"));
      c.setCname(rs.getString("CNAME"));
      c.setCaddr(rs.getString("CADDR"));
      return c;
   }
}
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
   <bean id="customerDao" class="com.ashok.spring.dao.namedparameter.CustomerDaoImpl">
      <property name="namedParameterJdbcTemplate"   ref="namedParameterJdbcTemplate" />
   </bean>
   <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
      <constructor-arg ref="dataSource" />
   </bean>
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
      <property name="url" value="jdbc:mysql://localhost:3306/student" />
      <property name="username" value="root" />
      <property name="password" value="ashok" />
   </bean>
</beans>
package com.ashok.spring.dao.namedparameter;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class TestDaoApplication {
   public static void main(String[] args) throws Exception {
      String configFile = "/com/ashok/spring/dao/namedparameter/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      CustomerDao dao = (CustomerDao) context.getBean("customerDao");
      Customer c = new Customer();
      c.setCid("C-111");
      c.setCname("Ashok Kumar");
      c.setCaddr("Bhimavaram");
      String status = dao.add(c);
      System.out.println("Customer Insertion :" + status);
      c.setCid("C-222");
      c.setCname("Vinod Kumar");
      c.setCaddr("Banglore");
      status = dao.add(c);
      System.out.println("Customer Insertion :" + status);
      
      Customer c1 = dao.search("C-111");
      System.out.println("Customer Details");
      System.out.println("--------------------");
      System.out.println("Customer Id :" + c1.getCid());
      System.out.println("Customer Name :" + c1.getCname());
      System.out.println("Customer Address :" + c1.getCaddr());
      System.out.println();
      
      Customer c2 = new Customer();
      c2.setCid("C-111");
      c2.setCname("Ashok");
      c2.setCaddr("Bhimavaram");
      status = dao.update(c2);
      System.out.println("Student Updation Status :" + status);
      
      Customer c3 = dao.search("C-111");
      System.out.println("Customer Updated Details");
      System.out.println("--------------------");
      System.out.println("Customer Id :" + c3.getCid());
      System.out.println("Customer Name :" + c3.getCname());
      System.out.println("Customer Address :" + c3.getCaddr());
      System.out.println();
      
      status = dao.delete("C-111");
      System.out.println("Student Deletion Status :" + status);
   }
}

Output

Customer Insertion :SUCCESS
Customer Insertion :SUCCESS
Customer Details
--------------------
Customer Id :C-111
Customer Name :Ashok Kumar
Customer Address :Bhimavaram
Student Updation Status: SUCCESS
Customer Updated Details
-------------------------------------
Customer Id: C-111
Customer Name: Ashok
Customer Address: Bhimavaram
Student Deletion Status: SUCCESS
NamedParameterJdbcTemplate

Scroll to top