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

	<dependencies>
		<!-- Spring Core and Context -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- Spring JDBC -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- MySql-Connector -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.31</version>
		</dependency>
	</dependencies>

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)

1_SpringJDBCTemplate_Project_Structure_In_Eclipse

Jars Libraries Used in the Project (Maven Dependencies)

2_SpringJDBCTemplate_Libraries_Used

Creating tables and inserting few records

Create table command

CREATE TABLE `PLAYER` (
  `PLAYER_ID` INT(6) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(50) NOT NULL,
  `AGE` INT(3) NOT NULL,
  `MATCHES` INT(3) NOT NULL,
  PRIMARY KEY (`PLAYER_ID`)
);

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

SpringContext.xml

<?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" />
	</bean>

	<!-- 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="" />
	</bean>
</beans>




Let’s see coding in action

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

Player.java

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() {
		super();
	}

	// 3-arg parameterized-constructor
	public Player(String name, int age, int matches) {
		super();
		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

PlayerDAO.java

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

Annotation

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
3_SpringJDBCTemplate_query_variants
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

PlayerDAOImpl.java

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;

@Repository("playerDAO")
public class PlayerDAOImpl implements PlayerDAO {

	@Autowired
	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) {

		String sql = "SELECT PLAYER_ID, NAME, AGE, MATCHES FROM PLAYER WHERE PLAYER_ID = ?";
		Player player = (Player) getJdbcTemplate().queryForObject(
				sql,
				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) {

		String sql = "INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES(?, ?, ?)";
		int returnValue = getJdbcTemplate().update(
				sql,
				new Object[] { player.getName(), player.getAge(), player.getMatches() });
		if(1 == returnValue)
			return "Player creation is SUCCESS";
		else
			return "Player creation is FAILURE";
	}

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

		String sql = "SELECT PLAYER_ID, NAME, AGE, MATCHES FROM PLAYER";
		List<Player> lstPlayers  = getJdbcTemplate().query(
				sql,
				new BeanPropertyRowMapper(Player.class));
		return lstPlayers;
	}
}

Time to Test !!

TestPlayerInfo class

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

TestPlayerInfo.java

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) {
		testSpringJdbcTemplate();
	}

	/**
	 * 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("\nID\tName\t\t\tAge\tMatches");
		System.out.println("==\t================\t===\t=======");
		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();
		System.out.println("\nID\tName\t\t\tAge\tMatches");
		System.out.println("==\t================\t===\t=======");
		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 !!

  • jayesh

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

    • SJ

      Jayesh,

      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 !!