Spring JDBCTemplate example

Spring JDBCTemplate example

Spring JDBC provides api support for implementing JDBC code using Spring framework. CRUD operation using Spring JDBC has been explained with example in the blog Spring JDBC. There are certain benefits of using Spring JDBC; however, there are some drawbacks as listed below:

  1. Developers are required to implement exception handling code.
  2. Developers are required to implement code which will be executed prior and post to query execution, such as making connection, statement and resultset. Once execution has finished, connections should be closed and cleaned up.
  3. Developers need to manage everything around the transactions. For example, if something went wrong while saving data to database. They have to write the code to rollback database to previous state.

To overcome above problems, Spring framework developers has come up with Spring JDBCTemplate, which handles all overhead like connection and statement management and also exception handling. Thus, providing developers to write their query and focus on business logic. Here in this tutorial we will demonstrate example of CRUD operation using spring JDBCTemplate.

Technologies used in this examples are :

  1. JDK 1.7
  2. Maven as a build tool.
  3. Spring 4.1, however you can use lower versions of spring as well.
  4. Mysql as a database.

This example has been implemented using Eclipse and maven plugin are used to create the basic folder structure for maven java project. Few other files have been included to implement this example. The final folder structure looks like the image below.

spring jdbctemplate

1) Spring JDBCTemplate dependencies.

Add Spring JDBC and mysql dependencies into the pom.xml file.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>springjdbctemplate</groupId>
  <artifactId>springjdbctemplate</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springjdbctemplate</name>
  <url>http://maven.apache.org</url>

 <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <!-- Spring -->
	<spring.version>4.1.0.RELEASE</spring.version>
    <mysql.version>5.1.32</mysql.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>${mysql.version}</version>
    </dependency>
    <!-- Spring JDBC Support -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>${spring.version}</version>
		</dependency>
  </dependencies>
</project>

2) Table creation in Database

We are using mysql database, using which Emp table will be created with below database script.

CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `firstName` varchar(45) DEFAULT NULL,
  `lastName` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3) Emp model object.

A model to store Emp data is required, so we’ll create Emp model class as below.

package com.code2succeed.beans;

public class Emp {
	private int id;
	private String firstName;
	private String lastName;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	
	public String toString() {
		StringBuilder sb = new StringBuilder();
		sb.append("Id : "+id);
		sb.append(", FirstName : "+firstName);
		sb.append(", LastName : "+lastName);
		
		return sb.toString();
	}
}

4) Mapper and Extractor class for Emp Bean.

Mapper class : It performs the actual work of mapping each row to a result object. Developers need not worry about exception handling, SQLException will be caught and handled by the calling JDBCTemplate.

package com.code2succeed.mapper;

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

import org.springframework.jdbc.core.RowMapper;

import com.code2succeed.beans.Emp;
import com.code2succeed.extractor.EmpExtractor;

public class EmpRowMapper implements RowMapper<Emp>{

	public Emp mapRow(ResultSet resultSet, int line) throws SQLException {
		EmpExtractor empExtractor = new EmpExtractor();
		return empExtractor.extractData(resultSet);
	}

}

Extractor class : It contains logic to create object(beans) from resultset.

package com.code2succeed.extractor;

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

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.code2succeed.beans.Emp;

public class EmpExtractor implements ResultSetExtractor<Emp>{

	public Emp extractData(ResultSet resultSet) throws SQLException, DataAccessException {
		Emp emp = new Emp();
		int id = resultSet.getInt("id");
		String firstName = resultSet.getString("firstName");
		String lastName = resultSet.getString("lastName");
		
		emp.setId(id);
		emp.setFirstName(firstName);
		emp.setLastName(lastName);
		
		return emp;
	}

}

5) Data Access Object (DAO) pattern

For DAO pattern, first we need to create one interface for CRUD application as below.

package com.code2succeed.dao;

import java.util.List;

import com.code2succeed.beans.Emp;

public interface EmpDao {
	//Create
	public void save(Emp emp);
	//Read
	public Emp getById(int id);
	//Update
	public void update(Emp employee);
	//Delete
	public void deleteById(int id);
	//Get All
	public List<Emp> getAll();
}

Now implement above interface and create EmpDaoJDBCTemplateImpl class which will contain JDBC code for CRUD operation.

package com.code2succeed.dao;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.code2succeed.beans.Emp;
import com.code2succeed.mapper.EmpRowMapper;

@Repository("empDao")
public class EmpDaoJDBCTemplateImpl implements EmpDao {
	
	@Autowired
	DataSource dataSource;
	
	public void save(Emp emp) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "insert into Emp(id, firstname, lastname) values (?,?,?)";
		Object [] objs = new Object[]{emp.getId(), emp.getFirstName(), emp.getLastName()};
		int output = jdbcTemplate.update(sql, objs);
		if(output != 0) {
			System.out.println("Employee saved with id "+emp.getId());
		}else {
			System.out.println("Employee save failed with id "+emp.getId());
		}
	}

	public Emp getById(int id) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "select * from emp where id = ?";
		Emp emp = jdbcTemplate.queryForObject(sql, new Object[]{id}, new EmpRowMapper());
		
		return emp;
	}

	public void update(Emp emp) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "UPDATE Emp SET firstname=?, lastname=? where id=?";
		Object [] objs = new Object[]{emp.getFirstName(), emp.getLastName(), emp.getId()};
		int output = jdbcTemplate.update(sql, objs);
		if(output != 0) {
			System.out.println("Employee updated with id "+emp.getId());
		}else {
			System.out.println("Employee update failed with id "+emp.getId());
		}
	}

	public void deleteById(int id) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "DELETE FROM Emp WHERE id=?";
		int output = jdbcTemplate.update(sql, id);
		if(output != 0) {
			System.out.println("Employee deleted with id "+id);
		}else {
			System.out.println("Employee deletion failed with id "+id);
		}
	}

	public List<Emp> getAll() {
		List<Emp> emps = new ArrayList<Emp>();
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "select * from emp";
		emps = jdbcTemplate.query(sql, new EmpRowMapper());
		return emps;
	}

}

6) Spring configuration file.

<?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"
    xmlns:tx="http://www.springframework.org/schema/tx"
    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
    		http://www.springframework.org/schema/tx 
    		http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
	
	<!-- scans packages to find and register beans within the application context -->
    <context:component-scan base-package="com.code2succeed" />
	
	<!-- 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/code2succeed" />
        <property name="username" value="root" />
        <property name="password" value="manager" />
    </bean>    
</beans>

7) Load and Run Test class

We have created App.java class which contains simple logic to test the CRUD application.

package com.code2succeed.main;

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

import com.code2succeed.beans.Emp;
import com.code2succeed.dao.EmpDao;

public class App {
    public static void main( String[] args ) {
    	ApplicationContext context = new GenericXmlApplicationContext("applicationContext.xml"); 
        EmpDao empDao = (EmpDao) context.getBean("empDao"); 
        
        //get all Employees
        System.out.println("All Employees");
        System.out.println(empDao.getAll());
        
        //get Employee with id = 1
        System.out.println("Employee with id = 1");
        System.out.println(empDao.getById(1));
        
        //Save new Employee
        Emp emp = new Emp();
        emp.setId(3);
        emp.setFirstName("Sandeep");
        emp.setLastName("Kulkarni");
        empDao.save(emp);
        //now get all Employees
        System.out.println(empDao.getAll());
        
        //Update Employee
        emp.setLastName("Kulkarni - new");
        empDao.update(emp);
        //now get all Employees
        System.out.println(empDao.getAll());
        
        //Delete Employee
        empDao.deleteById(3);
        System.out.println(empDao.getAll());
    }
}

8) Output

All Employees
[Id : 1, FirstName : Rahul, LastName : Anand, Id : 2, FirstName : Daniel, LastName : Anderson]
Employee with id = 1
Id : 1, FirstName : Rahul, LastName : Anand
Employee saved with id 3
[Id : 1, FirstName : Rahul, LastName : Anand, Id : 2, FirstName : Daniel, LastName : Anderson, Id : 3, FirstName : Sandeep, LastName : Kulkarni]
Employee updated with id 3
[Id : 1, FirstName : Rahul, LastName : Anand, Id : 2, FirstName : Daniel, LastName : Anderson, Id : 3, FirstName : Sandeep, LastName : Kulkarni - new]
Employee deleted with id 3
[Id : 1, FirstName : Rahul, LastName : Anand, Id : 2, FirstName : Daniel, LastName : Anderson]

Stay tuned for more tutorials and examples !

Leave a Reply

Your email address will not be published. Required fields are marked *