Oracle Batching - Introduction

We are exploring different methods to insert data from Excel to Oracle in Java using Apache POI / JDBC . In part 1 of this post, we saw a basic JDBC Example of inserting data in Java. In Part 2, we discussed standard batching approach where we bulk inserted data from Excel to Oracle. In this post, we will discuss Oracle batching as the target database is Oracle. We use OraclePreparedStatement for this. Let us get started in this example. You should refer to Part 1 to get the input data / worksheet to work with the examples.

Excel to Oracle - Oracle Batching - Java POI JDBC Example Program

In Oracle batching, we use OraclePreparedStatement to set the batch size at the beginning where we create the prepared statement. Once this is done, you can read the data from the Excel file using POI normally. When you call statement.execute, the batch size is checked and executed only if the batch size is reached. Even if you have some records in your excel document, that does not meet a batch length, they will be processed when you commit the transaction. There is no change to the way you read data from the Excel workbook. The code example is provided below:

This program does the same as the previous two, but is specific to Oracle database. When executed, it inserted all the five records in the Excel document to Oracle DB table successfully.

That completes the tutorial series, where we explored different approaches to insert data from Excel to Oracle in Java using POI / JDBC. You can write an XLSX example on the same lines. We would have written an example for this for sure, make sure you check it out in the related post section below. If you have any questions, you can post it in the comments section.