Java JDBC: Calling Stored Procedure using JDBC CallableStatement Interface

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:

4. Download:

Read Also:

References:

Happy Coding !!
Happy Learning !!

BenchResources.Net

Java tutorial for beginners

Java JDBC: Calling Stored Procedure using JDBC CallableStatement Interface

1. Pre-requisite:

2. Database parameters:

3. Stored Procedure

3.1 Advantages of using stored procedure:

3.2 Calling Stored Procedure using JDBC CallableStatement Interface

InvokeStoredProcedureUsingJDBCCallableStatement.java

3.3 Database Screen Capture:

4. Download:

1. Pre-requisite:

2. Database parameters:

3. Stored Procedure

3.1 Advantages of using stored procedure:

3.2 Calling Stored Procedure using JDBC CallableStatement Interface

InvokeStoredProcedureUsingJDBCCallableStatement.java

3.3 Database Screen Capture:

4. Download:

Related posts: