What is Spring JDBC?

Spring allows handling database access with the help of the Spring JDBC Template. Spring JDBC allows cleaning up the resources automatically. Also, it converts the JDBC SQL Exceptions into Run-time Exceptions. It provides flexibility to the programmers to react to the errors. It also converts the vendor-specific error messages into an easy and understandable message. There are several ways to query the database:

• Using queryForList() – it returns a list of hashmaps.
• Using ResultSetExtractor or RowMapper – it translates the SQL result directly into an object or a list of objects, respectively.

It uses JDBC API internally but eliminates various problems of JDBC API. Some of the major problems of the JDBC API are:

• It requires a lot of code writing before and after executing the query.
• You need to handle the transaction.
• The repetition of code becomes time-consuming.
• You need to perform exception handling code on the database logic.

Spring JDBC resolves all the problems mentioned above. It contains a lot of methods while writing a query, which saves a lot of time.

JDBC Template Class is a central class in Spring JDBC. It already takes care of the creation and release of resources, so there is no need to worry if you forgot to close the database connection.

Methods of JDBC Template Class:

1) public int update(String query) – It is used to insert, update, and delete the records.

2) public int update(String sqlquery, Object… args) – It is used to insert, update, and delete records using PreparedStatement using given arguments.

3) public void execute(String query) – It is used to execute the DDL query.

4) public T execute(String sqlquery, PreparedStatementCallback action) – It executes the query by using PreparedStatement callback.

5) public T query(String sqlquery, ResultSetExtractor rse) – It is used to fetch records using ResultSetExtractor.

6) public List query(String sqlquery, RowMapper rse) – It is used to fetch records using RowMapper.

Given below is an example to demonstrate how to use JDBC Template Class in Spring.

Example:Step 1: Include Spring JDBC and Database drivers in the maven project. Let’s name it as pom.xml

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

Step 2: Create a table in the database.

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;

Step 3: Now we will use DAO pattern so firstly we will create a Model class.

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+"}";
}
}

Step 4: Create an interface where all the operations are declared.

import java.util.List; 
//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();
}
-----------------------------------------------------
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; 
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());

Step 5: Create a bean configuration file.

<?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.spring.jdbc.dao.EmployeeDAOImpl">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="data Source" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:8080/TestDB" />
<property name="username" value="ABC" />
<property name="password" value="ABC123" />
</bean>
</beans>

Step 6: Create a Test Class.

import java.util.List;
import java.util.Random; 
import org.springframework.context.support.ClassPathXmlApplicationContext;
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("ABC");
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<Employee> empList = employeeDAO.getAll();
System.out.println(empList);
//Delete
employeeDAO.deleteById(rand);
//Close Spring Context
ctx.close();
System.out.println("DONE");
}
}

OUTPUT:

Employee saved with id=726
Employee Found::{ID=726,Name=ABC,Role=Java Developer}
Employee Retrieved::{ID=726,Name=ABC,Role=Java Developer}
Employee updated with id=726
[{ID=726,Name=ABC,Role=CEO}]
Employee deleted with id=726