In this article, we will use JDBC API to add couple of DML statements to batch and finally executing/inserting batch of statements in MySQL database from Java application i.e.; using Statement Interface

Advantage of Batch Insert :

Performance improves as number of database hits becomes lesser comparing with each SQL query execution

1. Pre-requisite:

Java JDK 1.8.0_77
MySQL database 5.5.16
Eclipse Luna IDE 4.4.0
mysql-connector-java-5.1.38.jar file

2. Database parameters :

Let us move on and code an example to connect MySQL database from Java application to execute/insert batch of statements using JDBC API. But before that, we will list down required things to connect database

database server IP or address (localhost)
sever port (3306)
database name (PLAYER_INFO)
username (root)
password (root@123)

Note: All bold are database values to connect MySQL database

3. Batch insert using JDBC Statement Interface :

As we are ready with required things to connect MySQL database from Java application, let us code a simple example using Statement Interface
We will use below methods from Statement Interface to add DML statements to batch and finally executing/inserting batch of statements (batch processing)
1. addBatch(String sqlQuery);
2. executeBatch();
Also, we will enclose all DML statement execution within JDBC transaction to maintain consistency and integrity with database

BatchInsertUsingJDBCStatement.java

package in.bench.resources.mysql.db.example;

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

public class BatchInsertUsingJDBCStatement {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		Statement statement = null;

		// Step 1: Loading or registering MySQL JDBC driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MySQL JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and get connection using DriverManager
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/PLAYER_INFO",
					"root",
					"root@123"); 

			// Start of JDBC transaction
			connection.setAutoCommit(false);

			// Step 2.B: Creating JDBC Statement
			statement = connection.createStatement();

			// create SQL query to insert PLAYER info
			String sqlInsertQuery1 = "INSERT INTO PLAYER(NAME, AGE, MATCHES)"
					+ " VALUES('Jacques Kallis', '40', '160')";

			// add to batch for execution
			statement.addBatch(sqlInsertQuery1);

			// again create SQL query to insert another PLAYER info
			String sqlInsertQuery2 = "INSERT INTO PLAYER(NAME, AGE, MATCHES)"
					+ " VALUES('AB de Villiers', '32', '106')";

			// add to batch for execution
			statement.addBatch(sqlInsertQuery2);

			// again create SQL query to insert another PLAYER info
			String sqlInsertQuery3 = "INSERT INTO PLAYER(NAME, AGE, MATCHES)"
					+ " VALUES('Hashim Amla', '33', '92')";

			// add to batch for execution
			statement.addBatch(sqlInsertQuery3);

			// Step 2.C: Executing SQL
			statement.executeBatch();

			// End of JDBC transaction
			connection.commit();

			System.out.println("All new PLAYER info"
					+ " insertion is successful");
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {
			// Step 3: Closing database connection
			try {
				if(null != connection) {
					// cleanup resources, once after processing
					statement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

All new PLAYER info insertion is successful

Note: Actually, we can very well mix and use both update and insert queries together for batch processing or batch execution

3.1 Batch Update and Insert :

BatchUpdateAndInsertUsingJDBCStatement.java

package in.bench.resources.mysql.db.example;

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

public class BatchUpdateAndInsertUsingJDBCStatement {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		Statement statement = null;

		// Step 1: Loading or registering MySQL JDBC driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MySQL JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and get connection using DriverManager
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/PLAYER_INFO",
					"root",
					"root@123"); 

			// Start of JDBC transaction
			connection.setAutoCommit(false);

			// Step 2.B: Creating JDBC Statement
			statement = connection.createStatement();

			// update PLAYER info
			String sqlUpdateQuery = "UPDATE PLAYER"
					+ " SET AGE = 44"
					+ " WHERE PLAYER_ID = 1";

			// add to batch for execution
			statement.addBatch(sqlUpdateQuery);

			// insert new PLAYER info - 1
			String sqlInsertQuery1 = "INSERT INTO PLAYER(NAME, AGE, MATCHES)"
					+ " VALUES('Adam Gilchrist', '44', '96')";

			// add to batch for execution
			statement.addBatch(sqlInsertQuery1);

			// insert new PLAYER info - 2
			String sqlInsertQuery2 = "INSERT INTO PLAYER(NAME, AGE, MATCHES)"
					+ " VALUES('Dion Nash', '44', '32')";

			// add to batch for execution
			statement.addBatch(sqlInsertQuery2);

			// Step 2.C: Executing SQL
			statement.executeBatch();

			// End of JDBC transaction
			connection.commit();

			System.out.println("All new PLAYER info"
					+ " updation/insertion is successful");
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {

			// Step 3: Closing database connection
			try {
				if(null != connection) {

					// cleanup resources, once after processing
					statement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

All PLAYER info updation/insertion is successful

4. Download :

References :

Happy Coding !!
Happy Learning !!

BenchResources.Net

Java tutorial for beginners

Java – Batch insert using JDBC Statement interface

Advantage of Batch Insert :

1. Pre-requisite:

2. Database parameters :

3. Batch insert using JDBC Statement Interface :

BatchInsertUsingJDBCStatement.java

Output:

3.1 Batch Update and Insert :

BatchUpdateAndInsertUsingJDBCStatement.java

Output:

4. Download :

Related Articles :

References :

Related

Advantage of Batch Insert :

1. Pre-requisite:

2. Database parameters :

3. Batch insert using JDBC Statement Interface :

BatchInsertUsingJDBCStatement.java

Output:

3.1 Batch Update and Insert :

BatchUpdateAndInsertUsingJDBCStatement.java

Output:

4. Download :

Related Articles :

References :

Related

Related posts: