JDBC Batch Processing Example
In this example, we will see how we can use Batch Processing inJava Database Connectivity(i.e.JDBC).
When multiple inserts are to be made to the table in a database, the trivial way is to execute a query per record.
However, this involves acquiring and releasing connection every time a record is inserted, which hampers application performance.
We overcome, this(acquiring and releasingconnection every-time) by making use of Batch operations in JDBC.
We set the parameters in thejava.sql.PreparedStatement and it to the batch usingPreparedStatement.addBatch() method. Then when the batch size reaches a desired threshold, we execute the batch usingPreparedStatement.executeBatch() method.
Another way to avoid manually releasing/acquiring connection is to use Connection Pooling.
However, when executing Queries in Batch, it is sometimes important to maintain theatomicity of the database. This can be a problem if one the commands in the batch throws some error since the commands after the exception will not be executed leaving the database in an inconsistent state. So we set the auto-commit to false and if all the records are executed successfully, we commit the transaction. This maintains the integrity of the the database.
We will try to understand points explained above with the help of an example :
BatchExample.java:
package com.javacodegeeks.examples;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Arrays;public class BatchExample{public static void main(String[] args){try (Connection connection = DBConnection.getConnection()){connection.setAutoCommit(false);try (PreparedStatement pstmt = connection.prepareStatement("Insert into txn_tbl (txn_id,txn_amount, card_number, terminal_id) values (null,?,?,?)");){pstmt.setString(1, "123.45");pstmt.setLong(2, 2345678912365L);pstmt.setLong(3, 1234567L);pstmt.addBatch();pstmt.setString(1, "456.00");pstmt.setLong(2, 567512198454L);pstmt.setLong(3, 1245455L);pstmt.addBatch();pstmt.setString(1, "7859.02");pstmt.setLong(2, 659856423145L);pstmt.setLong(3, 5464845L);pstmt.addBatch();int[] arr = pstmt.executeBatch();System.out.println(Arrays.toString(arr));connection.commit();}catch (SQLException e){e.printStackTrace();connection.rollback();}}catch (Exception e){e.printStackTrace();}}}DBConnection.java:
package com.javacodegeeks.examples;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/*** @author Chandan Singh*/public class DBConnection{public static Connection getConnection() throws SQLException, ClassNotFoundException{Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jcg?rewriteBatchedStatements=true", "root", "toor");return connection;}}One important point to note here is theconnection URL. TherewriteBatchedStatements=true is important since it nudges the JDBC to pack as many queries as possible into a single network data packet, thus lowering the traffic. Without that parameter, there will not be much performance improvement when using JDBC Batch.
Thank you!
We will contact you soon.
We can use JDBC Batch, when using
java.sql.Statement, in similar fashion.ThePreparedStatement.executeBatch() method retruns anint array. Depending upon the values, we can know the status of the each executed queries:
- A value greater than zero usually indicates successful execution of query.
- A value equal to
Statement.SUCCESS_NO_INFOindicates, successful command execution but no record count is available. - A value equal to
Statement.EXECUTE_FAILEDindicates, command was not executed successfully. It shows up only if the driver continued to execute the commands after the failed command.
Summary:
Here we tried to understand what is JDBC Batch and how we can the use the same to reduce network traffic and improve our application performance.
You can download the source code of this example here:BatchOperation.zip
Thank you!
We will contact you soon.
