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

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<Country> 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<Country> getAllCountries() {
  String query = "select id, countryname, population from Country";
  List<Country> countryList = new ArrayList<Country>();
  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>
Configure datasource basaed on your connection details, datasource bean will be 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<Country> 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

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<Country> getAllCountries() {
  String query = "select id, countryname, population from Country";
  List<Country> countryList = new ArrayList<Country>();
  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<Country>() {

   @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

Written by Arpit:

If you have read the post and liked it. Please connect with me on Facebook | Twitter | Google Plus

 

Java tutorial for beginners Copyright © 2012