JAVA EE: JDBC Batch Processing [PreparedStatement] Demo

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

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

No comments:

Post a Comment

Tutorials