Java – Calling Stored Procedure using JDBC CallableStatement interface

June 20, 2016 SJ JDBC 0

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

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.1 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.2 Calling Stored Procedure using JDBC CallableStatement Interface :

  • As we are ready with required things to connect MySQL database from Java application
  • Syntax: To invoke database stored procedure use below syntax 
{call spInsertNewPlayerRecord(?, ?, ?)}
  • Let us code a simple example using CallableStatement Interface

InvokeStoredProcedureUsingJDBCCallableStatement.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 InvokeStoredProcedureUsingJDBCCallableStatement {

	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
			callableStatement.setString(1, "Michael Vaughan");
			callableStatement.setInt(2, 41);
			callableStatement.setInt(3, 82);

			// Step 2.C: Executing CallableStatement
			int result = callableStatement.executeUpdate();

			System.out.println(result
                 + " indicates stored procedure execution 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 stored procedure execution is successful

3.3 Database Screen Capture :

1-Calling-Stored-Procedure-using-JDBC-CallableStatement-Interface

4. Download :

Related Articles :

References :

Happy Coding !!
Happy Learning !!

Java – Calling Stored Function using JDBC CallableStatement interface
Java – Batch insert using JDBC PreparedStatement interface

Related posts:

  1. Java – Calling Stored Procedure using JDBC CallableStatement interface with Batch execution
  2. Java – Inserting a record using JDBC Statement interface
  3. Java – Batch insert using JDBC Statement interface
  4. Java – Batch insert using JDBC PreparedStatement interface