Spring JDBC

In this article, we will implement a simple example based on Spring JDBC concepts

Technology Used

  • Java 1.7
  • Eclipse Luna IDE
  • Spring-4.0.0-RELEASE
  • Apache-Maven-3.2.1
  • Apache-Tomcat-7.0.54
  • 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_Spring_JDBC_Project_Structure_In_Eclipse

Jars Libraries Used in the Project (Maven Dependencies)

2_Spring_JDBC_Libraries_Used

Spring context configuration

This Spring Bean xml defines two beans

  • a datasource with value for drivername, url, username and password
  • a playerDAO bean for the implementation class of Spring JDBC

Note: datasource injected into playerDAO bean via setter injection

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

	<bean id="playerDAO" class="com.spring.series.jdbc.dao.impl.PlayerDAOImpl">
		<property name="dataSource" 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

Player.java

package com.spring.series.jdbc.model;

public class Player {

	// local variables
	private int playerId;
	private String name;
	private int age;
	private int 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, out of which we will implement only one to retrieve all players from the database table i.e.; getAllPlayer();

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.
Just implemented one public method getAllPlayer();
It uses Spring JDBC configured in the SpringContext.xml file to interact with MySql database to retrieve all players

If you compare this class with the earlier JDBC example without spring, you could see those boilerplate codes are reduced keeping the class simple and concise and placing configuration details in SpringContext xml file. And below steps aren’t needed anymore

  • loading driver class
  • getConnection() using DriverManager
  • supplying database credentials like URL, username and password
  • all the above steps are configured using spring context XML file

PlayerDAOImpl.java

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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.sql.DataSource;

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

public class PlayerDAOImpl implements PlayerDAO {

	private DataSource dataSource;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public Player getPlayer(int playerId) {
		// TODO Auto-generated method stub
		return null;
	}

	public String insertNewPlayer(Player player) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Player> getAllPlayer() {

		// local variables
		String sql = "SELECT PLAYER_ID, NAME, AGE, MATCHES FROM PLAYER";
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);

			System.out.println("ID\tName\t\t\tAge\tMatches");
			System.out.println("==\t================\t===\t=======");

			// extracts resultset and get each value
			while(resultSet.next()){
				System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getInt(3) + "\t" + resultSet.getInt(4));
			}
		}
		catch (SQLException sqle) {
			throw new RuntimeException(sqle);
		}
		finally {
			if (connection != null) {
				try {
					connection.close();
				}
				catch (SQLException sqle) {}
			}
		}
		return null;
	}
}

Time to Test !!

TestPlayerInfo class

This class is used to test the above Spring JDBC implementation and to view the output in the console

TestPlayerInfo.java

package com.spring.series.jdbc;

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

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

public class TestPlayerInfo {

	public static void main(String[] args) {
		testSpringJDBC();
	}

	private static void testSpringJDBC(){

		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("com/spring/series/jdbc/SpringContext.xml");
		PlayerDAO playerDAO = (PlayerDAO) applicationContext.getBean("playerDAO");

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

		playerDAO.getAllPlayer();
	}
}

Output in console

Aug 06, 2014 5:04:55 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@ad1355: startup date [Wed Aug 06 17:04:55 IST 2014]; root of context hierarchy
Aug 06, 2014 5:04:55 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 5:04:55 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver

Spring JDBC Demostration using spring datasource

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

Conclusion: Thus, moving the database credentials from the programming code to spring context configuration helps in minimize the errors in the coding. But still developers hands gets dirty by writing the code for creating/closing connections and handling exceptions

In the next article we will extend the same example using Spring JdbcTemplate and see how it helps in making query to the database yields faster performance

Download project

Spring-JDBC-Integration (4kB)

 

Read Also:

Happy Coding !!
Happy Learning !!

Spring JDBC: An example on JdbcTemplate using Annotation
Spring JDBC: Introduction and JDBC example without spring