• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
  • Core Java
  • Design Patterns
  • JSP
  • Servlets
  • Building Tools
  • jQuery
  • Spring
  • Hibernate
  • Mongo DB
  • More
    • HTML
    • SCJP
    • AJAX
    • UML
    • Struts
    • J2EE
    • Testing
    • Angular JS

J2EE Reference

  • Home
  • About Us
    • Java Learning Centers
  • Contact Us

Batch Processing in JDBC

July 11, 2012 By j2eereference Leave a Comment

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();
        }
      }
    }
  }

Related Posts

  • UncaughtExceptionHandler in java
  • How to generate and resolve OutOfMemoryError?
  • Difference between Spring’s Singleton scope & Singleton Design pattern
  • How to stop a thread?
  • Interrupting a thread in java
  • What is ThreadLocal in Java ?
  • ArrayList custom Implementation
  • Difference between volatile and synchronized keyword?
  • How to write thread safe code?
  • How to avoid deadlock in java ?

Filed Under: Core Java

Reader Interactions

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

FOLLOW US ONLINE

  • View J2eereference-166104970118637’s profile on Facebook
  • View j2eereference’s profile on Twitter
  • View j2eereference’s profile on LinkedIn

Subscribe by email

Recent posts

  • Java Buzzwords
  • Anonymous Inner Class in Java
  • Network Programming – java.net Package
  • Java Regular Expressions
  • Method Local Inner Class in Java
  • URL Processing in Java
  • Iterator Design Pattern Implementation using Java
  • Strategy Design Pattern Implementation using Java
  • Decorator Design Pattern
  • Adapter Design Pattern Implementation using Java
  • JSF Composite Components
  • JSF UI Components
  • What is JavaServer Faces (JSF)?
  • GOF Design Patterns
  • History and Need for Design Patterns

Footer

Core Java
Design Patterns
JSP
Servlets
HTML
Building Tools
AJAX
SCJP
jQuery
Testing
Spring
UML
Struts
Java Centers
Java Training
Home
About Us
Contact Us
Copyright © j2eereference.com. All right reserved.