Spring JDBC template
Overview: As we all know that the spring framework has become an essential part of the modern Java based application development. The spring framework has effectively managed to take control of every department in the Java development world. The JDBC template of spring is used in most of the spring based JEE applications to communicate with the database.
In this article I will talk upon how to access a relational database using the JDBC template of spring.
Introduction: We all know the importance of relational database in enterprise application development. Spring is one of the widely used frameworks in Java based enterprise application development. Spring has different modules like ORM, security, transaction, JPS, JMS etc to support the need of enterprise application development. As spring provides readily available components to plug-in, the modern approach for enterprise Java application development uses the features extensively. As a result the development is much faster and efficient. Spring provides a simple approach to handle database activities know as spring JDBC template.
Disadvantages of standard JDBC API: Standard JDBC API has the following disadvantages.
- Apart from executing the main query, you need to write a lot of code to handle the execution environment issues like creating connection, statement, resultset etc.
- Need to handle exception handling code separately.
- Need to handle transactional issues.
Advantages of spring JDBC template: Spring JDBC template has the following advantages compared to the normal approach of standard JDBC.
- Cleaning of used resources is done automatically by spring JDBC template. So the developers do not need to bother about releasing the resources. Hence it prevents memory leaks.
- Spring JDBC template handles the exception and errors in a more efficient way. It converts the JDBC SQLExceptions into RuntimeExceptions, so the developers can handle it in more flexible way.
- The spring JDBC template also converts the vendor specific errors in a better meaningful message. So the handlings of those errors are more efficient.
Getting Started:
In this document, let’s use gradle as a build tool to build our application. Let us start with a simple application used to store and retrieve the details of employees along with their departments of an organization. In our example, we will have the following attributes of the employee object:-
- Employee Id
- Employee First Name
- Employee Middle Name
- Employee Last Name
- Employee Department Id
And the department object has the following attributes:-
- Department Id
- Department Name
Now let us create the java objects which will be used to communicate with the database. Our POJO classes for these two entities are listed as under:
Listing1: Sample showing the Employee Class
[Code]
package com.home.springjdbc.objects;
public class Employee {
// The employee Id
private String empId;
// The employee first Name
private String empFName;
// The employee middle name
private String empMName;
// The employee last Name
private String empLName;
// The employee department Id
private String empDeptId;
public Employee ( String empId, String empFName, String empMName,
String empLName, String empDeptId ) {
super();
this.empId = empId;
this.empFName = empFName;
this.empMName = empMName;
this.empLName = empLName;
this.empDeptId = empDeptId;
}
/**
* @return the empId
*/
public String getEmpId () {
return empId;
}
/**
* @param empId
* the empId to set
*/
public void setEmpId ( String empId ) {
this.empId = empId;
}
/**
* @return the empFName
*/
public String getEmpFName () {
return empFName;
}
/**
* @param empFName
* the empFName to set
*/
public void setEmpFName ( String empFName ) {
this.empFName = empFName;
}
/**
* @return the empMName
*/
public String getEmpMName () {
return empMName;
}
/**
* @param empMName
* the empMName to set
*/
public void setEmpMName ( String empMName) {
this.empMName = empMName;
}
/**
* @return the empLName
*/
public String getEmpLName () {
return empLName;
}
/**
* @param empLName
* the empLName to set
*/
public void setEmpLName ( String empLName) {
this.empLName = empLName;
}
/**
* @return the empDeptId
*/
public String getEmpDeptId () {
return empDeptId;
}
/**
* @param empDeptId
* the empDeptId to set
*/
public void setEmpDeptId ( String empDeptId ) {
this.empDeptId = empDeptId;
}
/*
* ( non-Javadoc )
*
* @see java.lang.Object#toString ()
*/
@Override
public String toString () {
return “Employee [ empId=” + empId + “, empFName=” + empFName
+ “, empMName=” + empMName + “, empLName=” + empLName
+ “, empDeptId=” + empDeptId + “]”;
}
}
[/Code]
Listing2: Sample showing the Department Class
[Code]
package com.home.springjdbc.objects;
public class Department {
// The department Id
private String deptId;
// The department Name
private String deptName;
public Department ( String deptId, String deptName) {
super ();
this.deptId = deptId;
this.deptName = deptName;
}
/**
* @return the deptId
*/
public String getDeptId () {
return deptId;
}
/**
* @param deptId
* the deptId to set
*/
public void setDeptId ( String deptId ) {
this.deptId = deptId;
}
/**
* @return the deptName
*/
public String getDeptName () {
return deptName;
}
/**
* @param deptName
* the deptName to set
*/
public void setDeptName ( String deptName ) {
this.deptName = deptName;
}
/*
* (non-Javadoc)
*
* @see java.lang.Object#toString()
*/
@Override
public String toString () {
return “Department [ deptId=” + deptId + “, deptName=” + deptName + “]”;
}
}
[/Code]
Spring provides a template class known as JdbcTemplate. This class is responsible for communicating with SQL relational databases and JDBC. In the traditional approach the maximum of a JDBC code is occupied in handling connection management, resource acquisition, exception handling, and general error checking which is irreverent in the context of what we are trying to achieve. The JdbcTemplate takes care of all of these things and as a developer we should focus only on the business logic. A sample implementation of the template class is shown as under –
Listing3: Sample showing the implementation of JDBC template
[Code]
package com.home.springjdbc.dao;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
import com.home.springjdbc.objects.Employee;
public class EmployeeDAO {
public static void main ( String args[] ) {
// simple DS for test (not for production!)
SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
dataSource.setDriverClass((Class<? extends Driver>) org.h2.Driver.class);
dataSource.setUsername( “sa” );
dataSource.setUrl( “jdbc:h2:mem” );
dataSource.setPassword( “” );
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
System.out.println( “Creating tables” );
jdbcTemplate.execute( “drop table employees if exists” );
jdbcTemplate.execute( “create table employees(”
+ “id varchar(25), first_name varchar(255), middle_name varchar(255), last_name varchar(255), deptId varchar(25))”);
String[] records = “E001 Dean Andrew Roberts D25; E002 Jeff Longman Dean D42; E003 Erin Nancy Kirkland D66;”.split(“;”);
for ( String singleRecord : records ) {
String [] cols = singleRecord.split(” “);
System.out.printf( ” Adding employee record for %s %s %s %s %s\n”,cols[0], cols[1], cols[2], cols[3], cols[4]);
jdbcTemplate.update(“INSERT INTO employees(id, first_name, middle_name, last_name, deptId) values(?,?,?,?,?)”,cols[0], cols[1], cols[2], cols[3], cols[4]);
}
System.out.println ( ” Querying for customer records where first_name = ‘Erin’:” );
List<Employee> results = jdbcTemplate.query(
“select * from employees where first_name = ?”,
new Object[] { “Erin” }, new RowMapper<Employee>() {
@Override
public Employee mapRow(ResultSet rs, int rowNum)
throws SQLException {
return new Employee(rs.getString(“id”),
rs.getString(“first_name”), rs.getString(“middle_name”),
rs.getString(“last_name”), rs.getString(“deptId”));
}
});
for (Employee employee : results) {
System.out.println(employee);
}
}
}
[/Code]
In the above example, we set up a JDBC data source using the SimpleDriverDataSource from spring. We then use this data source to construct the JDBCTemplate instance. Once the JDBCTemplate class is created, we can easily start calling the database. First, we create the table using the execute method of the template. Then we insert few records using the update method of the template. Finally we use the query method to fetch the records of our choice. In our example we have used a simple query to fetch the record from the employee table. But as per our design we see that the employee table has a relation with the department table via the column – deptId. So we can also use the same code to fetch records joining these two tables. Just we need to change the query to put a join. The above code could be modified as under –
Listing4: Sample showing the modified query
[Code]
List<Employee> results = jdbcTemplate.query(
“select emp.first_name, emp.middle _name, emp.last _name, dept.dept_Name from employees emp, department dept where emp.first_name = ?”,
new Object[] { “Erin” }, new RowMapper<Employee>() {
@Override
public EmployeeDtls mapRow(ResultSet rs, int rowNum)
throws SQLException {
return new Employee( rs.getString(“first_name”),
rs.getString(“middle_name”),
rs.getString(“last_name”),
rs.getString(“dept_Name “));
}
});
for (EmployeeDtls employeeDtl : results) {
System.out.println(employeeDtl);
}
[/Code]
To execute this we should have another POJO class EmployeeDtls as under –
Listing5: Sample showing the POJO class EmployeeDtls
[Code]
package com.home.springjdbc.objects;
public class EmployeeDtls {
// The employee first Name
private String empFName;
// The employee middle name
private String empMName;
// The employee last Name
private String empLName;
// The department Name
private String deptName;
/**
* @param empFName
* @param empMName
* @param empLName
* @param deptName
*/
public EmployeeDtls(String empFName, String empMName, String empLName,
String deptName) {
super();
this.empFName = empFName;
this.empMName = empMName;
this.empLName = empLName;
this.deptName = deptName;
}
/*
* (non-Javadoc)
*
* @see java.lang.Object#toString()
*/
@Override
public String toString() {
return “EmployeeDtls [empFName=” + empFName + “, empMName=” + empMName
+ “, empLName=” + empLName + “, deptName=” + deptName + “]”;
}
/**
* @return the empFName
*/
public String getEmpFName() {
return empFName;
}
/**
* @param empFName
* the empFName to set
*/
public void setEmpFName(String empFName) {
this.empFName = empFName;
}
/**
* @return the empMName
*/
public String getEmpMName() {
return empMName;
}
/**
* @param empMName
* the empMName to set
*/
public void setEmpMName(String empMName) {
this.empMName = empMName;
}
/**
* @return the empLName
*/
public String getEmpLName() {
return empLName;
}
/**
* @param empLName
* the empLName to set
*/
public void setEmpLName(String empLName) {
this.empLName = empLName;
}
/**
* @return the deptName
*/
public String getDeptName() {
return deptName;
}
/**
* @param deptName
* the deptName to set
*/
public void setDeptName(String deptName) {
this.deptName = deptName;
}
}
[/Code]
Summary: So in this article we have seen how spring JDBC template can be used in Java based standard and enterprise applications. We have also discussed the advantages of this approach and how it can make the development much faster compared to the standard JDBC based development.
Let us summarize our discussion in the form of following bullet points –
- Spring has become an essential part of the modern Java based enterprise application development environment.
- Spring has different sections, out of which, the JDBC template is an important part.
- Spring JDBC template eases the development by taking the load of resource acquisition, connection management, exception handling, and general error checking. We as a developer just need to concentrate on the complex business scenario.
