In this article, we will implement an example on SimpleJdbcTemplate using annotation. Using SimpleJdbcTemplate over JdbcTemplate, helps to reduce the developers task of manually casting to the required type from the returning query and no need to pass input parameters as Object array. Read on for more explanation
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
- spring-core-4.0.0-RELEASE
- spring-context-4.0.0-RELEASE
- spring-beans-4.0.0-RELEASE
- spring-aop-4.0.0-RELEASE
- spring-expression-4.0.0-RELEASE
- spring-tx-4.0.0-RELEASE
- spring-jdbc-4.0.0-RELEASE
- mysql-connector-java-5.1.31
- commons-logging-1.1.1
- aopalliance-1.0
Project Structure (Package Explorer view in Eclipse)
Jars Libraries Used in the Project (Maven Dependencies)
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"> <context:annotation-config></context:annotation-config> <context:component-scan base-package="com.spring.series.jdbc.dao.impl"></context:component-scan> <bean id="simpleJdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate"> <constructor-arg ref="dataSource" /> </bean> <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 SimpleJdbcTemplate is annotated with @Autowired, both of which scanned and registered with the application context as we have already instructed this through the context xml
SimpleJdbcTemplate is simpler while coding than comparing with JdbcTemplate. Take a look at the below comparison
JdbcTemplate vs SimpleJdbcTemplate
JdbcTemplate | SimpleJdbcTemplate |
Manually need to cast the returning object or list of objects from the query, making the developers work more cumbersome | No overhead of casting to the required type as it does automatically (Rich API) |
Passing the input arguments as an object array to the query, should be in the format “new Object[]{ …. }”. For example, new Object[] { playerId, name, age, matches } |
Whereas in this case, we can simply pass the value as the input arguments. For example, playerId or player_id, name, age and matches |
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.simple.ParameterizedBeanPropertyRowMapper; import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; 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 SimpleJdbcTemplate simpleJdbcTemplate; public SimpleJdbcTemplate getSimpleJdbcTemplate() { return simpleJdbcTemplate; } /** * create or inserts the new player information into the database using simpleJdbcTemplate */ @SuppressWarnings("deprecation") public String createOrSaveNewPLayer(Player player) { String sql = "INSERT INTO PLAYER(NAME, AGE, MATCHES) VALUES(?, ?, ?)"; int returnValue = getSimpleJdbcTemplate().update( sql, player.getName(), player.getAge(), player.getMatches()); 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 */ @SuppressWarnings("deprecation") public Player getPlayer(int playerId) { String sql = "SELECT PLAYER_ID, NAME, AGE, MATCHES FROM PLAYER WHERE PLAYER_ID = ?"; Player player = getSimpleJdbcTemplate().queryForObject( sql, ParameterizedBeanPropertyRowMapper.newInstance(Player.class), playerId); return player; } /** * This method updates the player information in the database using simpleJdbcTemplate */ @SuppressWarnings("deprecation") public String updatePlayerInfo(Player player) { String sql = "UPDATE PLAYER SET NAME = ?, AGE = ?, MATCHES = ? WHERE PLAYER_ID = ?"; int returnValue = getSimpleJdbcTemplate().update( sql, player.getName(), player.getAge(), player.getMatches(), player.getPlayerId()); 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 */ @SuppressWarnings("deprecation") public String deletePlayerInfo(String player_id) { String sql = "DELETE FROM PLAYER WHERE PLAYER_ID = ?"; int returnValue = getSimpleJdbcTemplate().update( sql, player_id); if(1 == returnValue) return "Player deletion is SUCCESS"; else return "Player deletion is FAILURE"; } /** * Retrieves all players from the database using simpleJdbcTemplate */ @SuppressWarnings({ "rawtypes", "unchecked", "deprecation" }) public List<Player> getAllPlayer() { // local variables String sql = "SELECT PLAYER_ID, NAME, AGE, MATCHES FROM PLAYER"; List<Player> lstPlayers = getSimpleJdbcTemplate().query( sql, new BeanPropertyRowMapper(Player.class)); return lstPlayers; } }
Time to Test !!
TestPlayerInfo class
This class is used to test the above Spring Simple 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) { testSpringSimpleJdbcTemplate(); } /** * Test method : invokes all public DAO methods using Spring Dependency Injection after loading the context xml file */ private static void testSpringSimpleJdbcTemplate(){ // 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: With SimpleJdbcTemplate, developer task becomes easy to concentrate on the business logic rather than on the syntax and typecast overhead
In the next article, we will implement the same example using NamedParameterJdbcTemplate and also will review how we can use custom RowMapper to map java property to database column
Download project
Spring-JDBC-SimpleJdbcTemplate-using-Annotation (5kB)
Read Also:
- Introduction to Spring JDBC
- Spring JDBC example on JdbcTemplate using Annotation
- Spring JDBC example on NamedParameterJdbcTemplate using Annotation
- Spring JDBC using JdbcDaoSupport
Happy Coding !!
Happy Learning !!