Java JDBC: Batch insert using JDBC Statement Interface

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:

 

Read Also:

 

References:

 

Happy Coding !!
Happy Learning !!

Java JDBC: Creating a table using JDBC PreparedStatement Interface
Java JDBC: Batch update using JDBC Statement Interface