JAVA EE: Statement

Showing posts with label Statement. Show all posts

Wednesday, 30 July 2014

JDBC Batch Processing [Statement] Demo

Enjoyed this tutorial?

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

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

Sunday, 13 July 2014

JDBC Statement

Enjoying Our Java Tutorials?

Subscribe to our YouTube channel for clear, step-by-step Java and database programming guides!

SUBSCRIBE NOW

What is a JDBC Statement?

In Java Database Connectivity (JDBC), the Statement interface is the fundamental bridge used to send SQL commands to your relational database. Once your Java program establishes a connection using DriverManager, you can create a Statement object directly from that connection instance.

A standard Statement object is primarily designed for executing general, static SQL queries where parameter values do not change dynamically at runtime.

How to Create a Statement Object

Creating a statement is straightforward. You call the createStatement() method on your active Connection object:

    Connection connection = DriverManager.getConnection(url, user, password);

    Statement stmt = connection.createStatement();

Key Methods in the Statement Interface

The Statement interface provides specialized methods depending on the type of SQL command you want to run:

executeQuery(String sql): Used for SELECT queries. It executes the query and returns a tabular ResultSet containing the matched data records.
executeUpdate(String sql): Used for DML statements like INSERT, UPDATE, DELETE, or DDL statements like CREATE TABLE and DROP TABLE. It returns an integer representing the number of rows affected.
execute(String sql): A universal method used when a query might return multiple result sets or update counts, typically in dynamic or complex operations.

When Should You Use Statement?

The basic Statement interface is best suited for:

One-time static SQL statements (such as creating tables, altering tables, or dropping databases).
Simple test scripts or prototype applications where queries do not accept user input directly.
Batch queries containing fixed, non-repeating SQL statements.

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

Sunday, 13 July 2014

JDBC Statement

Enjoying Our Java Tutorials?

What is a JDBC Statement?

How to Create a Statement Object

Key Methods in the Statement Interface

When Should You Use Statement?

Related Tutorials You Might Like

Tutorials