Java JDBC: Batch insert using JDBC PreparedStatement Interface

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


Advantage of Batch Insert:

  • Performance improves as number of database hits becomes lesser comparing with each single SQL query execution
  • In addition to this, this is even faster compared with Statement interface, as PreparedStatement are pre-compiled


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 PreparedStatement Interface

  • As we are ready with required things to connect MySQL database from Java application
  • We will use below methods from PreparedStatement 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
  • Let us code a simple example using PreparedStatement Interface

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

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

public class BatchInsertUsingJDBCPreparedStatement {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		PreparedStatement preparedStatement = null;

		// Step 1: Loading or registering MySQL JDBC driver class
		try {
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MySQL JDBC driver");

		// 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

			// create SQL query to insert PLAYER info
			String sqlInsertQuery = 

			// Step 2.B: Creating JDBC Statement 
		preparedStatement = connection.prepareStatement(sqlInsertQuery);

			// new 1st PLAYER info insert
			preparedStatement.setString(1, "Jacques Kallis");
			preparedStatement.setInt(2, 40);
			preparedStatement.setInt(3, 166);

			// new 2nd PLAYER info insert
			preparedStatement.setString(1, "Saqlain Mushtaq");
			preparedStatement.setInt(2, 39);
			preparedStatement.setInt(3, 49);

			// new 3rd PLAYER info insert
			preparedStatement.setString(1, "Saeed Ajmal");
			preparedStatement.setInt(2, 38);
			preparedStatement.setInt(3, 33);

			// Step 2.C: Executing SQL & retrieve data into ResultSet

			// End of JDBC transaction

      "All new PLAYER info insertion is successful");
		catch(SQLException sqlex){
		finally {

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

					// cleanup resources, once after processing

					// and then finally close connection
			catch (SQLException sqlex) {


All new PLAYER info insertion is successful


4. Download:


Read Also:




Happy Coding !!
Happy Learning !!

Java JDBC: Calling Stored Procedure using JDBC CallableStatement Interface
Java JDBC: Batch update using JDBC PreparedStatement Interface