JAVA EE: Batch Processing

Showing posts with label Batch Processing. Show all posts

Friday, 1 August 2014

JDBC Batch Processing [PreparedStatement] Demo

Enjoyed this tutorial?

Subscribe to Ram N Java for regular, beginner-friendly Java & database tutorials!

What is Batch Processing in JDBC?

When inserting, updating, or deleting large amounts of records, sending queries to the database server one by one creates high network latency and hurts application performance. Batch processing allows you to bundle multiple SQL operations together and transmit them across the network in a single call.

Using batch processing with PreparedStatement is especially powerful because the database compiles the SQL query once and efficiently reuses the execution plan for all rows in the batch.

Key Methods for PreparedStatement Batching

addBatch(): Appends the current set of parameterized values into the batch buffer.
executeBatch(): Sends all gathered commands to the database engine simultaneously and returns an integer array (int[]) showing affected rows per operation.
clearBatch(): Clears any queued commands from the batch buffer if needed.

Step-by-Step Code Walkthrough

Here is how you can set up and execute batch updates using a parameterized query:

String sql = "INSERT INTO city (ID, Name, CountryCode, District, Population) VALUES (?, ?, ?, ?, ?)";
PreparedStatement ps = connection.prepareStatement(sql);

// Row 1
ps.setInt(1, 4080);
ps.setString(2, "Springfield");
ps.setString(3, "USA");
ps.setString(4, "Illinois");
ps.setInt(5, 115000);
ps.addBatch();

// Row 2
ps.setInt(1, 4081);
ps.setString(2, "Greenville");
ps.setString(3, "USA");
ps.setString(4, "North Carolina");
ps.setInt(5, 93000);
ps.addBatch();

// Execute all batched commands
int[] count = ps.executeBatch();
System.out.println("Total queries executed: " + count.length);

Managing Transactions with Auto-Commit

For optimal performance and transactional safety when running batches:

Disable auto-commit by calling connection.setAutoCommit(false); before adding records.
Execute the batch with executeBatch().
Commit all operations together using connection.commit();.
Roll back the batch using connection.rollback(); inside your catch block if an exception occurs.

Recommended Related Tutorials

Check out these related tutorials to master JDBC query execution and database operations:

Click here to watch in Youtube : https://www.youtube.com/watch?v=ATX3s02mb00

Click the below Image to Enlarge

JDBCBatchProcessDemo Project Dir Structure

JDBC Batch Processing [PreparedStatement] Demo

JDBCBatchProcess.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCBatchProcess
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL      = "jdbc:mysql://localhost:3306/world";

    // Database credentials
    static final String USERNAME    = "root";
    static final String PASSWORD    = "root";

    public static void main( String[] args )
    {
        JDBCBatchProcess JDBCBatchProcess = new JDBCBatchProcess();
        JDBCBatchProcess.addCityInformation();
    }

    private void addCityInformation()
    {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try
        {
            /*
             * Register the JDBC driver in DriverManager
             */

            Class.forName(JDBC_DRIVER);

            /*
             * Establish connection to the Database using DriverManager
             */

            connection = DriverManager
                    .getConnection(DB_URL, USERNAME, PASSWORD);

            /*
             * Set auto-commit to false
             */
            connection.setAutoCommit(false);

            String sql = "insert into city values(?,?,?,?,?)";

            /*
             * Execute the query
             */
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, 5000);
            preparedStatement.setString(2, "Bangalore");
            preparedStatement.setString(3, "IND");
            preparedStatement.setString(4, "Karnataka");
            preparedStatement.setInt(5, 20000);

            /*
             * Add sql to the batch
             */

            preparedStatement.addBatch();

            preparedStatement.setInt(1, 5001);
            preparedStatement.setString(2, "Channai");
            preparedStatement.setString(3, "IND");
            preparedStatement.setString(4, "TamilNadu");
            preparedStatement.setInt(5, 200000);

            /*
             * Add sql to the batch
             */

            preparedStatement.addBatch();

            preparedStatement.setInt(1, 5002);
            preparedStatement.setString(2, "Thiruvanathapuram");
            preparedStatement.setString(3, "IND");
            preparedStatement.setString(4, "Kerala");
            preparedStatement.setInt(5, 800000);

            /*
             * Add sql to the batch
             */

            preparedStatement.addBatch();

            int result[] = preparedStatement.executeBatch();

            /*
             * Explicitly commit statements to apply changes
             */
            connection.commit();

            for( int i = 0; i < result.length; i++ )
            {
                System.out.println(result[i]);
            }

        }
        catch( SQLException se )
        {
            /*
             * Handle errors for JDBC
             */
            try
            {
                connection.rollback();
            }
            catch( SQLException e )
            {
                e.printStackTrace();
            }
            se.printStackTrace();
        }
        catch( ClassNotFoundException e )
        {
            /*
             * Handle errors for Class.forName
             */
            e.printStackTrace();
        }
        catch( Exception e )
        {
            try
            {
                connection.rollback();
            }
            catch( SQLException e1 )
            {

                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if( preparedStatement != null )
                {
                    preparedStatement.close();
                }
            }
            catch( SQLException sqlException )
            {
                sqlException.printStackTrace();
            }
            try
            {
                if( connection != null )
                {
                    connection.close();
                }
            }
            catch( SQLException sqlException )
            {
                sqlException.printStackTrace();
            }
        }

    }
}

Output

1
1
1

Environment Used

JDK version : 1.7.0_51
Mysql Server version : 5.6.19

To Download JDBCBatchProcessDemo Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCBatchProcessDemo_PreparedStatement.zip?attredirects=0&d=1

See also:

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Wednesday, 30 July 2014

JDBC Batch Processing [Statement] Demo

Enjoyed this tutorial?

Subscribe to Ram N Java for regular, beginner-friendly Java & database tutorials!

▶ Subscribe Now

What is Batch Processing with Statement?

In standard database programming, executing individual SQL statements one after another leads to high network traffic and significant communication overhead. JDBC solves this with Batch Processing, allowing you to bundle multiple heterogeneous SQL statements (such as INSERT, UPDATE, or DELETE) together and send them to the database in a single round-trip using a standard Statement object.

Core Methods of the Statement Interface

addBatch(String sql): Adds a complete, standalone SQL statement to the batch execution queue.
executeBatch(): Sends all bundled SQL commands to the database engine at once and returns an integer array (int[]) showing the update count for each executed query.
clearBatch(): Empties the current batch buffer if you need to discard queued statements.

Step-by-Step Code Walkthrough

Here is how you can create and execute a batch consisting of diverse SQL operations:

Statement statement = connection.createStatement();

// Queue multiple DML statements into the batch
statement.addBatch("INSERT INTO city VALUES (4082, 'San Jose', 'USA', 'California', 1020000)");
statement.addBatch("INSERT INTO city VALUES (4083, 'Austin', 'USA', 'Texas', 960000)");
statement.addBatch("UPDATE city SET Population = 1030000 WHERE ID = 4082");

// Execute the entire batch in a single network call
int[] results = statement.executeBatch();

for (int count : results) {
    System.out.println("Rows affected: " + count);
}

Statement vs PreparedStatement for Batching

While PreparedStatement is optimized for executing the same parameterized query repeatedly with different values, standard Statement batching is great when you need to send different, non-parameterized SQL commands together in a unified network batch.

Recommended Related Tutorials

Expand your JDBC knowledge with these essential tutorials:

Click here to watch in Youtube : https://www.youtube.com/watch?v=rasVi3-BnPE

Click the below Image to Enlarge

JDBCBatchProcessDemo Project Dir Structure

JDBC Batch Processing [Statement] Demo

JDBCBatchProcess.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCBatchProcess
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/world";

    // Database credentials
    static final String USERNAME = "root";
    static final String PASSWORD = "root";

    public static void main(String[] args)
    {
        JDBCBatchProcess JDBCBatchProcess = new JDBCBatchProcess();
        JDBCBatchProcess.addCityInformation();
    }

    private void addCityInformation()
    {
        Connection connection = null;
        Statement statement = null;
        try
        {
            /*
             * Register the JDBC driver in DriverManager
             */

            Class.forName(JDBC_DRIVER);

            /*
             * Establish connection to the Database using DriverManager
             */

            connection = DriverManager
                    .getConnection(DB_URL, USERNAME, PASSWORD);

            /*
             * Set auto-commit to false
             */
            connection.setAutoCommit(false);

            String sql1 = "insert into city values(5000,'Bangalore','IND','Karnataka',400000)";
            String sql2 = "insert into city values(5001,'Channai','IND','TamilNadu',200000)";
            String sql3 = "insert into city values(5002,'Thiruvanathapuram','IND','Kerala',800000)";

            /*
             * Execute the query
             */
            statement = connection.createStatement();

            /*
             * Add sql's to the batch
             */
            statement.addBatch(sql1);
            statement.addBatch(sql2);
            statement.addBatch(sql3);

            int result[] = statement.executeBatch();

            /*
             * Explicitly commit statements to apply changes
             */
            connection.commit();

            for (int i = 0; i < result.length; i++)
            {
                System.out.println(result[i]);
            }

        }
        catch (SQLException se)
        {
            /*
             * Handle errors for JDBC
             */
            try
            {
                connection.rollback();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
            se.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            /*
             * Handle errors for Class.forName
             */
            e.printStackTrace();
        }
        catch (Exception e)
        {
            try
            {
                connection.rollback();
            }
            catch (SQLException e1)
            {

                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if (statement != null)
                {
                    statement.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
            try
            {
                if (connection != null)
                {
                    connection.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
        }

    }
}

Output

1
1
1

Environment Used

JDK version : 1.7.0_51
Mysql Server version : 5.6.19

To Download JDBCBatchProcessDemo_Statement_App Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCBatchProcessDemo_Statement_App.zip?attredirects=0&d=1

See also:

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

JDBC Batch Processing - Introduction

Want to Master Java & Database Programming?

Subscribe to our YouTube channel for clear, step-by-step developer tutorials and interview tips!

SUBSCRIBE NOW

The Problem with Individual Query Execution

In real-world applications, your Java program typically runs on an application server, while the relational database resides on a separate database server across the network.

When you execute multiple SQL commands—such as 100 insert or update statements—the traditional approach is to send each query individually over the network, wait for execution, receive the response, and then repeat the process.

Sending queries one by one introduces heavy network communication overhead, causes database round-trip delays, and drastically decreases the overall performance of your enterprise application.

What is JDBC Batch Processing?

Batch Processing in JDBC provides an elegant and high-performance solution to this problem. Instead of dispatching queries individually, it allows you to bundle multiple related SQL statements together into a single "batch" and transmit the entire collection to the database server in a single network call.

By executing queries together in a batch:

Reduces Network Traffic: Only one round trip over the network is required for the entire collection of statements.
Boosts Execution Speed: Database engines optimize execution when processing grouped write operations.
Improves Application Throughput: Database connection threads spend less time waiting on client transmissions.

Core Batch Processing Methods in JDBC

Both the Statement and PreparedStatement interfaces provide built-in methods dedicated to managing batches:

addBatch(String sql): Adds an individual SQL statement to the active batch list (used with Statement). When using PreparedStatement, call addBatch() after binding your parameters.
executeBatch(): Sends the collected batch of commands to the database server for execution. It returns an int[] array where each element contains the update count for the corresponding command.
clearBatch(): Clears all queued commands from the batch buffer, releasing resources and resetting the statement for future operations.

When to Use Batch Processing

Batch processing is the standard best practice whenever you are performing:

Bulk data imports or migrations (such as loading CSV or Excel records into tables).
Mass data updates or deletions across large database tables.
Transactional business operations requiring atomic execution of multiple changes.

Friday, 1 August 2014

JDBC Batch Processing [PreparedStatement] Demo

What is Batch Processing in JDBC?

Key Methods for PreparedStatement Batching

Step-by-Step Code Walkthrough

Managing Transactions with Auto-Commit

Recommended Related Tutorials

Wednesday, 30 July 2014

JDBC Batch Processing [Statement] Demo

What is Batch Processing with Statement?

Core Methods of the Statement Interface

Step-by-Step Code Walkthrough

Statement vs PreparedStatement for Batching

Recommended Related Tutorials

JDBC Batch Processing - Introduction

Want to Master Java & Database Programming?

The Problem with Individual Query Execution

What is JDBC Batch Processing?

Core Batch Processing Methods in JDBC

When to Use Batch Processing

Related Tutorials You Might Like

Tutorials