{"id":22156,"date":"2015-04-14T11:00:02","date_gmt":"2015-04-14T08:00:02","guid":{"rendered":"http:\/\/examples.javacodegeeks.com\/?p=22156"},"modified":"2019-03-05T13:09:10","modified_gmt":"2019-03-05T11:09:10","slug":"jdbc-batch-update-example","status":"publish","type":"post","link":"https:\/\/examples.javacodegeeks.com\/java-development\/core-java\/sql\/jdbc-batch-update-example\/","title":{"rendered":"JDBC Batch Update Example"},"content":{"rendered":"

1. Introduction<\/h2>\n

This article presents a simple example of performing JDBC Batch Update. It assumes that the reader is familiar with the JDBC (Java DataBase Connectivity<\/em>) 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.
\n 
\n 
\n 
\n 
\n <\/p>\n

 
\nConsider 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 \u201cBatch Update\u201d comes into the picture. \u2018Batch Operation\u2019 implies grouping a certain chunk of operations into one unit.<\/p>\n

The official page reads: \u201cThe 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.<\/em>\u201d 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 \u2018batched\u2019 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.<\/p>\n

This article demonstrates the use of JDBC Batch Update operation. It talks about the two ways in which \u2018Batch Update\u2019 can be done using Statement<\/a><\/code> and PreparedStatement<\/a><\/code> objects. Though the example revolves around update<\/code>; delete<\/code> and insert<\/code> operations can also be \u2018batched\u2019. Of course, batching select<\/code> 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 \u2018batch\u2019 fails? Well that might leave the database in an inconsistent state. How should that be handled? This is when \u2018transactions\u2019 come to the rescue. It is a feature of the Connection<\/a><\/code> object. We are going to run our Batch operations in transactions, which can be thought of as \u2018atomic execution units\u2019. 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.<\/p>\n

2. Example Walk-through<\/h2>\n

2.1 Technologies used in this demonstration<\/h3>\n