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
- 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
- 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
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
- SimpleJdbcTemplate
- 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:
- Introduction to Spring JDBC
- Spring JDBC example on SimpleJdbcTemplate using Annotation
- Spring JDBC example on NamedParameterJdbcTemplate using Annotation
- Spring JDBC using JdbcDaoSupport
Happy Coding !!
Happy Learning !!