Spring JDBCTemplate CRUD example

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

This JdbcTemplate class is part of the org.springframework.jdbc.core package.
Also, the JdbcTemplate class is a thread safe class, and we can use one instance in different DAO implementation classes by injecting it into that class.

Advantages of using JdbcTemplate

  • Using JdbcTemplate, we can eliminate boilerplate code in our application, like opening the database connection, preparing statements, closing the connections after completion of the operation, 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 also provides convenient ways of iterating results fetched from database with the help of RowMapper and ResultSetExtractor.  

We can initialize the JdbcTemplate instance by following ways.

  • Creating an object of JdbcTemplate and using the 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. Also, 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 into the DAO layer and connect it to the MySQL database.

Create datasource and JdbcTemplate beans

The first step is to create a spring application and add the following content in the beans.xml spring configuration file. Here, we are creating a data source bean property and use it as a constructor argument for JdbcTemplate bean as shown below. The spring framework initializes the data source object with the MySQL driver class and the connection string (as there are 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 the property file, which contains JDBC configuration details like driver class and connection string.

Now, 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

Creating a POJO class

Create an Employee.java file under package the com.asb.dto, and add the following content. Here, this class defines 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

The next step is to create our application’s DAO layer. This layer will contain an interface, having CRUD operation methods declaration and an implementation class for the interface. 

Create a package called com.asb.dao, under src/ folder, and add an interface MySqlDao as shown below.

Note that, the interface contains CRUD operation methods to fetch Employee objects, 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. 

Also, 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 loads our spring bean configuration file and performs CRUD operations on a 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 on the right side of the page is the console output of our application.

We are creating 2 employee objects with names John and ASB with Employee ID 1, and 2 respectively.

Also, we are updating the name of employee Id 1 to Mark. 

Finally, we are deleting the employee with employee id 1.

Conclusion

In this article, we learned how to create an example CRUD application using the spring and JDBC template.