JDBC Batch

Today we will look into JDBC Batch insert and update examples in MySQL and Oracle databases. Sometimes we need to run bulk queries of a similar kind for a database. For example, loading data from CSV files to relational database tables.

As we know that we have option to use Statement or PreparedStatement to execute queries. Apart from that JDBC provides Batch Processing feature through which we can execute the bulk of queries in one go for a database.

JDBC Batch

JDBC batch statements are processed through Statement and PreparedStatement addBatch() and executeBatch() methods. This tutorial is aimed to provide details about JDBC Batch insert example for MySQL and Oracle database.

We will look into different programs so we have a project with the structure as below image.

JDBC Batch insert update MySQL Oracle With Examples

Notice that I have MySQL and Oracle DB JDBC Driver jars in the project build path so that we can run our application across MySQL and Oracle DB both.

Let’s first create a simple table for our test programs. We will run the bulk of JDBC insert queries and look at the performance with different approaches.

We will read the Database configuration details from the property file so that switching from one database to another is quick and easy.

db.properties

Before we move into actual JDBC batch insert example to insert bulk data into the Employee table, let’s write a simple utility class to get the database connection.

DBConnection.java

Now let’s look at the different approach we can take for JDBC batch insert example.

  1. Use Statement to execute one query at a time.JDBCStatement.java
  2. Use PreparedStatement to execute one query at a time.JDBCPreparedStatement.java

    This approach is similar to using Statement but PreparedStatement provides performance benefits and avoids SQL injection attacks.
  3. Using Statement Batch API for bulk processing.JDBCStatementBatch.java

    We are processing 10,000 records with a batch size of 1000 records. Once the batch size reaches, we are executing it and continue processing remaining queries.
  4. Using PreparedStatement Batch Processing API for bulk queries.JDBCPreparedStatementBatch.java

Let’s see how our programs work with MySQL database, I have executed them separately multiple times and below table contains the results.

MySQL DB Statement PreparedStatement Statement Batch PreparedStatement Batch
Time Taken (ms) 8256 8130 7129 7019

When I looked at the response time, I was not sure whether it’s right because I was expecting some good response time improvements with Batch Processing. So I looked online for some explanation and found out that by default MySQL batch processing works in a similar way like running without batch.

To get the actual benefits of Batch Processing in MySQL, we need to pass rewriteBatchedStatements as TRUE while creating the DB connection. Look at the MySQL URL above in db.properties file for this.

With rewriteBatchedStatements as true, below table provides the response time for the same programs.

MySQL DB Statement PreparedStatement Statement Batch PreparedStatement Batch
Time Taken (ms) 5676 5570 3716 394

As you can see that PreparedStatement Batch Processing is very fast when rewriteBatchedStatements is true. So if you have a lot of batch processing involved, you should use this feature for faster processing.

Oracle Batch Insert

When I executed above programs for Oracle database, the results were in line with MySQL processing results and PreparedStatement Batch processing was much faster than any other approach.

JDBC Batch Processing Exceptions

Let’s see how batch programs behave in case one of the queries throw exceptions.

JDBCBatchExceptions.java

When I executed the above program for MySQL database, I got below exception and none of the records were inserted in the table.

When executed the same program for Oracle database, I got below exception.

But the rows before exception were inserted into the database successfully. Although the exception clearly says what the error is but it doesn’t tell us which query is causing the issue. So either we validate the data before adding them for batch processing or we should use JDBC Transaction Management to make sure all or none of the records are getting inserted in case of exceptions.

Same program with JDBC transaction management looks like below.

JDBCBatchExceptions.java

As you can see that I am rolling back the transaction if any SQL exception comes. If the batch processing is successful, I am explicitly committing the transaction.

Summary

That’s all for JDBC Batch insert update example, make sure to experiment with your data to get the optimal value of batch size for bulk queries. One of the limitations of JDBC batch processing is that we can’t execute different type of queries in the batch.

By admin

Leave a Reply