The central class of the Spring JDBC abstraction framework is the JdbcTemplate class that includes the most common logic in using the JDBC API to access data, such as handling the creation of connection, statement creation, statement execution, and release of resource. The JdbcTemplate class can be found in the org.springframework.jdbc.core package.
The JdbcTemplate class instances are thread-safe once configured. A single JdbcTemplate can be configured and injected into multiple DAOs.
We can use the JdbcTemplate to execute the different types of SQL statements. Data Manipulation Language (DML) is used for inserting, retrieving, updating, and deleting the data in the database. SELECT, INSERT, or UPDATE statements are examples of DML. Data Definition Language (DDL) is used for either creating or modifying the structure of the database objects in the database. CREATE, ALTER, and DROP statements are examples of DDL.
The JdbcTemplate class is in the org.springframework.jdbc.core package. It is a non-abstract class. It can be initiated using any of the following constructors:
JdbcTemplate: Construct a newJdbcTemplateobject. When constructing an object using this constructor, we need to use thesetDataSource()method to set theDataSourcebefore using this object for executing the statement.JdbcTemplate(DataSource): Construct a newJdbcTemplateobject, and initialize it with a givenDataSourceto obtain the connections for executing the requested statements.JdbcTemplate(DataSource, Boolean): Construct a newJdbcTemplateobject, and initialize it by a givenDataSourceto obtain the connections for executing the requested statements, and the Boolean value describing the lazy initialization of the SQL exception translator.If the Boolean argument value is
true, then the exception translator will not be initialized immediately. Instead, it will wait until theJdbcTemplateobject is used for executing the statement. If the Boolean argument value isfalse, then the exception translator will be initialized while constructing theJdbcTemplateobject.
It also catches the JDBC exception and translates it into the generic and more informatics exception hierarchy, which is defined in the org.springframework.dao package. This class avoids common error and executes the SQL queries, updates the statements, stores the procedure calls, or extracts the results.
While using the JdbcTemplate, the application developer has to provide the code for preparing the SQL statement and the extract result. In this section, we will look into operations such as, query, update, and so on using the JdbcTemplate in Spring.
The Spring JdbcTemplate makes the application developer's life a lot easier by taking care of all the boilerplate code required for creating and releasing database connection, which saves development time. In the earlier section, we saw how to define the DataSource bean in the configuration file. To initialize the JdbcTemplate object, we will use the DataSource bean as ref. This is discussed while explaining the configuration file, Spring.xml.
The following code snippet shows the Spring.xml file:
<?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:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">
<context:annotation-config />
<context:component-scan base-package="org.packt.Spring.chapter5.JDBC.dao" />
<bean id="dataSource"class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName">
<value={jdbc.driverClassName}></value>
</property>
<property name="url">
<value={jdbc.url}></value>
</property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
<context:property-placeholder location="jdbc.properties"/>
</beans>The Spring JdbcTemplate provides many helpful methods for the CRUD operations for the database.
Here, we use the
select command to query the database using the JdbcTemplate class. Depending upon the following application requirements, the database table can be queried:
- The following is a simple query to get the number of rows in a relation:
int rowCount = this.jdbcTemplate.queryForObject("select count(*) from employee ", Integer.class); - A simple query that uses the bind variable is shown here:
int countOfEmployeesNamedRavi = this.jdbcTemplate.queryForObject( "select count(*) from employee where Name = ?", Integer.class, "Ravi"); - The following is a simple query for String:
String empName = this.jdbcTemplate.queryForObject( "select Name from employee where EmpId = ?", new Object[]{12121}, String.class); - The code block to populate a domain object after querying is shown here:
Employee employee = this.jdbcTemplate.queryForObject( "select Name, Age from employee where EmpId = ?", new Object[]{1212}, new RowMapper<Employee>() { public Employee mapRow(ResultSet rs, int rowNum) throws SQLException { Employee emp = new Employee(rs.getString("Name"), rs.getString("Age")); return emp; } }); - The code block to populate a list of the domain objects after querying is given here:
List<Employee> employee = this.jdbcTemplate.query( "select Name, Age from employee", new RowMapper<Employee>() { public Employee mapRow(ResultSet rs, int rowNum) throws SQLException { Employee emp = new Employee(rs.getString("Name"), rs.getString("Age")); return emp; } });
Apart from querying the database table, the operation for updating the record can also be performed as discussed in the next section.
When we talk about updating a record, it simply implies inserting a new record, making a change in an existing record, or deleting an existing record.
The Update() method is used to perform operations such as insert, update, or delete. The parameter values are usually provided as an object array or var args. Consider the following cases:
- The following shows an
Insertoperation:this.jdbcTemplate.update("insert into employee (EmpId, Name, Age) values (?, ?, ?)", 12121, "Ravi", "Soni"); - An
Updateoperation is shown here:this.jdbcTemplate.update("update employee set Name = ? where EmpId = ?", "Shree", 12121); - A
Deleteoperation is given here:this.jdbcTemplate.update("delete from employee where EmpId = ?",Long.valueOf(empId));
The final directory structure of the application is shown here:
The Employee class has parameterized the constructor with three parameters, namely, empId, name, and age:
package org.packt.Spring.chapter5.JDBC.model;
public class Employee {
private int empId;
private String name;
private int age;
public Employee(int empId, String name, int age) {
setEmpId(empId);
setName(name);
setAge(age);
}
// setter and getterThe EmployeeDao interface contains the declaration of a method whose implementation is provided in EmployeeDaoImpl.java:
package org.packt.Spring.chapter5.JDBC.dao;
import org.packt.Spring.chapter5.JDBC.model.Employee;
public interface EmployeeDao {
void createEmployee();
int getEmployeeCount();
int insertEmployee(Employee employee);
int deleteEmployeeById(int empId);
Employee getEmployeeById(int empId);
}Now let's look at the implementation of EmployeeDao, where we will use the JdbcTemplate class to execute the different types of queries:
package org.packt.Spring.chapter5.JDBC.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.packt.Spring.chapter5.JDBC.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeDaoImpl implements EmployeeDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int getEmployeeCount() {
String sql = "select count(*) from employee";
return jdbcTemplate.queryForInt(sql);
}
@Override
public int insertEmployee(Employee employee) {
String insertQuery = "insert into employee (EmpId, Name, Age) values (?, ?, ?) ";
Object[] params = new Object[] { employee.getEmpId(),
employee.getName(), employee.getAge() };
int[] types = new int[] { Types.INTEGER, Types.VARCHAR, Types.INTEGER };
return jdbcTemplate.update(insertQuery, params, types);
}
@Override
public Employee getEmployeeById(int empId) {
String query = "select * from Employee where EmpId = ?";
// using RowMapper anonymous class, we can create a separate RowMapper
// for reuse
Employee employee = jdbcTemplate.queryForObject(query,
new Object[] { empId }, new RowMapper<Employee>() {
@Override
public Employee mapRow(ResultSet rs, int rowNum)
throws SQLException {
Employee employee = new Employee(rs.getInt("EmpId"), rs
.getString("Name"), rs.getInt("Age"));
return employee;
}
});
return employee;
}
@Override
public int deleteEmployeeById(int empId) {
String delQuery = "delete from employee where EmpId = ?";
return jdbcTemplate.update(delQuery, new Object[] { empId });
}
}