{"id":18019,"date":"2015-01-02T11:21:56","date_gmt":"2015-01-02T09:21:56","guid":{"rendered":"http:\/\/examples.javacodegeeks.com\/?p=18019"},"modified":"2019-03-05T12:59:50","modified_gmt":"2019-03-05T10:59:50","slug":"jdbc-batch-processing","status":"publish","type":"post","link":"https:\/\/examples.javacodegeeks.com\/java-development\/core-java\/sql\/jdbc-batch-processing\/","title":{"rendered":"JDBC Batch Processing Example"},"content":{"rendered":"

In this example, we will see how we can use Batch Processing in Java Database Connectivity<\/em>(i.e.JDBC).<\/p>\n

When multiple inserts are to be made to the table in a database, the trivial way is to execute a query per record.
\nHowever, this involves acquiring and releasing connection every time a record is inserted, which hampers application performance.<\/p>\n

We overcome, this(acquiring and releasing connection<\/code> every-time) by making use of Batch operations in JDBC.
\n 
\n 
\n <\/p>\n

 
\nWe set the parameters in the java.sql.PreparedStatement<\/code> and it to the batch using PreparedStatement.addBatch()<\/code> method. Then when the batch size reaches a desired threshold, we execute the batch using PreparedStatement.executeBatch()<\/code> method.<\/p>\n

TIP:<\/strong>
\nAnother way to avoid manually releasing\/acquiring connection is to use Connection Pooling.<\/div>\n

However, when executing Queries in Batch, it is sometimes important to maintain the atomicity<\/code> 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.

<\/div> <\/div><\/p>\n

We will try to understand points explained above with the help of an example :<\/p>\n

BatchExample.java:<\/em>
\n<\/span><\/p>\n

package com.javacodegeeks.examples;\n\nimport java.sql.Connection;\nimport java.sql.PreparedStatement;\nimport java.sql.SQLException;\nimport java.util.Arrays;\n\n\npublic class BatchExample\n{\n\tpublic static void main(String[] args)\n\t{\n\t\ttry (Connection connection = DBConnection.getConnection())\n\t\t{\n\t\t\tconnection.setAutoCommit(false);\n\n\t\t\ttry (PreparedStatement pstmt = connection.prepareStatement(\"Insert into txn_tbl (txn_id,txn_amount, card_number, terminal_id) values (null,?,?,?)\");)\n\t\t\t{\n\t\t\t\tpstmt.setString(1, \"123.45\");\n\t\t\t\tpstmt.setLong(2, 2345678912365L);\n\t\t\t\tpstmt.setLong(3, 1234567L);\n\t\t\t\tpstmt.addBatch();\n\n\t\t\t\tpstmt.setString(1, \"456.00\");\n\t\t\t\tpstmt.setLong(2, 567512198454L);\n\t\t\t\tpstmt.setLong(3, 1245455L);\n\t\t\t\tpstmt.addBatch();\n\n\t\t\t\tpstmt.setString(1, \"7859.02\");\n\t\t\t\tpstmt.setLong(2, 659856423145L);\n\t\t\t\tpstmt.setLong(3, 5464845L);\n\t\t\t\tpstmt.addBatch();\n\n\t\t\t\tint[] arr = pstmt.executeBatch();\n\t\t\t\tSystem.out.println(Arrays.toString(arr));\n\n\t\t\t\tconnection.commit();\n\t\t\t}\n\t\t\tcatch (SQLException e)\n\t\t\t{\n\t\t\t\te.printStackTrace();\n\t\t\t\tconnection.rollback();\n\t\t\t}\n\t\t}\n\t\tcatch (Exception e)\n\t\t{\n\t\t\te.printStackTrace();\n\t\t}\n\t}\n}\n\n\n<\/pre>\n
 DBConnection.java:<\/em>
\n<\/span><\/p>\n
package com.javacodegeeks.examples;\n\n\nimport java.sql.Connection;\nimport java.sql.DriverManager;\nimport java.sql.SQLException;\n\n\n\/**\n* @author Chandan Singh\n*\/\npublic class DBConnection\n{\n\tpublic static Connection getConnection() throws SQLException, ClassNotFoundException\n\t{\n\t\tConnection connection = DriverManager.getConnection(\"jdbc:mysql:\/\/localhost:3306\/jcg?rewriteBatchedStatements=true\", \"root\", \"toor\");\n\n\t\treturn connection;\n\t}\n\t\n}\n\n\n<\/pre>\n
One important point to note here is the connection URL <\/strong>. The rewriteBatchedStatements=true<\/code> 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.[ulp id=’TD36VgQCKfhTAygK’]<\/p>\n
NOTE:<\/strong>
\nWe can use JDBC Batch, when using java.sql.Statement<\/code>, in similar fashion.<\/div>\n
The PreparedStatement.executeBatch()<\/code> method retruns an int array<\/code>. Depending upon the values, we can know the status of the each executed queries:<\/p>\n