Overview

The Spring framework has become an essential part of modern Java-based application development and has effectively managed to take control of every department in the Java development world. The JDBC template for Spring is used in most of the Spring-based JEE applications to communicate with the database. This article will discuss how to access a relational database using the JDBC template in Spring.

Introduction

We all know the importance of relational database in enterprise application development. Spring is one of the most widely used frameworks in Java based enterprise application development. Spring has different modules such as ORM, security, transaction, JPS, JMS, etc. 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 more efficient. Spring provides a simple approach to handle database activities known as the 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 such as creating connection, statement, resultset, etc.
• Need to handle exception handling code separately.
• Need to handle transactional issues.

Advantages of the Spring JDBC Template

Spring JDBC template has the following advantages compared with the normal approach of standard JDBC.

• Cleaning of used resources is done automatically by the Spring JDBC template. 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 manner.
• The Spring JDBC template also converts the vendor specific errors in a more meaningful message, thus the handling of those errors is more efficient.

Getting Started

In this document, let’s use Gradle as the tool to build our application. Let us start with a simple application used to store and retrieve employee details, along with their departments in 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 that will be used to communicate with the database. Our POJO classes for these two entities are listed below:

Listing 1: Sample showing the Employee Class

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

Listing 2: Sample showing the Department Class

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

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 bulk of JDBC code is occupied in handling connection management, resource acquisition, exception handling, and general error checking, which is irrelevant 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 below:

Listing 3: Sample showing the implementation of JDBC template

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) 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
",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 results = jdbcTemplate.query(				"select * from employees where first_name = ?",				new Object[] { "Erin" }, new RowMapper() {		@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);		}	}}

In the example above, 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 a 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. As a result, 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 in Listing 4.

Listing 4: Sample showing the modified query

List 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() {@Overridepublic 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);		}

To execute this we should have another POJO class EmployeeDtls, such as:

Listing 5: Sample showing the POJO class EmployeeDtls

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

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.

?

About the Author

Kaushik Pal is a technical architect with 15 years of experience in enterprise application and product development. He has expertise in web technologies, architecture/design, Java/J2EE, Open source and big data technologies. You can find more of his work at www.techalpine.com and you can email him here.