Java JDBC: Calling Stored Procedure using CallableStatement with Batch execution

In this article, we will use JDBC API to prepare batch to execute database stored procedure in MySQL database from Java application (i.e.; using CallableStatement Interface)

 

Q) How to prepare and execute batch using JDBC API ?

  • To prepare batch: use addBatch(); method of CallableStatement which is inherited from java.sql.PreparedStatement
    void addBatch() throws SQLException
    
  • To execute batch: use executeBatch(); method of CallableStatement which is inherited from java.sql.Statement
    int[] executeBatch() throws SQLException
    

 

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 invoke or call database stored procedure 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. Stored Procedure

  • Set of DML statements like insert, update and delete with business logic completely on database side
  • With performance boost as these statements are pre-compiled

 

3.2 Advantages of using stored procedure

  • Increases performance, as these are pre-compiled
  • Faster execution
  • Business logic completely inside database with extra-level of security
  • If there are any changes in business logic, all changes need to be done at database end and not making Java code dirty
  • Stored Procedure: spInsertNewPlayerRecord
    use PLAYER_INFO;
    
    delimiter /
    
    CREATE PROCEDURE spInsertNewPlayerRecord(  
    IN name VARCHAR(50), IN age INT, IN matches INT)
    BEGIN
    INSERT INTO PLAYER (NAME, AGE, MATCHES) VALUES (name, age, matches);
    END;
    /
    
    delimiter ;
    

 

3.3 Calling Stored Procedure using CallableStatement with Batch execution

  • As we are ready with required things to connect MySQL database from Java application
  • Syntax: To invoke database stored procedure use below syntax,
    // set values for new player record - 1
    callableStatement.setString(1, "Herschelle Gibbs");
    callableStatement.setInt(2, 42);
    callableStatement.setInt(3, 90);
    
    // add to batch - 1
    callableStatement.addBatch();
    
  • Let us code simple example using CallableStatement Interface

BatchStoredProcedureUsingJDBCCallableStatement.java

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

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

public class BatchStoredProcedureUsingJDBCCallableStatement {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		CallableStatement callableStatement = 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"); 

			// Step 2.B: Creating JDBC CallableStatement 
			callableStatement = connection
					.prepareCall("{call spInsertNewPlayerRecord(?, ?, ?)}");

			// set values for new player record - 1
			callableStatement.setString(1, "Herschelle Gibbs");
			callableStatement.setInt(2, 42);
			callableStatement.setInt(3, 90);

			// add to batch - 1
			callableStatement.addBatch();

			// set values for new player record - 2
			callableStatement.setString(1, "Mervyn Dillon");
			callableStatement.setInt(2, 41);
			callableStatement.setInt(3, 38);

			// add to batch - 2
			callableStatement.addBatch();

			// set values for new player record - 3
			callableStatement.setString(1, "Nathan Astle");
			callableStatement.setInt(2, 44);
			callableStatement.setInt(3, 81);

			// add to batch - 3
			callableStatement.addBatch();

			// Step 2.C: Executing CallableStatement
			int[] insertCounts = callableStatement.executeBatch();

			// retrieving result and checking
			for(int result: insertCounts){
				System.out.println(result 
						+ " indicates insertion is successful");
			}
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {
			// Step 3: Closing database connection
			try {
				if(null != connection) {
					// cleanup resources, once after processing
					callableStatement.close();

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

Output:

1 indicates insertion is successful
1 indicates insertion is successful
1 indicates insertion is successful

 

3.4 Database Screen Capture:

2-Calling-Stored-Procedure-using-JDBC-CallableStatement-with-Batch-execution

 

4. Download:

 

Read Also:

 

References:

 

Happy Coding !!
Happy Learning !!

Java JDBC: Transaction handling using Statement Interface
Java JDBC: Calling Stored Function using JDBC CallableStatement Interface