Spring JDBC Tutorial

Spring JDBC is the topic of this tutorial. Databases are an integral part of most of the Enterprise Applications. So when it comes to a Java EE framework, having good integration with JDBC is very important.

Spring JDBC

spring jdbc, spring jdbc example, spring JDBCTemplate, JDBCTemplateSpring Framework provides excellent integration with JDBC API and provides JdbcTemplate utility class that we can use to avoid boiler-plate code from our database operations logic such as Opening/Closing Connection, ResultSet, PreparedStatement etc. Let’s first look at a simple Spring JDBC example application and then we will see how JdbcTemplate class can help us in writing modular code with ease, without worrying whether resources are closed properly or not. Spring Tool Suite to develop Spring based applications is very helpful, so we will use STS to create our Spring JDBC application. Our final project structure will look like below image.

Create a simple Spring Maven Project from the STS Menu, you can choose whatever name you like or stick with my project name as SpringJDBCExample.

Spring JDBC Dependencies

First of all, we need to include Spring JDBC and Database drivers in the Maven project pom.xml file. My final pom.xml file looks like below.

<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.springframework.samples</groupId>
    <artifactId>SpringJDBCExample</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <!-- Generic properties -->
        <java.version>1.6</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

        <!-- Spring -->
        <spring-framework.version>4.0.2.RELEASE</spring-framework.version>

        <!-- Logging -->
        <logback.version>1.0.13</logback.version>
        <slf4j.version>1.7.5</slf4j.version>
    </properties>

    <dependencies>
        <!-- Spring and Transactions -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring-framework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring-framework.version}</version>
        </dependency>

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

        <!-- MySQL Driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.0.5</version>
        </dependency>

        <!-- Logging with SLF4J & LogBack -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
            <scope>runtime</scope>
        </dependency>

    </dependencies>
</project>

Spring JDBC Example – Database Setup

Let’s create a simple table that we will use in our application for CRUD operations example.

CREATE TABLE `Employee` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `role` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Spring JDBC Example – Model Class

We will use DAO Pattern for JDBC operations, so let’s create a Java bean that will model our Employee table.

package com.journaldev.spring.jdbc.model;

public class Employee {

    private int id;
    private String name;
    private String role;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getRole() {
        return role;
    }
    public void setRole(String role) {
        this.role = role;
    }
    
    @Override
    public String toString(){
        return "{ID="+id+",Name="+name+",Role="+role+"}";
    }
}

Most of the part is automatically generated by STS, however, I have updated the Spring Framework version to use the latest version as 4.0.2.RELEASE. Also, we have added required artifacts spring-jdbc and mysql-connector-java. The first one contains the Spring JDBC support classes and the second one is the database driver. I am using MySQL database for our testing purposes, so I have added MySQL JConnector jar dependencies. If you are using some other RDBMS then you should make the corresponding changes in the dependencies.

Spring JDBC Example – DAO Interface and Implementation

For the DAO pattern, we will first have an interface declaring all the operations we want to implement.


package com.journaldev.spring.jdbc.dao;

import java.util.List;
import com.journaldev.spring.jdbc.model.Employee;

//CRUD operations
public interface EmployeeDAO {
    
    //Create
    public void save(Employee employee);
    //Read
    public Employee getById(int id);
    //Update
    public void update(Employee employee);
    //Delete
    public void deleteById(int id);
    //Get All
    public List<Employee> getAll();
}


package com.journaldev.spring.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.journaldev.spring.jdbc.model.Employee;

public class EmployeeDAOImpl implements EmployeeDAO {

	private DataSource dataSource;

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

	@Override
	public void save(Employee employee) {
		String query = "insert into Employee (id, name, role) values (?,?,?)";
		Connection con = null;
		PreparedStatement ps = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			ps.setInt(1, employee.getId());
			ps.setString(2, employee.getName());
			ps.setString(3, employee.getRole());
			int out = ps.executeUpdate();
			if(out !=0){
				System.out.println("Employee saved with id="+employee.getId());
			}else System.out.println("Employee save failed with id="+employee.getId());
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public Employee getById(int id) {
		String query = "select name, role from Employee where id = ?";
		Employee emp = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				emp = new Employee();
				emp.setId(id);
				emp.setName(rs.getString("name"));
				emp.setRole(rs.getString("role"));
				System.out.println("Employee Found::"+emp);
			}else{
				System.out.println("No Employee found with id="+id);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return emp;
	}

	@Override
	public void update(Employee employee) {
		String query = "update Employee set name=?, role=? where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			ps.setString(1, employee.getName());
			ps.setString(2, employee.getRole());
			ps.setInt(3, employee.getId());
			int out = ps.executeUpdate();
			if(out !=0){
				System.out.println("Employee updated with id="+employee.getId());
			}else System.out.println("No Employee found with id="+employee.getId());
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void deleteById(int id) {
		String query = "delete from Employee where id=?";
		Connection con = null;
		PreparedStatement ps = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			ps.setInt(1, id);
			int out = ps.executeUpdate();
			if(out !=0){
				System.out.println("Employee deleted with id="+id);
			}else System.out.println("No Employee found with id="+id);
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public List getAll() {
		String query = "select id, name, role from Employee";
		List empList = new ArrayList();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			con = dataSource.getConnection();
			ps = con.prepareStatement(query);
			rs = ps.executeQuery();
			while(rs.next()){
				Employee emp = new Employee();
				emp.setId(rs.getInt("id"));
				emp.setName(rs.getString("name"));
				emp.setRole(rs.getString("role"));
				empList.add(emp);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return empList;
	}

}

The implementation of CRUD operations are simple to understand. If you want to learn more about DataSource, please read JDBC DataSource Example.

Spring JDBC Example – Bean Configuration

Spring JDBC framework classes come into the picture when we create a Spring Bean Configuration file and define the beans. We will create the DataSource in the Spring Bean context file and set it to our DAO implementation class. My Spring Bean Configuration file looks like below.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="https://www.springframework.org/schema/beans"
	xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="https://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd">

	<bean id="employeeDAO" class="com.journaldev.spring.jdbc.dao.EmployeeDAOImpl">
		<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/TestDB" />
		<property name="username" value="pankaj" />
		<property name="password" value="pankaj123" />
	</bean>

</beans>

First, we create a DataSource object of class DriverManagerDataSource. This class provides the basic implementation of DataSource that we can use. We are passing MySQL database URL, username, and password as properties to the DataSource bean. The dataSource bean is set to the EmployeeDAOImpl bean, readying our Spring JDBC implementation. This implementation is loosely coupled, and if we want to switch to some other implementation or move to another database server, all we need is to make corresponding changes in the bean configurations. This is one of the major advantages provided by the Spring JDBC framework.

Spring JDBC Test Class

Let’s write a simple test class to make sure everything is working fine.

package com.journaldev.spring.jdbc.main;

import java.util.List;
import java.util.Random;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.journaldev.spring.jdbc.dao.EmployeeDAO;
import com.journaldev.spring.jdbc.model.Employee;

public class SpringMain {

    public static void main(String[] args) {
        //Get the Spring Context
        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
        
        //Get the EmployeeDAO Bean
        EmployeeDAO employeeDAO = ctx.getBean("employeeDAO", EmployeeDAO.class);
        
        //Run some tests for JDBC CRUD operations
        Employee emp = new Employee();
        int rand = new Random().nextInt(1000);
        emp.setId(rand);
        emp.setName("Pankaj");
        emp.setRole("Java Developer");
        
        //Create
        employeeDAO.save(emp);
        
        //Read
        Employee emp1 = employeeDAO.getById(rand);
        System.out.println("Employee Retrieved::"+emp1);
        
        //Update
        emp.setRole("CEO");
        employeeDAO.update(emp);
        
        //Get All
        List empList = employeeDAO.getAll();
        System.out.println(empList);
        
        //Delete
        employeeDAO.deleteById(rand);
        
        //Close Spring Context
        ctx.close();
        
        System.out.println("DONE");
    }

}

I am using Random Class to generate a random number for the employee id. When we run the above program, we get the following output.

Mar 25, 2014 12:54:18 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@4b9af9a9: startup date [Tue Mar 25 12:54:18 PDT 2014]; root of context hierarchy
Mar 25, 2014 12:54:18 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring.xml]
Mar 25, 2014 12:54:19 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Employee saved with id=726
Employee Found::{ID=726,Name=Pankaj,Role=Java Developer}
Employee Retrieved::{ID=726,Name=Pankaj,Role=Java Developer}
Employee updated with id=726
[{ID=726,Name=Pankaj,Role=CEO}]
Employee deleted with id=726
Mar 25, 2014 12:54:19 PM org.springframework.context.support.ClassPathXmlApplicationContext doClose
INFO: Closing org.springframework.context.support.ClassPathXmlApplicationContext@4b9af9a9: startup date [Tue Mar 25 12:54:18 PDT 2014]; root of context hierarchy
DONE

Spring JdbcTemplate Example

If you look at the DAO implementation class, there is a lot of boiler-plate code where we are opening and closing Connection, PreparedStatements, and ResultSet. This can lead to resource leak if someone forgets to close the resources properly. We can use org.springframework.jdbc.core.JdbcTemplate class to avoid these errors. Spring JdbcTemplate is the central class in Spring JDBC core package and provides a lot of methods to execute queries and automatically parse ResultSet to get the Object or list of Objects. All we need is to provide the arguments as Object array and implement Callback interfaces such as PreparedStatementSetter and RowMapper for mapping arguments or converting ResultSet data to bean objects. Let’s look at another implementation of EmployeeDAO where we will use Spring JdbcTemplate class for executing different types of queries.

package com.journaldev.spring.jdbc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.journaldev.spring.jdbc.model.Employee;

public class EmployeeDAOJDBCTemplateImpl implements EmployeeDAO {

	private DataSource dataSource;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	@Override
	public void save(Employee employee) {
		String query = "insert into Employee (id, name, role) values (?,?,?)";
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		Object[] args = new Object[] {employee.getId(), employee.getName(), employee.getRole()};
		
		int out = jdbcTemplate.update(query, args);
		
		if(out !=0){
			System.out.println("Employee saved with id="+employee.getId());
		}else System.out.println("Employee save failed with id="+employee.getId());
	}

	@Override
	public Employee getById(int id) {
		String query = "select id, name, role from Employee where id = ?";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		//using RowMapper anonymous class, we can create a separate RowMapper for reuse
		Employee emp = jdbcTemplate.queryForObject(query, new Object[]{id}, new RowMapper(){

			@Override
			public Employee mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Employee emp = new Employee();
				emp.setId(rs.getInt("id"));
				emp.setName(rs.getString("name"));
				emp.setRole(rs.getString("role"));
				return emp;
			}});
		
		return emp;
	}

	@Override
	public void update(Employee employee) {
		String query = "update Employee set name=?, role=? where id=?";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		Object[] args = new Object[] {employee.getName(), employee.getRole(), employee.getId()};
		
		int out = jdbcTemplate.update(query, args);
		if(out !=0){
			System.out.println("Employee updated with id="+employee.getId());
		}else System.out.println("No Employee found with id="+employee.getId());
	}

	@Override
	public void deleteById(int id) {

		String query = "delete from Employee where id=?";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		int out = jdbcTemplate.update(query, id);
		if(out !=0){
			System.out.println("Employee deleted with id="+id);
		}else System.out.println("No Employee found with id="+id);
	}

	@Override
	public List getAll() {
		String query = "select id, name, role from Employee";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		List empList = new ArrayList();

		List<Map<String,Object>> empRows = jdbcTemplate.queryForList(query);
		
		for(Map<String,Object> empRow : empRows){
			Employee emp = new Employee();
			emp.setId(Integer.parseInt(String.valueOf(empRow.get("id"))));
			emp.setName(String.valueOf(empRow.get("name")));
			emp.setRole(String.valueOf(empRow.get("role")));
			empList.add(emp);
		}
		return empList;
	}

}

Important Points About Spring JdbcTemplate

  • Use of Object array to pass PreparedStatement arguments, we could also use PreparedStatementSetter implementation but passing Object array seems easy to use.
  • No code related to opening and closing connections, statements, or result set. All that is handled internally by Spring JdbcTemplate class.
  • RowMapper anonymous class implementation to map the ResultSet data to Employee bean object in queryForObject() method.
  • queryForList() method returns a list of Maps, where each Map contains the row data mapped with key as the column name and value from the database row matching the criteria.

To use Spring JdbcTemplate implementation, all we need is to change the employeeDAO class in the Spring Bean configuration file as shown below.

<bean id="employeeDAO" class="com.journaldev.spring.jdbc.dao.EmployeeDAOJDBCTemplateImpl">
    <property name="dataSource" ref="dataSource" />
</bean>

When you run the main class, the output of Spring JdbcTemplate implementation will be similar to the one seen above with normal JDBC implementation. That’s all for Spring JDBC Example tutorial.

Create a Free Account

Register now and get access to our Cloud Services.

Posts you might be interested in:

centron Managed Cloud Hosting in Deutschland

Prisma Tutorial

Databases, Tutorial
Prisma Tutorial Content1 Introduction2 Prerequisites3 Step 1 — Creating Your TypeScript Project4 Step 2 — Setting Up Prisma with PostgreSQL5 Install Prisma CLI6 Set Up PostgreSQL with Docker7 Launch PostgreSQL…