Spring JDBC: An example on JdbcTemplate using Annotation

In this article, we will implement an example based on the Spring JdbcTemplate using annotation which ultimately reduces the burden of writing/coding boilerplate codes like creating/closing connection to the database and handling exception

Technology Used

  • Java 1.7
  • Eclipse Luna IDE
  • Spring-4.0.0-RELEASE
  • Apache-Maven-3.2.1
  • MySql-Connector-Java-5.1.31

Mavenize or download required jars

Add Spring-4.0.0 & mysql-connector-java dependencies to the pom.xml

		<!-- Spring Core and Context -->

		<!-- Spring JDBC -->

		<!-- MySql-Connector -->

Folks who aren’t familiar with Maven concepts or don’t require maven for their project, can download the below jars individually from the spring site and include them in the classpath

Project Structure (Package Explorer view in Eclipse)


Jars Libraries Used in the Project (Maven Dependencies)


Creating tables and inserting few records

Create table command


Insert command

INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Sachin Tendulkar",41,200);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Shane Warne",44,145);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Kevin Pietersen",34,104);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Shahid Afridi",35,27);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Brian Lara",45,131);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Graeme Smith",34,117);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Mahela Jayawardene",37,145);

Spring Configuration Context xml

This Spring Bean xml defines

  • dataSource with value for driverClassName, url, username and password
  • jdbcTemplate bean which injects the dataSource bean configured in the XML
  • <context:annotation-config /> – to activate beans registered with the application context
  • <context:component-scan base-package=“”/> – scans package to find & register beans within the application context


<?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-4.0.xsd">

	<!-- to activate annotations in beans already registered in the application context -->
	<context:annotation-config />

	<!-- scans packages to find and register beans within the application context -->
	<context:component-scan base-package="com.spring.series.jdbc.dao.impl" />

	<!-- jdbcTemplate uses dataSource -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />

	<!-- dataSource configuration -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/benchresources" />
		<property name="username" value="root" />
		<property name="password" value="" />

Let’s see coding in action

Model class Player with four primitive attributes with their getter/setter && a no-argument and 3-argument constructors


package com.spring.series.jdbc.model;

public class Player {

	// member variables
	private int playerId;
	private String name;
	private int age;
	private int matches;

	// default constructor
	public Player() {

	// 3-arg parameterized-constructor
	public Player(String name, int age, int matches) {
		this.name = name;
		this.age = age;
		this.matches = matches;

	// getter and setter
	public int getPlayerId() {
		return playerId;
	public void setPlayerId(int playerId) {
		this.playerId = playerId;
	public String getName() {
		return name;
	public void setName(String name) {
		this.name = name;
	public int getAge() {
		return age;
	public void setAge(int age) {
		this.age = age;
	public int getMatches() {
		return matches;
	public void setMatches(int matches) {
		this.matches = matches;

PlayerDAO interface

PlayerDAO interface with three public methods exposed to retrieve

  • get player based on the PLAYER_ID
  • get all players stored in the database
  • inserts/saves new player information in the database using jdbcTemplate

Note: It is always good programming practice to write code-to-interface


package com.spring.series.jdbc.dao;

import java.util.List;

import com.spring.series.jdbc.model.Player;

public interface PlayerDAO {

	public Player getPlayer(int playerId);
	public String insertNewPlayer(Player player);
	public List<Player> getAllPlayer();

PlayerDAOImpl class

This Impl class implements above interface which exposes three public methods
It uses Spring JDBC configured in the SpringContext.xml file to interact with MySql database to do database operations


This component class is annotated with @Repository (“playerDAO”) and private member variable JdbcTemplate is annotated with @Autowired, both of which scanned and registered with the application context as we have already instructed this through the context xml

JdbcTemplate vs  plain Spring JDBC

  • with JdbcTemplate, you don’t need to create and manage many redundant codes like creating connection, closing connection and handling exception
  • also don’t’ need to handle core SQL DML statements like insert, delete, update making performance inefficient
  • JdbcTemplate can handle above overhead automatically since it got many readily available variants. For example, see the below screenshot

There are more variants, so before creating your own consult documentation
Choose wisely from the available variants for your requirements
Note: Since Spring 2.5 allows the developer to map the returning object’s property to the column value using “BeanPropertyRowMapper” or “ParameterizedBeanPropertyRowMapper” provided both has same name. For example, java property playerId will match to column value PLAYERID or PLAYER_ID both will do


package com.spring.series.jdbc.dao.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.spring.series.jdbc.dao.PlayerDAO;
import com.spring.series.jdbc.model.Player;

public class PlayerDAOImpl implements PlayerDAO {

	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;

	 * This method retrieves a player from database using jdbcTemplate based on the PLAYER_ID supplied in the formal arguments
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Player getPlayer(int playerId) {

		Player player = (Player) getJdbcTemplate().queryForObject(
				new Object[] { playerId },
				new BeanPropertyRowMapper(Player.class));
		return player;

	 * create or inserts the new player information into the database using jdbcTemplate
	public String insertNewPlayer(Player player) {

		int returnValue = getJdbcTemplate().update(
				new Object[] { player.getName(), player.getAge(), player.getMatches() });
		if(1 == returnValue)
			return "Player creation is SUCCESS";
			return "Player creation is FAILURE";

	 * Retrieves all players from the database
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<Player> getAllPlayer() {

		List<Player> lstPlayers  = getJdbcTemplate().query(
				new BeanPropertyRowMapper(Player.class));
		return lstPlayers;

Time to Test !!

TestPlayerInfo class

This class is used to test the above Spring JDBC Template implementation


package com.spring.series.jdbc;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.spring.series.jdbc.dao.PlayerDAO;
import com.spring.series.jdbc.model.Player;

public class TestPlayerInfo {

	public static void main(String[] args) {

	 * Test method : invokes all three public DAO methods using Spring Dependency Injection after loading the context xml file
	private static void testSpringJdbcTemplate(){

		// loads the context xml and uses getBean() to retrieve the bean
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("com/spring/series/jdbc/SpringContext.xml");
		PlayerDAO playerDAO = (PlayerDAO) applicationContext.getBean("playerDAO");

		System.out.println("\nSpring JDBC Template Demostration using spring datasource");

		// invokes getPlayer() method supplying PLAYER_ID
		System.out.println("\nA. Invoking getPlayer() method supplying player_id in the formal argument");
		Player player1 = playerDAO.getPlayer(1);
		System.out.println(player1.getPlayerId() + "\t" + player1.getName() + "\t" + player1.getAge() + "\t" + player1.getMatches());

		// insert or save new player information into the database
		System.out.println("\nB. Creating or Saving new player in database");
		Player newPlayer = new Player("Lou Vincent", 36, 23);
		String returnStr = playerDAO.insertNewPlayer(newPlayer);
		System.out.println("Return message : " + returnStr);

		// invokes to retrieve all players from database
		System.out.println("\nC. Invoking getAllPlayers() method to retrieve all players from database");
		List<Player> lstPlayers = playerDAO.getAllPlayer();
		for(Player player : lstPlayers){
			System.out.println(player.getPlayerId() + "\t" + player.getName() + "\t" + player.getAge() + "\t" + player.getMatches());

Output in console

Aug 06, 2014 3:38:51 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@661a11: startup date [Wed Aug 06 15:38:51 IST 2014]; root of context hierarchy
Aug 06, 2014 3:38:51 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [com/spring/series/jdbc/SpringContext.xml]
Aug 06, 2014 3:38:51 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver

Spring JDBC Template Demostration using spring datasource

A. Invoking getPlayer() method supplying player_id in the formal argument

ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	41	200

B. Creating or Saving new player in database
Return message : Player creation is SUCCESS

C. Invoking getAllPlayers() method to retrieve all players from database

ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	41	200
2	Shane Warne		44	145
3	Kevin Pietersen	        34	104
4	Shahid Afridi		35	27
5	Brian Lara		45	131
6	Graeme Smith		34	117
7	Mahela Jayawardene	37	145
8	Lou Vincent		36	23

Conclusion: Using Spring JdbcTemplate helps us to reduce those few lines of boilerplate code in almost every method and significantly improving database operation faster

There are more rich classes available comparing JdbcTemplate which makes database operation even faster

  1. SimpleJdbcTemplate
  2. NamedJdbcTemplate

In the next articles, we will implement an example for both and also will review how we can use custom RowMapper to map java property to database column

Download project

Spring-JDBC-JdbcTemplate-using-Annotation (4kB)


Read Also:

Happy Coding !!
Happy Learning !!

Spring JDBC: An example on SimpleJdbcTemplate using Annotation
Spring JDBC
  • jayesh

    one error occures :java.lang.NoClassDefFoundError: org/springframework/jdbc/core/RowMapper please tell me how to solve it

    • SJ


      You are missing spring-jdbc jar in your deploy-able.
      Either use maven to resolve dependencies or else manually include jar into WEB-INF/lib folder of your web app.

  • Adil

    Very helpful post, It took only 30 minutes for me to create project and run… Cheers 🙂

    • SJ

      Thanks Adil !!

      Glad that it helped you !!

  • chandoo vamsi

    Hi Sir, Actually I followed your pattern but, failed to achieve it. Could you please help me. I posted my question in stackoverflow.com.

    • Vamsi,

      Can you elaborate on your doubt, exactly what error you are facing that will help.

      • chandoo vamsi

        Maven project standalone application sir.

        This is my source:
        model: Employee.java
        public class Employee {
        private int id;
        private String name;
        private String mobile;
        setters & getters
        DAO: EmployeeDAO.java
        public interface EmployeeDAO {
        public void insert(Employee emp);
        public class EmployeeDAOImpl extends JdbcDaoSupport implements EmployeeDAO {
        private DataSource dataSource;
        private JdbcTemplate jdbcTemplate;

        public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);

        public void insert(Employee emp) {
        // TODO Auto-generated method stub
        Main class:App.java
        public class App {
        private ApplicationContext context;
        private EmployeeDAO employeeDAO;

        public static void main(String[] args) {
        context = new ClassPathXmlApplicationContext(“Spring-Datasource.xml”);
        employeeDAO = (EmployeeDAO) context.getBean(“employeeDAO.class”);
        Xml configuration file;Spring-Datasource.xml

        Error: Exception in thread “main” org.springframework.beans.factory.NoSuchBeanDefinitionException: No bean named ’employeeDAOImpl’ is defined

        folder structure and every thing is clearly shown at: https://stackoverflow.com/questions/44084321/springjdbc-classpathxmlapplicationcontext-unable-to-load-getbean-from-xml-in-res

        Sir, I asked few of my colleagues but, couldn’t make out. Please help me or send me some relevant code. I am noob in java.

        • Provide setter & getter for DAO class and

          Also get bean using just bean id like,

          employeeDAO = (EmployeeDAO) context.getBean(“employeeDAO”);

          instead of

          employeeDAO = (EmployeeDAO) context.getBean(“employeeDAO.class”);

          Let me know, your result

          • chandoo vamsi

            Still the same sir. No bean named ’employeeDAOImpl’ is defined. Sir, I followed your example aswell. It also ended with same error(no bean defined). Dont know where i did the mistake. Could you please send me any relevant code with main as well.

          • Can you zip your project and send to BenchResources.Net@gmail.com

            I can take a quick look into it.

          • chandoo vamsi

            Thanks a lot boss. you are an amazing.

          • Replied to your email, check.