Batch Processing

Why

There can be requirement where multiple times DML i.e. insert, update or delete operations are needed to be performed with database. In such case hitting database again and again to execute queries might cause database performance bottle neck.

What

To overcome such problems JDBC API supports batch processing that allows us to perform related operation all together in one shot. Means similar type of database operations can be clubbed/grouped to form a batch and that batch can be submitted to database as single command. This helps improving database performance. Batch processing can be carried out on DMLs(insert, update, delete) and/or database queries that returns nothing like DDL commands.

How

Batch processing in Statement

Batch processing in Statement is two step process

1. Add related DMLs in a batch, to achieve this addBatch(String sql) can be invoked on Statement object
2. Execute the batch, to achieve this executeBatch() can be invoked on Statement object

Modifier and Type	Method and Description
void	addBatch(String sql) Adds the given SQL command to the current list of commands for this Statement object.
void	clearBatch() Empties this Statement object's current list of SQL commands.
int[]	executeBatch() Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
default long[]	executeLargeBatch() Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Batch processing in PreparedStatement

Batch processing in PreparedStatement is two step process

1. Add set of params to a batch, to achieve this addBatch() can be invoked on PreparedStatement object
2. Execute the batch, to achieve this executeBatch() can be invoked on Statement type object

Modifier and Type	Method and Description
void	addBatch() Adds a set of parameters to this PreparedStatement object's batch of commands.