Spring JDBC: An example on NamedParameterJdbcTemplate using Annotation

In this article, we will implement an example on NamedParameterJdbcTemplate using annotation. This approach is similar to JdbcTemplate, but the placeholder (?) replaced by named parameter and this improves the readability and easier to maintain it

In the previous articles on SimpleJdbcTemplate/JdbcTemplate where SQL parameters are represented by “?” and its bind by position and problem is whenever the order changes developer needs to change the parameter bindings as well, making it’s error prone and cumbersome to maintain it

Note: Named Parameters are supported only in SimpleJdbcTemplate & NamedParameterJdbcTemplate

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_SpringNamedParameterJdbcTemplate_Project_Structure_In_Eclipse

Jars Libraries Used in the Project (Maven Dependencies)

2_SpringNmedParameterJdbcTemplate_Jars_In_Classpath

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
  • simpleJdbcTemplate bean which injects the dataSource bean configured in the XML using constructor injection
  • <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" />

	<!-- namedParameterJdbcTemplate uses dataSource via constructor-injection -->
	<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
		<constructor-arg 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 && no-arg constructor, 3-arg constructor and 4-arg constructor

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

	// 4-arg parameterized-constructor
	public Player(int playerId, String name, int age, int matches) {
		super();
		this.playerId = playerId;
		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 CRUD operation to interact with database

  • create or save new player information into the database
  • get player based on the PLAYER_ID
  • update player information taking player object as input argument
  • delete player information from the database with playerId as formal argument
  • get all players stored in the database from PLAYER table

 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 {

	// simple CRUD operations
	public String createOrSaveNewPLayer(Player player);
	public Player getPlayer(int playerId);
	public String updatePlayerInfo(Player player);
	public String deletePlayerInfo(String playerId);
	public List<Player> getAllPlayer();
}

PlayerDAOImpl class

This Impl class implements above interface which exposes CRUD like operations
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 NamedParameterJdbcTemplate is annotated with @Autowired, both of which scanned and registered with the application context as we have already instructed this through the context xml

Named Parameter

Used Named Parameter instead of using placeholder (?) which improves the readability and order changes doesn’t impact the implementation

Row Mapper

In our earlier examples on JdbcTemplate/SimpleJdbcTemplate, we have directly mapped using the readily available classes from spring “BeanPropertyRowMapper” or “ParameterizedBeanPropertyRowMapper” provided both has the same name

But using custom row mapper developer has the flexibility of mapping the java properties to database column of any value. See the PlayerRowMapper class below

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.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

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

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

	// member variables
	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	/**
	 * create or inserts the new player information into the database using simpleJdbcTemplate
	 */
	public String createOrSaveNewPLayer(Player player) {

		// local variables
		String sql = "INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES( :name, :age, :matches)";
		SqlParameterSource namedParameters = new MapSqlParameterSource()
		.addValue("name", player.getName())
		.addValue("age", player.getAge())
		.addValue("matches", player.getMatches());

		int returnValue = getNamedParameterJdbcTemplate().update(
				sql,
				namedParameters);

		if(1 == returnValue)
			return "Player creation is SUCCESS";
		else
			return "Player creation is FAILURE";
	}

	/**
	 * This method retrieves a player from database using jdbcTemplate based on the PLAYER_ID supplied in the formal arguments
	 */
	public Player getPlayer(int playerId) {

		String sql = "SELECT PLAYER_ID, NAME, AGE, MATCHES FROM PLAYER WHERE PLAYER_ID = :player_id";
		SqlParameterSource namedParameters = new MapSqlParameterSource("player_id", playerId);
		Player player = getNamedParameterJdbcTemplate().queryForObject(
				sql,
				namedParameters,
				new PlayerRowMapper());
		return player;
	}

	/**
	 * This method updates the player information in the database using simpleJdbcTemplate
	 */
	public String updatePlayerInfo(Player player) {

		String sql = "UPDATE PLAYER SET NAME = :name, AGE = :age, MATCHES = :matches WHERE PLAYER_ID = :player_id";
		SqlParameterSource namedParameters = new MapSqlParameterSource()
		.addValue("name", player.getName())
		.addValue("age", player.getAge())
		.addValue("matches", player.getMatches())
		.addValue("player_id", player.getPlayerId());

		int returnValue = getNamedParameterJdbcTemplate().update(
				sql,
				namedParameters);

		if(1 == returnValue)
			return "Player updation is SUCCESS";
		else
			return "Player updation is FAILURE";
	}

	/**
	 * This method deletes the player information from the database using simpleJdbcTemplate
	 */
	public String deletePlayerInfo(String player_id) {

		String sql = "DELETE FROM PLAYER WHERE PLAYER_ID =:playerId ";
		SqlParameterSource namedParameters = new MapSqlParameterSource("playerId", player_id);

		int returnValue = getNamedParameterJdbcTemplate().update(
				sql,
				namedParameters);

		if(1 == returnValue)
			return "Player deletion is SUCCESS";
		else
			return "Player deletion is FAILURE";
	}

	/**
	 * Retrieves all players from the database using simpleJdbcTemplate
	 */
	public List<Player> getAllPlayer() {

		// local variables
		String sql = "SELECT PLAYER_ID, NAME, AGE, MATCHES FROM PLAYER";
		List<Player> lstPlayers  = getNamedParameterJdbcTemplate().query(
				sql,
				new PlayerRowMapper());
		return lstPlayers;
	}
}

PlayerRowMapper.java

package com.spring.series.jdbc.utils;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

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

public class PlayerRowMapper implements RowMapper<Player> {

	public Player mapRow(ResultSet resultSet, int rowNumber) throws SQLException {
		Player player = new Player();
		player.setPlayerId(resultSet.getInt("PLAYER_ID"));
		player.setName(resultSet.getString("NAME"));
		player.setAge(resultSet.getInt("AGE"));
		player.setMatches(resultSet.getInt("MATCHES"));
		return player;
	}
}

Time to Test !!

TestPlayerInfo class

This class is used to test the above Spring NamedParameterJdbcTemplate 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) {
		testSpringNamedParameterJdbcTemplate();
	}

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

		// 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 Simple JDBC Template Demostration using spring datasource");

		// invokes createOrSaveNewPLayer() method
		System.out.println("\nA. Invkoing createOrSaveNewPLayer() method to create/save new player information");
		Player newPlayer = new Player("Tamim Iqbal", 24, 19);
		String strCreateOrSave = playerDAO.createOrSaveNewPLayer(newPlayer);
		System.out.println("Return message : " + strCreateOrSave);

		// invokes getPlayer() method
		System.out.println("\nB. Invkoing getPlayer() method to retrieve player based on the player_id supplied in the formal argument");
		Player player5 = playerDAO.getPlayer(1);
		System.out.println("ID\tName\t\t\tAge\tMatches");
		System.out.println("==\t================\t===\t=======");
		System.out.println(player5.getPlayerId() + "\t" + player5.getName() + "\t" + player5.getAge() + "\t" + player5.getMatches());

		// invokes updatePlayerInfo() method
		System.out.println("\nC. Invkoing updatePlayerInfo() method to update Player information");
		Player updatePlayer = new Player(2, "Shane Warne", 45, 149);
		String strUpdate = playerDAO.updatePlayerInfo(updatePlayer);
		System.out.println("Return message : " + strUpdate);

		// invokes deletePlayerInfo() method
		System.out.println("\nD. Invkoing deletePlayerInfo() method to delete player inforamtion from the database");
		String strDelete = playerDAO.deletePlayerInfo("7");
		System.out.println("Return message : " + strDelete);

		// invokes getAllPlayer() method
		System.out.println("\nE. Invkoing getAllPlayer() method to retrieve all players from the database");
		System.out.println("ID\tName\t\t\tAge\tMatches");
		System.out.println("==\t================\t===\t=======");
		List<Player> lstPlayer = playerDAO.getAllPlayer();
		for(Player player : lstPlayer){
			System.out.println(player.getPlayerId() + "\t" + player.getName() + "\t" + player.getAge() + "\t" + player.getMatches());
		}
	}
}

Output in console

Aug 06, 2014 12:03:48 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@817d6: startup date [Wed Aug 06 00:03:47 IST 2014]; root of context hierarchy
Aug 06, 2014 12:03:48 AM 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 12:03:48 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver

Spring Simple JDBC Template Demostration using spring datasource

A. Invkoing createOrSaveNewPLayer() method to create/save new player information
Return message : Player creation is SUCCESS

B. Invkoing getPlayer() method to retrieve player based on the player_id supplied in the formal argument
ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	41	200

C. Invkoing updatePlayerInfo() method to update Player information
Return message : Player updation is SUCCESS

D. Invkoing deletePlayerInfo() method to delete player inforamtion from the database
Return message : Player deletion is SUCCESS

E. Invkoing getAllPlayer() method to retrieve all players from the database
ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	41	200
2	Shane Warne		45	149
3	Kevin Pietersen	        34	104
4	Shahid Afridi		35	27
5	Brian Lara		45	131
6	Graeme Smith		34	117
8	Tamim Iqbal		24	19

Conclusion: NamedParameter helps to increase the readability of the code and it is lot easier to maintain. In the next article, we will see an example on JdbcDaoSupport

Download project

Spring-JDBC-NamedParameterJdbcTemplate-using-Annotation (5kB)

 

Read Also:

Happy Coding !!
Happy Learning !!

Spring JDBC: Using JdbcDaoSupport
Spring JDBC: An example on SimpleJdbcTemplate using Annotation