Batch Processing
As name suggest ,Batch processing is a group of related SQL statements in a batch and after execution submit the batch into database.
In other words we can say that it submit multiple statements to the database for processing as a single unit.
By grouping statements into batches you can minimize the number of round trips to the database and thus help in improving performance. So it is better to use batch processing as compared to execute each statement separately.
Methods in Batch Processing
DatabaseMetaData.supportsBatchUpdates(): This method returns either true or false, if it returns true then it means JDBC drives supports Batch process.
addBatch() : This method is used to add statements in batch. It can also be callable statement or prepared statements.
executeBatch(): After Calling this method, all the statements in the batch is sent to database and execute statements in the order in which they are added.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
package com.j2eereference.coreJava; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class BatchDemo{ public static void main(String[] args) throws SQLException { Connection con = null; PreparedStatement stmt= null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:mysql://localhost/database"; con = DriverManager.getConnection(url, "username", "password"); String sql = "UPDATE authors set author name =? WHERE id = ?"; stmt = con.prepareStatement(sql); stmt.setString(1,"name1"); stmt.addBatch(); stmt.setString(2,"namwe2"); stmt.addBatch(); stmt.setString(3,"name3"); stmt.addBatch(); } catch (Exception e) { e.printStackTrace(); } finally { if (stmt != null) { stmt.close(); } if (con != null) { con.close(); } } } } |
Above example use preparedstatement in Batch Processing, however we can also use statements and callable statements.
clearBatch() : This method is used to remove the statements that are added to the batch. However we cannot select statement which we want to remove.
Commiting Transaction in Batch
To execute a query in JDBC, we have to first creates a connection. Created connection is in auto-commit mode by default.
thus when we execute a sql statement it is committed automatically and transaction is done.
A statement is said to be completed if it retrieves all its resultset and then committed.
In case of Batch processing , we have a batch of statements, so if any one fails to execute then we get undesirable results.
To avoid this, we have to disable auto-commit mode by using the below statement setAutoCommit(false).
By executing this in our code we set autocommit mode to false so that auto-commit mode is disable.
We have to call commit method explicitly in this case in order to commit sql statements.
Consider the above example with setAutoCommit mode false
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
package com.j2eereference.coreJava; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Connection; public class BatchDemo{ public static void main(String[] args) throws SQLException { Connection con = null; PreparedStatement stmt= null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String url = "jdbc:mysql://localhost/database"; con = DriverManager.getConnection(url, "username", "password"); String sql = "UPDATE authors set author name =? WHERE id = ?"; con.setAutoCommit(false); stmt = con.prepareStatement(sql); stmt.setString(1,"name1"); stmt.addBatch(); stmt.setString(2,"name2"); stmt.addBatch(); stmt.setString(3,"name3"); stmt.addBatch(); stmt.executeBatch(); con.commit(); con.setAutoCommit(true); } catch (Exception e) { con.rollback(); e.printStackTrace(); } finally { if (stmt != null) { stmt.close(); } if (con != null) { con.close(); } } } } |
Leave a Reply