Spring JdbcTemplate example

Table of Contents

Spring example using Normal JDBC API:
Spring JdbcTemplate example:

In this post, we are going to see Spring JdbcTemplate example.
Spring jdbcTemplate is used to convenient way to connect to database and execute queries. It internally use JDBC code only, but provides you APIs , so you don’t have to write boiler plate code. You don’t have write much code before and after executing queries for creating connection , creating statement , closing connections etc.

Lets understand with the help of simple example:

Lets say you want to save country object to database.
If you want to write it using normal JDBC api, you have to use below code:


 public Country addCountry(Country country) {
  String query = "insert into Country (id, countryname, population) values (?,?,?)";
  Connection con = null;
  PreparedStatement ps = null;
  try {
   con = dataSource.getConnection();
   ps = con.prepareStatement(query);
   ps.setInt(1, country.getId());
   ps.setString(2, country.getCountryName());
   ps.setLong(3, country.getPopulation());
   int out = ps.executeUpdate();
   if (out != 0) {
    System.out.println("Country saved with country=" + country.getCountryName());
   } else
    System.out.println("Country save failed with country=" + country.getCountryName());
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    ps.close();
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  return country;
 }

public Country addCountry(Country country) {

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

Connection con = null;

PreparedStatement ps = null;

try {

con = dataSource.getConnection();

ps = con.prepareStatement(query);

ps.setInt(1, country.getId());

ps.setString(2, country.getCountryName());

ps.setLong(3, country.getPopulation());

int out = ps.executeUpdate();

if (out != 0) {

System.out.println("Country saved with country=" + country.getCountryName());

} else

System.out.println("Country save failed with country=" + country.getCountryName());

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

ps.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

return country;

}

If you use Spring JdbcTemplate to do same operation, you need to use below code:


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;

}

If you observe above code, you can easily see that you need to write very less code with Spring JdbcTemplate and you also don’t need to do exception handling

Spring example using Normal JDBC API:

Create Country table in mysql database with following code:


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

CREATE TABLE COUNTRY

(

id int PRIMARY KEY NOT NULL AUTO_INCREMENT,

countryName varchar(100) NOT NULL,

population int NOT NULL

)

;

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.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.arpit.java2blog.model.Country;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository("countryDAO")
public class CountryDAOImpl implements CountryDAO {

 @Autowired
 private DataSource dataSource;

 @Override
 public List getAllCountries() {
  String query = "select id, countryname, population from Country";
  List countryList = new ArrayList();
  Connection con = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
   con = dataSource.getConnection();
   ps = con.prepareStatement(query);
   rs = ps.executeQuery();
   while (rs.next()) {
    Country country = new Country();
    country.setId(rs.getInt("id"));
    country.setCountryName(rs.getString("countryname"));
    country.setPopulation(rs.getLong("population"));
    countryList.add(country);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    rs.close();
    ps.close();
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  return countryList;
 }

 @Override
 public Country getCountry(int id) {
  String query = "select countryname, population from Country where id = ?";
  Country country = null;
  Connection con = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
   con = dataSource.getConnection();
   ps = con.prepareStatement(query);
   ps.setInt(1, id);
   rs = ps.executeQuery();
   if (rs.next()) {
    country = new Country();
    country.setId(id);
    country.setCountryName(rs.getString("countryname"));
    country.setPopulation(rs.getLong("population"));
    System.out.println("Country Found::" + country);
   } else {
    System.out.println("No Country found with id=" + id);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    rs.close();
    ps.close();
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  return country;
 }

 @Override
 public Country addCountry(Country country) {
  String query = "insert into Country (id, countryname, population) values (?,?,?)";
  Connection con = null;
  PreparedStatement ps = null;
  try {
   con = dataSource.getConnection();
   ps = con.prepareStatement(query);
   ps.setInt(1, country.getId());
   ps.setString(2, country.getCountryName());
   ps.setLong(3, country.getPopulation());
   int out = ps.executeUpdate();
   if (out != 0) {
    System.out.println("Country saved with country=" + country.getCountryName());
   } else
    System.out.println("Country save failed with country=" + country.getCountryName());
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    ps.close();
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  return country;
 }

 @Override
 public void updateCountry(Country country) {
  String query = "update Country set countryname=?, population=? where id=?";
  Connection con = null;
  PreparedStatement ps = null;
  try {
   con = dataSource.getConnection();
   ps = con.prepareStatement(query);
   ps.setString(1, country.getCountryName());
   ps.setLong(2, country.getPopulation());
   ps.setInt(3, country.getId());
   int out = ps.executeUpdate();
   if (out != 0) {
    System.out.println("Country updated with country=" + country.getCountryName());
   } else
    System.out.println("No Country found with country=" + country.getCountryName());
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    ps.close();
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

 public void deleteCountry(int id) {
  String query = "delete from Country where id=?";
  Connection con = null;
  PreparedStatement ps = null;
  try {
   con = dataSource.getConnection();
   ps = con.prepareStatement(query);
   ps.setInt(1, id);
   int out = ps.executeUpdate();
   if (out != 0) {
    System.out.println("Country deleted with id=" + id);
   } else
    System.out.println("No Country found with id=" + id);
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    ps.close();
    con.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

package org.arpit.java2blog.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import javax.sql.DataSource;

import org.arpit.java2blog.model.Country;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Repository;

@Repository("countryDAO")

public class CountryDAOImpl implements CountryDAO {

@Autowired

private DataSource dataSource;

@Override

public List getAllCountries() {

String query = "select id, countryname, population from Country";

List countryList = new ArrayList();

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

con = dataSource.getConnection();

ps = con.prepareStatement(query);

rs = ps.executeQuery();

while (rs.next()) {

Country country = new Country();

country.setId(rs.getInt("id"));

country.setCountryName(rs.getString("countryname"));

country.setPopulation(rs.getLong("population"));

countryList.add(country);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

rs.close();

ps.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

return countryList;

}

@Override

public Country getCountry(int id) {

String query = "select countryname, population from Country where id = ?";

Country country = null;

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

con = dataSource.getConnection();

ps = con.prepareStatement(query);

ps.setInt(1, id);

rs = ps.executeQuery();

if (rs.next()) {

country = new Country();

country.setId(id);

country.setCountryName(rs.getString("countryname"));

country.setPopulation(rs.getLong("population"));

System.out.println("Country Found::" + country);

} else {

System.out.println("No Country found with id=" + id);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

rs.close();

ps.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

return country;

}

@Override

public Country addCountry(Country country) {

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

Connection con = null;

PreparedStatement ps = null;

try {

con = dataSource.getConnection();

ps = con.prepareStatement(query);

ps.setInt(1, country.getId());

ps.setString(2, country.getCountryName());

ps.setLong(3, country.getPopulation());

int out = ps.executeUpdate();

if (out != 0) {

System.out.println("Country saved with country=" + country.getCountryName());

} else

System.out.println("Country save failed with country=" + country.getCountryName());

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

ps.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

return country;

}

@Override

public void updateCountry(Country country) {

String query = "update Country set countryname=?, population=? where id=?";

Connection con = null;

PreparedStatement ps = null;

try {

con = dataSource.getConnection();

ps = con.prepareStatement(query);

ps.setString(1, country.getCountryName());

ps.setLong(2, country.getPopulation());

ps.setInt(3, country.getId());

int out = ps.executeUpdate();

if (out != 0) {

System.out.println("Country updated with country=" + country.getCountryName());

} else

System.out.println("No Country found with country=" + country.getCountryName());

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

ps.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

public void deleteCountry(int id) {

String query = "delete from Country where id=?";

Connection con = null;

PreparedStatement ps = null;

try {

con = dataSource.getConnection();

ps = con.prepareStatement(query);

ps.setInt(1, id);

int out = ps.executeUpdate();

if (out != 0) {

System.out.println("Country deleted with id=" + id);

} else

System.out.println("No Country found with id=" + id);

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

ps.close();

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

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("countryDAO");

  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("countryDAO");

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

As you can see in CountryDAOImpl, you need write lot of code to handle connections, exceptions etc.

Spring JdbcTemplate example:

Now Replaced above CountryDAOImpl.java with below Spring JdbcTemplate example.


package org.arpit.java2blog.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
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.stereotype.Repository;

@Repository("countryDAO")
public class SpringJDBCTemplateExample 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 = ?";
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

  // using RowMapper anonymous class, we can create a separate RowMapper
  // for reuse
  Country country = jdbcTemplate.queryForObject(query, new Object[] {id}, 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 (?,?,?)";
  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;
 }

 @Override
 public void updateCountry(Country country) {
  String query = "update Country set countryname=?, population=? where id=?";
  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 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=?";
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

  int status = jdbcTemplate.update(query, id);
  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

package org.arpit.java2blog.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

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.stereotype.Repository;

@Repository("countryDAO")

public class SpringJDBCTemplateExample 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 = ?";

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

// using RowMapper anonymous class, we can create a separate RowMapper

// for reuse

Country country = jdbcTemplate.queryForObject(query, new Object[] {id}, 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 (?,?,?)";

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;

}

@Override

public void updateCountry(Country country) {

String query = "update Country set countryname=?, population=? where id=?";

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 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=?";

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

int status = jdbcTemplate.update(query, id);

if (status != 0) {

System.out.println("Country deleted with id=" + id);

} else

System.out.println("No Country found with id=" + id);

}

As you can see, you need to write very less code with SpringJdbcTemplate.
jdbcTemplate.update(query, args) : This method is used to add or update in database. Object[] args is arguments array corresponds to ? in the query. jdbcTemplate.queryForObject(query, new Object[] {id}, new RowMapper() : We have used RowMapper and overriden maprows method to set values from resultset to country object. When you run above SpringApplicationMain again , you will get similar ouput.

Lets compare lines of code for normal JDBC and Spring JdbcTemplate

Method	Total lines of code for same operations
JDBC API	170
Spring JdbcTemplate	106

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 JdbcTemplate example

Spring example using Normal JDBC API:

Spring JdbcTemplate example:

Was this post helpful?

Author

Leave a Reply Cancel reply

Categories

Popular Posts

Let’s be Friends

Spring example using Normal JDBC API:

Spring JdbcTemplate example:

Was this post helpful?

Related posts:

Share this

Author

Leave a Reply Cancel reply

Let’s be Friends