Spring JdbcTemplate Tutorial

One of the most popular and powerful tools to connect to the database and execute SQL queries, the Spring JDBC template is just made to simplify the JDBC applications of the Spring framework. It helps to eradicate a lot of problems with the JDBC Application programming interface (API) by using the JDBC API internally. JDBC template works as a backbone of the core JDBC workflow and helps eliminate common errors. Its application code is used to provide SQL (Structured Query Language) and extract results from it. The central class that comes in the JDBC core package is The org.spring framework.jdbc.core.JdbcTemplate class.

Spring JDBC Template Specifications

• The Spring JdbcTemplate produces plenty of boilerplate code like commencing or closing a connection to a database, SQL exceptions handling, etc;
• In the Spring Framework, the JDBC implementation simplifies working with many low-level operations such as opening or closing connections, executing SQL queries, etc.
• Spring JDBC Template enables us to work with the database in the Spring Framework, where we just need to define the connection parameters from the database and register the SQL query, Spring will take care of the rest.
• The database interaction can be performed with the help of several classes (also known as approaches) of the JDBC template in Spring.
• The operations that are included in the JDBC template class are executing SQL queries or updates, initiating iteration over ResultSets as well as catching JDBC exceptions, and translating them to the more generic informative exception hierarchy as defined in the org.springframework.dao package.
• The instances of the JdbcTemplate class can be used for several DAO (Data Access) objects. So the JDBC template class instances are thread-safe.
• The JdbcTemplate is commonly configured in the Spring configuration file, then it is implemented using bean in DAO classes.

Advantages of Spring JdbcTemplate

• The Spring JDBC template provides you with many methods to write the queries directly. Hence, it saves a lot of work and time.
• The Spring JDBC template provides the functionality to automatically clean up the resources. For example, release the database connections.
• The JDBC template of Spring will transform the standard JDBC SQLExceptions into RuntimeExceptions which enables the programmer to solve the errors in a more flexible manner.
• By Spring JDBC template, you can change the vendor-specific error messages to more understandable error messages.
• The Spring JDBC template provides a more convenient way to query the database. There are several methods that are included in the Spring JDBC template to achieve this. Some are ResultSetExtractor and RowMapper where the former is used to translate the SQL result into an object directly and the latter converts the same in a list of objects.
• The use of Spring JDBC Template also helps a lot in overcoming many problems in the JDBC API in such a way that:
• It is a time-saving concept as there is no need to write a lot of code before and after executing the query like creating connection, statement, closing resultset, connection, etc.
• It removes the need to perform exception handling code on the database logic.
• No need to handle transactions.
• It saves a lot of time by removing the need to write the code from one database to another.

The Spring JDBC Packages

Before we further delve into the concept of the JDBC template in the Spring framework, we should know that the JDBC is divided into four different packages, that are:

core

This is the core functionality of the JDBC. The core package of the JDBC template includes some useful and important classes, which are JdbcTemplate, SimpleJdbcInsert, SimpleJdbcCall, and NamedParameterJdbcTemplate.

datasource

These are the utility classes that are used to access a data source. It has several data source implementations for testing JDBC code in the Jakarta EE container externally. It is always to be noted that a datasource should be configured as a bean in the application context.

object

It allows database access in an object-oriented style. By using objects, we are able to run queries and return the results as a business object. It provides the functionality to map the query results between the columns and properties of business objects.

support

This package of JDBC supports classes for classes under core and object packages. For example, it provides the functionality of SQLException translation.

Spring Jdbc Approaches

There are four approaches that are supported by the Spring framework for JDBC database access that is:

• JdbcTemplate
• NamedParameterJdbcTemplate
• SimpleJdbcTemplate
• SimpleJdbcInsert and SimpleJdbcCall

But in this tutorial, we will study the usage of the JDBC template to access the database.

JdbcTemplate Class

Defined as the central class in the JDBC core package, JdbcTemplate class is responsible for the creation and release of resources like creating and closing of the connection object, etc; The interesting thing in this is that there will not be any problem that can occur in case you forget to close the connection. It takes care of the exception and provides the informative exception messages through exception classes defined in the org.springframework.dao package.

JdbcTemplate class provides all the database operations ranging from insertion, update, deletion to retrieval of the data from the database. Its code only needs to implement callback interfaces. The parameters of this class include callback interfaces and the SQLExceptionTranslator interface that results in the removal of the requirement to subclass it. The entire SQL operations in the JdbcTemplate class are logged at debug level by the log category that is defined as "org.springframework.jdbc.core.JdbcTemplate".

Spring JdbcTemplate Class Methods

public int update(String query)

This method is used to insert, update and delete records in a relational database.

public int update(String query,Object... args)

This method is used to insert, update and delete records using PreparedStatement using given arguments.

public void execute(String query)

This method is used to execute Data Definition Language (DDL).

public T execute(String sql, PreparedStatementCallback action)

This method is used to execute the query by using PreparedStatement callback.

public T query(String sql, ResultSetExtractor rse)

This method is used to retrieve records using ResultSetExtractor.

public List query(String sql, RowMapper rse)

Here in this method, RowMapper is used to fetch records.

Example of Spring JdbcTemplate

Let’s better understand the above methods by an example that uses the JDBC template class. We have just created a table inside the Oracle10g database.

create table employee(  
id number(10),  
name varchar2(100),  
salary number(10)  
); 

Now we will create a class named Employee.java that includes three properties with constructors and their setters and getters.

package com.Intellinuts;  
public class Employee {  
private int id;  
private String name;  
private float salary;  
//no-arg and parameterized constructors  
//getters and setters  
} 

Going further, we create a class named EmployeeDao.java that includes one property jdbcTemplate, and three methods that are saveEmployee(), updateEmployee, and deleteEmployee().

package com.Intellinuts;  
import org.springframework.jdbc.core.JdbcTemplate;  

public class EmployeeDao {  
private JdbcTemplate jdbcTemplate;  

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
    this.jdbcTemplate = jdbcTemplate;  
}  

public int saveEmployee(Employee e){  
    String query="insert into employee values(  
    '"+e.getId()+"','"+e.getName()+"','"+e.getSalary()+"')";  
    return jdbcTemplate.update(query);  
}  
public int updateEmployee(Employee e){  
    String query="update employee set   
    name='"+e.getName()+"',salary='"+e.getSalary()+"' where id='"+e.getId()+"' ";  
    return jdbcTemplate.update(query);  
}  

public int deleteEmployee(Employee e){  
    String query="delete from employee where id='"+e.getId()+"' ";  
    return jdbcTemplate.update(query);  
}  

}  

Now we will create the XML configuration file named applicationContext.xml that stores the bean information.

<?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:p="http://www.springframework.org/schema/p"  
    xsi:schemaLocation="http://www.springframework.org/schema/beans   
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">  

<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />  
<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />  
<property name="username" value="system" />  
<property name="password" value="oracle" />  
</bean>  
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
<property name="dataSource" ref="ds"></property>  
</bean>  
<bean id="edao" class="com.Intellinuts.EmployeeDao">  
<property name="jdbcTemplate" ref="jdbcTemplate"></property>  
</bean>  

</beans>  

In the above file, there is a property named datasource in the JdbcTemplate class of the DriverManagerDataSource type. Here the JdbcTemplate class will need the reference from the DriverManagerDataSource object for the datasource property.

In the EmployeeDao class, we are using the JdbcTemplate object that is passed by the setter method, but the same can be done by the constructor also.

Now we will create a class named Test.java that will execute the query by getting the bean information from the applicationContext.xml file, and calling the saveEmployee() method. You can also execute the query by calling the updateEmployee() and deleteEmployee() method by uncommenting the code.

package com.Intellinuts;  

import org.springframework.context.ApplicationContext;  
import org.springframework.context.support.ClassPathXmlApplicationContext;  
public class Test {  

public static void main(String[] args) {  
    ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");  

    EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");  
    int status=dao.saveEmployee(new Employee(102,"Amit",35000));  
    System.out.println(status);  
    /*int status=dao.updateEmployee(new Employee(102,"Sonoo",15000)); 
    System.out.println(status); 
    */  
    /*Employee e=new Employee(); 
    e.setId(102); 
    int status=dao.deleteEmployee(e); 
    System.out.println(status);*/  
}  

}