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 new JdbcTemplate
object. When constructing an object using this constructor, we need to use the setDataSource()
method to set the DataSource
before using this object for executing the statement.JdbcTemplate(DataSource)
: Construct a new JdbcTemplate
object, and initialize it with a given DataSource
to obtain the connections for executing the requested statements.JdbcTemplate(DataSource, Boolean)
: Construct a new JdbcTemplate
object, and initialize it by a given DataSource
to 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 the JdbcTemplate
object is used for executing the statement. If the Boolean argument value is false
, then the exception translator will be initialized while constructing the JdbcTemplate
object.
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:
int rowCount = this.jdbcTemplate.queryForObject("select count(*) from employee ", Integer.class);
int countOfEmployeesNamedRavi = this.jdbcTemplate.queryForObject( "select count(*) from employee where Name = ?", Integer.class, "Ravi");
String empName = this.jdbcTemplate.queryForObject( "select Name from employee where EmpId = ?", new Object[]{12121}, String.class);
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; } });
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:
Insert
operation:this.jdbcTemplate.update("insert into employee (EmpId, Name, Age) values (?, ?, ?)", 12121, "Ravi", "Soni");
Update
operation is shown here:this.jdbcTemplate.update("update employee set Name = ? where EmpId = ?", "Shree", 12121);
Delete
operation 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 getter
The 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 }); } }