JDBC Batch Update Example

1. Introduction

This article presents a simple example of performing JDBC Batch Update. It assumes that the reader is familiar with the JDBC (Java DataBase Connectivity) API which is just one of the tools in Java for connecting to a database from a client. The API provides several simple methods for querying and updating data in a database.
 
 
 
 
 

Want to be a JDBC Master ?
Subscribe to our newsletter and download the JDBC Ultimate Guide right now!
In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Thank you!

We will contact you soon.


 
Consider a situation in which hundreds or more database records need to be affected, that is updated, inserted or deleted. Making a network call for each operation could mean more time, more network traffic, more communication overhead which could adversely hit performance. This is when “Batch Update” comes into the picture. ‘Batch Operation’ implies grouping a certain chunk of operations into one unit.

The official page reads: “The batch update facility allows a Statement object to submit a set of heterogeneous SQL statements together as a single unit, or batch, to the underlying data source.” What it means is, if there are say, 200 database operations that need to be performed then instead of each operation hitting the database once we can have the 200 operations ‘batched’ into say, 4 batches of 50 operations each. Thus the database would be hit just 4 times instead of the earlier 200 times. The JDBC specification limits the number of operations in a Batch to a max size of 100 but individual databases may have their own limits.

This article demonstrates the use of JDBC Batch Update operation. It talks about the two ways in which ‘Batch Update’ can be done using Statement and PreparedStatement objects. Though the example revolves around update; delete and insert operations can also be ‘batched’. Of course, batching select statements does not make much sense. One thing to be mindful about is that the database executes each operation in the batch separately. So what happens if any one of the operations in the ‘batch’ fails? Well that might leave the database in an inconsistent state. How should that be handled? This is when ‘transactions’ come to the rescue. It is a feature of the Connection object. We are going to run our Batch operations in transactions, which can be thought of as ‘atomic execution units’. So if all operations in a batch succeed, the whole transaction succeeds. But if any one of the operations fails, the whole transaction is made to fail or is rolled back. This ensures that the database state always stays consistent. The complete working example is available at the end of the article for reference.

2. Example Walk-through

2.1 Technologies used in this demonstration

2.2 Sample data used in the Example

Sample Data

2.3 Project Structure used

Project structure

3. Approach 1: Jdbc Batch Update using Statement object

4. Approach 2: Jdbc Batch Update using PreparedStatement

5. Download the source code

This concludes our example of using JDBC Batch Update using both Statement and PreparedStatement. As promised, the example code has been shared below.

Download
Download the full source code of this example here: JdbcBatchUpdateExample
Exit mobile version