Spring framework provides a JdbcTemplate class, which makes coding related to database related operations simpler. This class handles common tasks like obtaining database connection, preparing statements, executing the statements, processing the result set and closing the resources, etc.

This JdbcTemplateclass is part of org.springframework.jdbc.core package.
This class is thread safe and one instance can be used in different DAO implementation classes by injecting it to that class.

Few advantages of using JdbcTemplate are given below:

  • Using JdbcTemplate, we can eliminate boilerplate code in our application, like opening the database connection, preparing statements, closing the connections once the operation is completed, etc.
  • We can use JdbcTemplate for all DML(create, update, insert and delete, etc) and DDL(table creation, drop, alteration, etc.) operations.
  • It offers wide range of built in query methods to fetch records from database. (Example : queryForMap(), queryForList(), etc.)
  • It handles exceptions occurred during database transaction and converts them into more informative ones. These are part of org.springframework.dao package.
  • It provides convenient ways of iterating results 
    fetched from database with the help of RowMapper and ResultSetExtractor.  

We can initialize JdbcTemplate instance by following ways:

  • Create an object of JdbcTemplate and use setDataSource() method to set the datasource.
  • Create an object of JdbcTemplate by passing datasource object as constructor argument.
  • Create an object of JdbcTemplate by passing datasource object and a Boolean value as constructor parameters. Boolean value parameter set to true makes the JdbcTemplate SQL exception translator(used to handle the exceptions) initialization at the time of statement execution(lazy).

Following is the example CRUD operation using spring JdbcTemplate. We will use JdbcTemplate with XML configuration to inject the bean to DAO layer and connect to MySQL database.

Steps involved in creating spring JdbcTemplate CRUD example application are:

  • Create datasource and JdbcTemplate beans.
  • Create a POJO class.
  • Create a DAO layer interface and implementations class.
  • Invoke the CRUD methods of DAO layer.

Create datasource and JdbcTemplate beans

First step is to create a spring application and add the following content in beans.xml spring configuration file. Here, we are creating a dtatsource bean property and use it as constructor argument for JdbcTemplate bean as shown below. Datasource object is initialized with MySQL driver class and connection string (as there is no database credentials set, I am not using username/password properties).

< ?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.3.xsd">

<context:component-scan base-package="com.asb.dao"/>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="${jdbc.driverClassName}" /> <property name="url" value="${jdbc.url}" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>

<context:property-placeholder location="jdbc.properties" />
< /beans>

In the above code snippet, we are using <context:property-placeholder> element, to load property file, which contains jdbc configuration details like: driver class and connection string. Create a jdbc.properties file under src/ folder and add the following content (modify the property value accordingly to your database).

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test

Create a POJO class

Create a Employee.java file under package com.asb.dto and add the following content. Here, this class is used to define an employee object having id and name fields.

package com.asb.dto;
public class Employee {

private int id;
private String name;

public Employee(int id, String name){
this.id = id;
this.name = name;
}
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 toString(){
StringBuffer sb = new StringBuffer();
sb.append("Employee ID : "+ this.id);
sb.append(" Employee Name : "+ this.name);
return sb.toString();
}
}

Create a DAO layer Interface and Implementations class

Next step is to create our applications DAO layer. This layer will contain and interface, having CRUD operation related methods declaration and an implementation class for the interface.  Create a package com.asb.dao under src/ folder and add an interface MySqlDao as shown below. This interface contains methods to fetch Employee object, update, insert and delete.

package com.asb.dao;
import java.util.List;
import com.asb.dto.Employee;
public interface MySqlDao {
List getAllEmployees();
int updateEmployee(Employee emp);
int deleteEployee(int id);
int inserEmployeeDetails(Employee employee);
}

Add an implementation class for the above interface as shown below. Here @Repository annotation makes the class to be registered as spring bean which performs database related activities. A JdbcTemplate object is defined with @Autowired annotation , which is initialized by spring framework using configuration details provided in the beans.xml file.

package com.asb.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.asb.dto.Employee;
@Repository
public class MySQLDaoImpl implements MySqlDao{
@Autowired
private JdbcTemplate jdbcTemplate;

private String sqlSelectAll = "select * from emp_dtls";
private String sqlUpdate = "update emp_dtls set emp_first_name = ? where emp_id = ?";
private String sqlInsert = "insert into emp_dtls (emp_id, emp_first_name) values (?, ?)";
private String sqlDelete = "delete from emp_dtls where emp_id = ?";

@Overridepublic List<Employee> getAllEmployees() {
return (List<Employee>) this.jdbcTemplate.query(sqlSelectAll, new RowMapper<Employee>(){
@Override
public Employee mapRow(ResultSet arg0, int arg1) throws SQLException { Employee employee = new Employee(arg0.getInt("emp_id"),arg0.getString("emp_first_name"));
return employee;
}
});
}

@Overridepublic int updateEmployee(Employee emp) {
return this.jdbcTemplate.update(sqlUpdate, emp.getName(), emp.getId());}

@Overridepublic int deleteEployee(int id) {
return this.jdbcTemplate.update(sqlDelete, new Object[] {id});}

@Overridepublic int inserEmployeeDetails(Employee employee) {
Object[] params = new Object[] {employee.getId(), employee.getName()};
int[] types = new int[] { Types.INTEGER, Types.VARCHAR};
return this.jdbcTemplate.update(sqlInsert, params, types);
}
}

Create a MainClass.java file under com.asb.main package. This class is used to load our spring bean configuration file and perform CRUD operations on connected MySQL database.

package com.asb.main;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.asb.dao.MySQLDaoImpl;
import com.asb.dao.MySqlDao;
import com.asb.dto.Employee;
public class MainClass {
@SuppressWarnings("resource")
public static void main(String args[]) {
ApplicationContext appContext = new ClassPathXmlApplicationContext("beans.xml");

MySqlDao dao = appContext.getBean("mySQLDaoImpl", MySQLDaoImpl.class);

System.out.println("Inserting employee!!");
Employee newEmp1 = new Employee(1, "John");
Employee newEmp2 = new Employee(2, "ASB"); dao.inserEmployeeDetails(newEmp1);
dao.inserEmployeeDetails(newEmp2);
printEmployees(dao.getAllEmployees());

System.out.println("Updating employee!!");
newEmp1.setName("Mark");
dao.updateEmployee(newEmp1);
printEmployees(dao.getAllEmployees());

System.out.println("Deleting employee Code 1");
dao.deleteEployee(1);
printEmployees(dao.getAllEmployees());
}

public static void printEmployees(List<Employee> empList){
for(Employee emp : empList) {
System.out.println(emp.toString());
}
}
}

Following is the complete project structure.

Output: The image shown in right side of the page is the console output of our application. Here, we are creating 2 employee objects with name John and ASB with Employee ID 1 and 2 respectively. We are updating the name of employee Id 1 to Mark. Finally, we are deleting the employee with employee id 1.

You may be interested in :