In a real-world application scenario, a huge amount of processing is done at the backend server where the data is actually processed and persisted into a repository. Apart from many prominent features of Spring, such as DI (Dependency Injection), Aspects, and POJO-oriented development, Spring has excellent support for data handling. There are different ways to write good database applications. Still today, a large number of applications are written based upon JDBC data access capability. This article specifically deals with the JDBC in connection to Spring, its support, and pros and cons with appropriate examples and code snippets.
JDBC Overview
One of the biggest advantages of still using JDBC in the world of ORM is that it doesn’t require mastering another framework’s query language apart from working with data at a much lower level. It enables a programmer to take advantage of the database’s proprietary features. It has its disadvantages as well. Unfortunately, the disadvantages are often so visible that they do not need a mention. For example, one of them is boilerplate code. The term boilerplate code basically means writing the same code again and again without incorporating any value in the code. This typically can be seen when we query data from a database; for example, in the following code, we simply fetch a User record from the database.
public User getUserById(long id) {
User user = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = dataSource.getConnection();
pstmt = con.prepareStatement("select * from "
+ "user_table where userid=?");
pstmt.setInt(1, id);
rs.pstmt.executeQuery();
if (rs.next()) {
user = new User();
user.setId(rs.getInt("userid"));
user.setFullName(rs.getString("fullname"));
user.setUserType(rs.getString("usertype"));
user.setPassword(rs.getString("password"));
}
} catch (SQLException ex1) {}
finally {
try {
if (rs != null)
rs.close();
if (pstmt != null)
rs.close();
if (con != null)
rs.close();
} catch (SQLException ex2) {}
}
return user;
}
Observe that, every time we need to interact with the database, we must create three objects—a connection (Connection), statement (PreparedStatement), and resultset (ResultSet). All these also have to be enclosed within the imposed try…catch block. Even the closure of the connection also has to be enclosed within try…catch. This is ludicrous because the actual required code for the function is much less. The code is simply puffed up with unnecessary but compulsory code and has to be repeated wherever we interact with the database. A clever coding scheme may reduce this mess, yet it is impossible to eradicate the problem of boilerplate JDBC code. This is not only the problem with JDBC but also JMS, JNDI, and REST.
Spring’s Solution
The Spring framework provided a solution to this mess and provided a means to eliminate boilerplate code by using template classes. These classes encapsulate the boilerplate code, thus relieving the programmer. This means that the boilerplate code is still there, only the programmer who uses one of the template classes is relieved of the trouble of writing it. The JdbcTemplate provided by Spring is the central class of the JDBC core package.
It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package.
We need to implement only the call-back interfaces and give them a clear, defined contract. For example, the PreparedStatementCreator callback interface is used for creating a prepared statement. The ResultsetExtractor interface acts like a ResultSet.
Therefore, the preceding code snippet may be rewritten with JdbcTemplate as follows:
@Autowired
private JdbcTemplate jdbcTemplate;
public User getUserById(long id) {
return jdbcTemplate.queryForObject(
"select * from user_table where userid=?",
new UserRowMapper(),id);
}
class UserRowMapper implements RowMapper<User>{
@Override
public User mapRow(ResultSet rs, int runNumber)
throws SQLException {
User user=new User();
user.setId(rs.getInt("userid"));
user.setFullName(rs.getString("fullname"));
user.setUserType(rs.getString("usertype"));
user.setPassword(rs.getString("password"));
return user;
}
}
The RowMapper is an interface typically used by the JdbcTemplate to map one row per basis of rows of the ResultSet. The RowMapper objects are stateless and therefore reusable. They are perfect for implementing any row mapping logic. Observe that, in the previous code, we did not handle exceptions explicitly as we have done in the code which does not use JdbcTemplate. The RowMapper implementation performs the actual implementation of mapping each row to the result object without the programmer’s need to worry about exception handling. It will be called and handled by calling JdbcTemplate.
The Exceptions
The exceptions provided by JDBC are often too imposing than necessary, with little value. Spring’s data access exception hierarchy are more streamlined and reasonable in this respect. This means that it has a consistent set of exception classes in its arsenal in contrast to JDBC’s one size fit to all exception called SQLException for all problems related to data access. Spring’s data access exceptions are rooted with the DataAccessException class. Therefore, we can have both the choice of checked vs. unchecked exception ingrained into the framework. This sounds more practical because there are really no solutions to many of the problems that occured during runtime data access and it is pointless in we catch them when we cannot address the situation with a suitable alternative.
Spring’s Way of Simplifying Data Access
What Spring actually does is that it distinguishes the fixed and variable part of the data access mechanism into two sets of classes called template classes and callback classes, respectively. The fixed part of the code represents the perfunctory part of data access and the variable part is that data access method that varies according to the changing requirement.
In short, the template classes handle:
- Transaction control
- Resource management
- Exception handling
And, the callback classes handle:
- Creating query statement
- Parameter binding
- Result set marshalling
We can choose one among many template classes, according to the choice of persistent technology used. For example, for JDBC we may choose JdbcTemplate, or for ORM we may choose JpaTemplate, HibernateTemplate, and so forth.
Now, while connecting to the database, we have three options to configure the data source, such as:
- Defined by the JDBC driver
- Looked up by JNDI
- Fetched from connection pool
A production-ready application typically uses a connection pool or JNDI. The JDBC driver defined data sources are by far the simplest, although it is used mostly for testing purposes. Spring offers three classes in the package org.springframework.jdbc.datasource of this category; they are:
- DriverManagerDataSource: Simple implementation of the standard JDBC DataSource interface, configuring the plain old JDBC DriverManager via bean properties, and returning a new Connection from every request.
- SingleConnectionDataSource: Returns the same connection on every request. This type of connection is primarily intended for testing.
- SimpleDriverDataSource: Same as DriverManagerDataSource except that it has special class loading issues such as OSGi; this class directly works with JDBC Driver.
Configuring these data sources is similar. We can configure them in a bean class or via XML.
// Configuring MySQL data source
@Bean
public DataSource dataSource() {
DriverManagerDataSource ds=new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/testdb");
ds.setUsername("root");
ds.setPassword("secret");
return ds;
}
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
p_driverClassName="com.mysql.jdbc.Driver"
p_url="jdbc:mysql://localhost:3306/testdb"
p_username="root"
p_password="secret"/>
JDBC Template Classes
Spring offers a couple of template classes to simplify data access with JDBC:
- JdbcTemplate: This is the basic class of the core JDBC package org.springframework.jdbc.core that provides the simplest access to the database through indexed queries.
- NamedParameterJdbcTemplate: This template class also provides a basic set of JDBC operations where the values are bound with named parameters rather than traditional ‘?’ placeholders in SQL queries.
JDBC Callback Classes
The key JDBC callback functional interfaces defined in org.springframework.jdbc.core are:
- CallableStatementCallback<T>: Operates on the JDBC CallableStatement. This callback is used internally by JdbcTemplate and allows execution on a single CallableStatement such as single or multiple SQL execute calls with different parameters.
- PreparedStatementCallback<T>: Operates on JDBC PreparedStatement. This callback is internally used by JdbcTemplate and allows execution of more than one operation on a single PreparedStatement such as single or multiple SQL executeUpdate call with different parameters.
- StatementCallback<T>: Operates on the JDBC Statement. This callback also is used internally by JdbcTemplate to execute more than one operation on a single Statement such as single or multiple SQL executeUpdate calls.
A Simple Spring Boot JDBC Example
Let’s try a simple Spring boot example. A Spring boot project automatically handles many of the complexities of configuration where a developer is relieved of all the trouble once a correct dependency is included into the Maven file pom.xml. To keep the length of the article short, we’ll not include code explanations. Please use the references given at the end of the article for a more detailed description.
To work on the following example, create a database and a table in MySQl as follows:
Log into MySQL database and create a database and a table with the following command:
CREATE DATABASE testdb; USE testdb; CREATE TABLE candidate( id INT UNSIGNED NOT NULL AUTO_INCREMENT, fullname VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, phone VARCHAR(10) NOT NULL, PRIMARY KEY(id) );
Start as a Spring starter project from Spring Tool Suite (STS) with the dependency JDBC and MySQL. The Maven configuration file, pom.xml, of the project is as follows:
<?xml version="1.0" encoding="UTF-8"?>
<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>org.mano.springbootjdbc.demo</groupId>
<artifactId>spring-boot-jdbc-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>spring-boot-jdbc-demo</name>
<description>Demo project for Spring Boot
jdbc</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.10.RELEASE</version>
<relativePath/> <!-- Look up parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8
</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8
</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-
plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Model Class: Candidate.java
package org.mano.springbootjdbc.demo.model;
public class Candidate {
private int id;
private String fullname;
private String email;
private String phone;
public Candidate() {
super();
}
public Candidate(int id, String fullname,
String email, String phone) {
super();
setId(id);
setFullname(fullname);
setEmail(email);
setPhone(phone);
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFullname() {
return fullname;
}
public void setFullname(String fullname) {
this.fullname = fullname;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Candidate [id=" + id + ", fullname=" + fullname
+ ", email=" + email + ", phone=" + phone + "]";
}
}
Data Access Object Interface: CandidateDao.java
package org.mano.springbootjdbc.demo.dao;
import java.util.List;
import org.mano.springbootjdbc.demo.model.Candidate;
public interface CandidateDao {
public void addCandidate(Candidate candidate);
public void modifyCandidate(Candidate candidate,
int candidateId);
public void deleteCandidate(int candidateId);
public Candidate find(int candidateId);
public List<Candidate> findAll();
}
Data Access Object Implementation Class: CandidateDaoImpl.java
package org.mano.springbootjdbc.demo.dao;
import java.util.ArrayList;
import java.util.List;
import org.mano.springbootjdbc.demo.model.Candidate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
@Qualifier("candidateDao")
public class CandidateDaoImpl implements CandidateDao {
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public void addCandidate(Candidate candidate) {
jdbcTemplate.update("insert into candidate
(id,fullname,email,phone) "
+ "values (?,?,?,?)", candidate.getId(),
candidate.getFullname(), candidate.getEmail(),
candidate.getPhone());
System.out.println(candidate+" is added successfully!");
}
@Override
public void modifyCandidate(Candidate candidate, int
candidateId) {
jdbcTemplate.update("update candidate fullname=?,
email=?,phone=? "
+ "where id=? values (?,?,?,?)",candidate.getFullname(),
candidate.getEmail(), candidateId);
System.out.println("Candidate with id="+candidateId+
" modified successfully!");
}
@Override
public void deleteCandidate(int candidateId) {
jdbcTemplate.update("delete from candidate where id=?",
candidateId);
System.out.println("Candidate with id="+candidateId+
" deleted successfully!");
}
@Override
public Candidate find(int candidateId) {
Candidate c = null;
c = (Candidate) jdbcTemplate.queryForObject("select *
from candidate "
+ "where id=?", new Object[] { candidateId },
new BeanPropertyRowMapper<Candidate>(Candidate.
class));
return c;
}
@Override
public List<Candidate> findAll() {
List<Candidate> candidates = new ArrayList<>();
candidates = jdbcTemplate.query("select * from candidate",
new BeanPropertyRowMapper<Candidate>
(Candidate.class));
return candidates;
}
}
Spring Boot Loader Class: SpringBootJdbcDemoApplication.java
package org.mano.springbootjdbc.demo;
import java.util.List;
import org.mano.springbootjdbc.demo.dao.CandidateDao;
import org.mano.springbootjdbc.demo.model.Candidate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.
SpringBootApplication;
@SpringBootApplication
public class SpringBootJdbcDemoApplication implements
CommandLineRunner {
@Autowired
private CandidateDao cdao;
public static void main(String[] args) {
SpringApplication.run(SpringBootJdbcDemoApplication.
class, args);
}
@Override
public void run(String... arg0) throws Exception {
Candidate c1 = new Candidate(1, "Sachin Tendulkar",
"sachin@outlook.com", "1234567890");
Candidate c2 = new Candidate(2, "Amit Saha",
"amit2017@mail.com", "9632587410");
Candidate c3 = new Candidate(3, "Sandip Paul",
"spaul@gmail.com", "8527419630");
Candidate c4 = new Candidate(4, "Rajib Kakkar",
"rkakkar@yahoo.com", "9876543210");
Candidate c5 = new Candidate(5, "Rini Simon",
"rsimaon@gmail.com", "8624793150");
cdao.addCandidate(c1);
cdao.addCandidate(c2);
cdao.addCandidate(c3);
cdao.addCandidate(c4);
cdao.addCandidate(c5);
List<Candidate> candidates = cdao.findAll();
for (Candidate candidate : candidates) {
System.out.println(candidate);
}
cdao.deleteCandidate(3);
candidates = cdao.findAll();
for (Candidate cc : candidates) {
System.out.println(cc);
}
}
}
Application.properties
spring.driverClassName=com.mysql.jdbc.Driver spring.url=jdbc:mysql://localhost:3306/testdb spring.username=root spring.password=secret
Run the Application
To run the application, right-click the project in the Project Explorer pane and select Run As -> Spring Boot App. That’s all.
Conclusion
We have three options to work with Relational Database programming with Spring:
- The old-fashioned JDBC with Spring. This means using the Spring framework for all practical purposes in the program except Spring’s data support.
- Using JDBC template classes. Spring offers JDBC abstraction classes to query relational databases; these are far simpler than working with native JDBC code.
- Spring also has excellent support for the ORM (Object Relational Mapping) framework and can integrate well with a prominent implementation of JPA (Java Persistent Annotation) API such as Hibernate. It also has its own Spring Data JPA assistance that can automatically generate repository implementation on the fly at runtime.
If one opts for JDBC for some reason, it is better to use Spring template support such as JdbcTemplate other than using ORM.
References
- Walls, Crag. Spring in Action 4, Manning Publications
- Spring 5 API Documetation
