Spring NamedParameterJdbcTemplate example

Table of Contents

JdbcTemplate :
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;
 }

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<String,Object> namedParameters = new HashMap<String,Object> ();    
  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;
 }

public Country addCountry(Country country) {

String query = "insert into Country (id, countryname, population) values (:id,:countryname,:population)";

Map<String,Object> namedParameters = new HashMap<String,Object> ();

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)
;

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 + "]";
 } 

}

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);

}

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<Map<String, Object>> countryRows = jdbcTemplate.queryForList(query);

  for (Map<String, Object> 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<String,Object> namedParameters = new HashMap<String,Object> ();   
  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<String,Object> namedParameters = new HashMap<String,Object> ();    
  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<String,Object> namedParameters = new HashMap<String,Object> ();    
  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<String,Object> namedParameters = new HashMap<String,Object> ();    
  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);
 }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

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<Map<String, Object>> countryRows = jdbcTemplate.queryForList(query);

for (Map<String, Object> 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<String,Object> namedParameters = new HashMap<String,Object> ();

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<String,Object> namedParameters = new HashMap<String,Object> ();

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<String,Object> namedParameters = new HashMap<String,Object> ();

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<String,Object> namedParameters = new HashMap<String,Object> ();

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


<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:beans="http://www.springframework.org/schema/beans"
 xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
 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
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

 <context:annotation-config />
 <beans:bean id="dataSource"
  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
  <beans:property name="url"
   value="jdbc:mysql://localhost:3306/CountryData" />
  <beans:property name="username" value="root" />
  <beans:property name="password" value="" />
 </beans:bean>
 <context:component-scan base-package="org.arpit.java2blog" />
</beans:beans>

<?xml version="1.0" encoding="UTF-8"?>

<beans:beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:beans="http://www.springframework.org/schema/beans"

xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"

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

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

<context:annotation-config />

<beans:bean id="dataSource"

class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />

<beans:property name="url"

value="jdbc:mysql://localhost:3306/CountryData" />

<beans:property name="username" value="root" />

<beans:property name="password" value="" />

</beans:bean>

<context:component-scan base-package="org.arpit.java2blog" />

</beans:beans>

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");
 }
}

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

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

Was this post helpful?

Let us know if this post was helpful. Feedbacks are monitored on daily basis. Please do provide feedback as that\'s the only way to improve.

Spring NamedParameterJdbcTemplate example

JdbcTemplate :

NamedParameterJdbcTemplate :

Example:

Was this post helpful?

Author

Leave a Reply Cancel reply

Categories

Popular Posts

Let’s be Friends

JdbcTemplate :

NamedParameterJdbcTemplate :

Example:

Was this post helpful?

Related posts:

Share this

Author

Leave a Reply Cancel reply

Let’s be Friends