The single executable unit for performing multiple operations is known as a batch. If you batch multiple calls to the same prepared statement, then most of the JDBC drivers show improved performance. Moreover, if you group the updates into batches, then you can limit the number of round trips to the database, as shown in the following diagram:
As shown in the aforementioned figure, we have Server-1, where our Java application is running, and in Server-2, the database is running. Both the servers are situated in different locations. Let's assume that we have to execute 100 queries. Generally, we send each query from the Java application to the database server and execute them one by one. Here, we have sent the SQL-1 query from the Java application to the database server for execution, and then the SQL-2 query, and so on till the SQL-100 query. So here, for 100 queries, we have to send the SQL queries from the Java application to the database server through the network. This will add a communication overhead and reduce the performance. So to improve the performance and reduce the communication overhead, we use the JDBC batch processing, as shown here:
JDBC with batch processing
In the preceding figure, we have a batch with 100 SQL queries, which will be sent from the Java application server to the database server only once, and they will still be executed. So, there is no need to send each SQL query from the Java application server to the database server. In this way, it will reduce the communication overhead and improve the performance.
The batch update operation allows you to submit multiple SQL queries to the DataSource for processing at once. Submitting multiple SQL queries at once instead of submitting them individually, improves the performance.
This section explains how to use an important batch update option with the JdbcTemplate. The JdbcTemplate includes a support for executing the batch of statements through a JDBC statement and through PreparedStatement.
The JdbcTemplate includes the following two overloaded batchUpdate() methods that support this feature:
- One method is for executing a batch of SQL statements using the JDBC statement. This method's signature is that it issues multiple SQL updates, as shown here:
public int[] batchUpdate(String[] sql) throws DataAccessException
The following sample code shows how to use this method:
jdbcTemplate.batchUpdate (new String [] { "update emp set salary = salary * 1.5 where empId = 10101", "update emp set salary = salary * 1.2 where empId = 10231", "update dept set location = 'Bangalore' where deptNo = 304" }); - The other method is for executing the SQL statement multiple times with different parameters using
PreparedStatement, as shown by the following code snippet:public int[] batchUpdate(String sql, BatchPreparedStatementSetter bPSS) throws DataAccessException
Let's consider an example of a code, where an update batch operation performs actions.
The final directory structure of the application is shown here:
The EmployeeDaoImp class has the method insertEmployees() that performs the batch insert operation, as shown here:
package org.packt.Spring.chapter5.JDBC.dao;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import org.packt.Spring.chapter5.JDBC.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeDaoImpl {
@Autowired
private JdbcTemplate jdbcTemplate;
public void insertEmployees(final List<Employee> employees) {
jdbcTemplate.batchUpdate("INSERT INTO employee "
+ "(id, name) VALUES (?, ?)",
new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i)
throws SQLException {
Employee employee = employees.get(i);
ps.setLong(1, employee.getId());
ps.setString(2, employee.getName());
}
public int getBatchSize() {
return employees.size();
}
});
}
public int getEmployeeCount() {
String sql = "select count(*) from employee";
return jdbcTemplate.queryForInt(sql);
}
}The HrPayrollBatchUpdate class calls a method from EmployeeDaoImp to perform a batch update operation:
package org.packt.Spring.chapter5.JDBC.batchupdate;
public class HrPayrollBatchUpdate {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext(
"Spring.xml");
EmployeeDaoImp employeeDaoImp = (EmployeeDaoImp) context
.getBean("employeeDaoImp");
List<Employee> employeeList = new ArrayList<Employee>();
Employee employee1 = new Employee(10001, "Ravi");
Employee employee2 = new Employee(23330, "Kant");
Employee employee3 = new Employee(12568, "Soni");
employeeList.add(employee1);
employeeList.add(employee2);
employeeList.add(employee3);
employeeDaoImp.insertEmployees(employeeList);
System.out.println(employeeDaoImp.getEmployeeCount());
}
}The preceding code shows how to use the batchUpdate() method with string and BatchPreparedStatementSetter for executing a SQL statement multiple times with different parameter values. In this section, we have seen how to execute batch statements using a JdbcTemplate.