Java – Calling Stored Function using JDBC CallableStatement interface

June 21, 2016 SJ JDBC 0

In this article, we will use JDBC API to call stored function 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 function 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 Function :

  • This is similar to stored procedure with one difference
  • That stored function always returns a value
  • Whereas for stored procedure we must register OUT parameter

3.1 Advantages of using stored function :

  • 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 Function: sfCountPlayerRecordinDB 
use PLAYER_INFO; 
delimiter 
/ 
CREATE FUNCTION sfCountPlayerRecordinDB() RETURNS INT     
BEGIN     
declare total int;     
select count(*) into total from player;     
return total;     
END; 
/ 
delimiter ;

3.2 Calling Stored Function using JDBC CallableStatement Interface :

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

InvokeStoredFunctionUsingJDBCCallableStatement.java

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

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

public class InvokeStoredFunctionUsingJDBCCallableStatement {

	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 sfCountPlayerRecordinDB()}");

			// register OUT parameter
			callableStatement.registerOutParameter(1, Types.INTEGER);

			// Step 2.C: Executing CallableStatement
			callableStatement.execute();

			// get count and print in console
			int count = callableStatement.getInt(1);
			System.out.println(
                 "Player count in PLAYER_INFO database is = " + count);
		}
		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:

Player count in PLAYER_INFO database is = 14

4. Download :

Related Articles :

References :

Happy Coding !!
Happy Learning !!

Java – Calling Stored Procedure using JDBC CallableStatement interface with Batch execution
Java – Calling Stored Procedure using JDBC CallableStatement interface

Related posts:

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