Spring NamedParameterJdbcTemplate example

In this post , we are going to see about Spring NamedParameterJdbcTemplate. In previous post, we have already seen Spring JdbcTemplate example. NamedParameterJdbcTemplate is used to pass named parameter instead of ? in case of JdbcTemplate.
It makes code more readable and maintainable. Lets say you have 10 parameters and you have to use 10 ? to represent parameters and pass it in same sequence in object[] array but with the help of NamedParameterJdbcTemplate, it is very easy to specify parameters.
Lets compare code for JdbcTemplate and NamedParameterJdbcTemplate :

JdbcTemplate :

public Country addCountry(Country country) {
  String query = "insert into Country (id, countryname, population) values (?,?,?)";
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  Object[] args = new Object[] { country.getId(), country.getCountryName(), country.getPopulation() };
  int status = jdbcTemplate.update(query, args);
  if (status != 0) {
   System.out.println("Country saved with country=" + country.getCountryName());
  } else
   System.out.println("Country save failed with country=" + country.getCountryName());
  return country;
 }

NamedParameterJdbcTemplate :

public Country addCountry(Country country) {
  String query = "insert into Country (id, countryname, population) values (:id,:countryname,:population)";
  Map namedParameters = new HashMap ();    
  namedParameters.put("id", country.getId());   
  namedParameters.put("countryname", country.getCountryName());
  namedParameters.put("population", country.getPopulation() );
  NamedParameterJdbcTemplate namedParamterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  int status = namedParamterJdbcTemplate.update(query, namedParameters);
  if (status != 0) {
   System.out.println("Country saved with country=" + country.getCountryName());
  } else
   System.out.println("Country save failed with country=" + country.getCountryName());
  return country;
 }

As you can see, we have use :{paramterName} for passing paramters instead of ? .

Example:

Lets understand with the help of simple example:
Create Country table in mysql database with following code:

CREATE TABLE COUNTRY
(
   id int PRIMARY KEY NOT NULL,
   countryName varchar(100) NOT NULL,
   population int NOT NULL
)
;
CREATE UNIQUE INDEX PRIMARY ON COUNTRY(id)
;

We will you use Country table for querying and updating values in database. Lets first create our bean class Country.java

package org.arpit.java2blog.model;

/*
 * This is our model class and it corresponds to Country table in database
 */

public class Country{

 int id;
 String countryName; 
 long population;

 public Country() {
  super();
 }
 public Country(int i, String countryName,long population) {
  super();
  this.id = i;
  this.countryName = countryName;
  this.population=population;
 }
 public int getId() {
  return id;
 }
 public void setId(int id) {
  this.id = id;
 }
 public String getCountryName() {
  return countryName;
 }
 public void setCountryName(String countryName) {
  this.countryName = countryName;
 }
 public long getPopulation() {
  return population;
 }
 public void setPopulation(long population) {
  this.population = population;
 }
 @Override
 public String toString() {
  return "Country [id=" + id + ", countryName=" + countryName + ", population=" + population + "]";
 } 

}

Create a DAO classed CountryDAO.java which will have all methods for database operations.

package org.arpit.java2blog.dao;

import java.util.List;

import org.arpit.java2blog.model.Country;

public interface CountryDAO {

 List getAllCountries();

 Country getCountry(int id);

 Country addCountry(Country country);

 void updateCountry(Country country);

 void deleteCountry(int id);

}

Create DAO implementation of above interface using normal JDBC APIs.

package org.arpit.java2blog.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.arpit.java2blog.model.Country;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository("countryDAONamedJDBC")
public class CoountryDAOImpl implements CountryDAO {

 @Autowired
 private DataSource dataSource;

 @Override
 public List getAllCountries() {
  String query = "select id, countryname, population from Country";
  List countryList = new ArrayList();
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

  List> countryRows = jdbcTemplate.queryForList(query);

  for (Map countryRow : countryRows) {
   Country country = new Country();
   country.setId(Integer.parseInt(String.valueOf(countryRow.get("id"))));
   country.setCountryName(String.valueOf(countryRow.get("countryName")));
   country.setPopulation((Integer)countryRow.get("population"));
   countryList.add(country);
  }

  return countryList;
 }

 @Override
 public Country getCountry(int id) {
  String query = "select id,countryname, population from Country where id = :id";

  Map namedParameters = new HashMap ();   
  namedParameters.put("id", id);  
  NamedParameterJdbcTemplate namedParamterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  // int status = namedParamterJdbcTemplate.update(query, namedParameters);

  // using RowMapper anonymous class, we can create a separate RowMapper
  // for reuse
  Country country = namedParamterJdbcTemplate.queryForObject(query, namedParameters, new RowMapper() {

   @Override
   public Country mapRow(ResultSet rs, int rowNum) throws SQLException {
    Country country = new Country();
    country.setId(rs.getInt("id"));
    country.setCountryName(rs.getString("countryname"));
    country.setPopulation(rs.getLong("population"));
    return country;
   }
  });

  return country;
 }

 @Override
 public Country addCountry(Country country) {
  String query = "insert into Country (id, countryname, population) values (:id,:countryname,:population)";
  Map namedParameters = new HashMap ();    
  namedParameters.put("id", country.getId());   
  namedParameters.put("countryname", country.getCountryName());
  namedParameters.put("population", country.getPopulation() );
  NamedParameterJdbcTemplate namedParamterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  int status = namedParamterJdbcTemplate.update(query, namedParameters);
  if (status != 0) {
   System.out.println("Country saved with country=" + country.getCountryName());
  } else
   System.out.println("Country save failed with country=" + country.getCountryName());
  return country;
 }

 @Override
 public void updateCountry(Country country) {
  String query = "update Country set countryname=:countryname, population=:population where id=:id";
  Map namedParameters = new HashMap ();    
  namedParameters.put("id", country.getId());   
  namedParameters.put("countryname", country.getCountryName());
  namedParameters.put("population", country.getPopulation() );
  NamedParameterJdbcTemplate namedParamterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  int status = namedParamterJdbcTemplate.update(query, namedParameters);
  if (status != 0) {
   System.out.println("Country updated with country=" + country.getCountryName());
  } else
   System.out.println("No Country found with country=" + country.getCountryName());
 }

 @Override
 public void deleteCountry(int id) {
  String query = "delete from Country where id=:id";
  Map namedParameters = new HashMap ();    
  namedParameters.put("id", id);  
  NamedParameterJdbcTemplate namedParamterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  int status = namedParamterJdbcTemplate.update(query, namedParameters);
  if (status != 0) {
   System.out.println("Country deleted with id=" + id);
  } else
   System.out.println("No Country found with id=" + id);
 }

}

Create applicationcontext.xml as below

Configure datasource basaed on your connection details, datasource bean will be [autowired](https://java2blog.com/autowired-annotation-in-spring/ “autowired”) in CountryDAOImpl.
Create Main class named SpringApplicationMain.java as below

package org.arpit.java2blog.main;

import java.util.List;

import org.arpit.java2blog.dao.CountryDAO;
import org.arpit.java2blog.model.Country;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringApplicationMain {
 public static void main(String[] args) {

  ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
  CountryDAO countryDao = (CountryDAO) context.getBean("countryDAONamedJDBC");

  Country countryUSA = new Country();
  countryUSA.setCountryName("USA");
  countryUSA.setPopulation(10000);

  Country countryIndia = new Country();
  countryIndia.setCountryName("India");
  countryIndia.setPopulation(20000);

  Country countryChina = new Country();
  countryChina.setCountryName("China");
  countryChina.setPopulation(30000);

  Country countryBhutan = new Country();
  countryBhutan.setCountryName("Bhutan");
  countryBhutan.setPopulation(5000);

  // Add Country
  countryDao.addCountry(countryUSA);
  countryDao.addCountry(countryIndia);
  countryDao.addCountry(countryChina);
  countryDao.addCountry(countryBhutan);
  // Read
  Country countryRead = countryDao.getCountry(3);
  System.out.println("Getting country with ID 3::" + countryRead.getCountryName());

  // Update
  countryRead.setPopulation(40000);
  countryDao.updateCountry(countryRead);

  // Get All
  List countryList = countryDao.getAllCountries();
  System.out.println(countryList);

  // Delete
  countryDao.deleteCountry(4);

  System.out.println("We are done with all operations");
 }
}

When you run above program, you will get below output:

Aug 28, 2016 11:01:20 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@71881149: startup date [Sun Aug 28 23:01:20 IST 2016]; root of context hierarchy
Aug 28, 2016 11:01:21 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
Aug 28, 2016 11:01:21 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Country saved with country=USA
Country saved with country=India
Country saved with country=China
Country saved with country=Bhutan
Country Found::Country [id=3, countryName=China, population=30000]
Getting country with ID 3::China
Country updated with country=China
[Country [id=1, countryName=USA, population=10000], Country [id=2, countryName=India, population=20000], Country [id=3, countryName=China, population=40000], Country [id=4, countryName=Bhutan, population=5000]]
Country deleted with id=4
We are done with all operations