Calling database custom Function Using CallableStatement

In this post you will learn how to call Stored Function Using JDBC CallableStatement.
Here i am using MYSQL database but you are feel free to choose any database.
Jdbc code would be same for all database only little changes may be in how to create SQL custom function from once database to another

Project structure in eclipse:

DB Script to create SQL function in MYSQL database schema(jdbcdb)

DELIMITER $$

CREATE FUNCTION `jdbcdb`.`getAvg` (n1 INT,n2 INT)
RETURNS INTEGER
BEGIN
DECLARE avgNum INTEGER;
SET avgNum =(n1+n2)/2;
RETURN avgNum;
END

DELIMITER $$

CREATE FUNCTION `jdbcdb`.`getAvg` (n1 INT,n2 INT)

RETURNS INTEGER

BEGIN

DECLARE avgNum INTEGER;

SET avgNum =(n1+n2)/2;

RETURN avgNum;

END

DBUtil.java class which is responsible to connect with MySQL database.
this util class uses getConnection() method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException

This method takes three parameters database URL,username and password.
Here database username =”root”,password = “root” what password had supplied during MYSQL database installation time and finally database URL =“jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.

package com.infotech.util;

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

public class DBUtil {
	private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
	private static final String DB_USERNAME="root";
	private static final String DB_PASSWORD="root";
	private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";
	
	private static Connection connection = null;
	static{
		try {
			Class.forName(DB_DRIVER_CLASS);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";

private static final String DB_USERNAME="root";

private static final String DB_PASSWORD="root";

private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";

private static Connection connection = null;

static{

try {

Class.forName(DB_DRIVER_CLASS);

connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

public static Connection getConnection(){

return connection;

}

Client program which calls MySQL above created function and returns value

package com.infotech.client;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import java.util.Scanner;

import com.infotech.util.DBUtil;

public class ClientTest {

	public static void main(String[] args) {
		
		String SQL = "{?=CALL getAvg(?,?)}";
		try(Connection connection = DBUtil.getConnection();CallableStatement cs = 
				connection.prepareCall(SQL);Scanner scanner = new Scanner(System.in)) {
			System.out.println("Enter first Number::");
			int n1 = scanner.nextInt();
			System.out.println("Enter Second Number::");
			int n2 = scanner.nextInt();
			
			cs.registerOutParameter(1, Types.INTEGER);
			
			cs.setInt(2, n1);
			cs.setInt(3, n2);
			
			cs.execute();
			
			System.out.println(cs.getInt(1));
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

package com.infotech.client;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.Types;

import java.util.Scanner;

import com.infotech.util.DBUtil;

public class ClientTest {

public static void main(String[] args) {

String SQL = "{?=CALL getAvg(?,?)}";

try(Connection connection = DBUtil.getConnection();CallableStatement cs =

connection.prepareCall(SQL);Scanner scanner = new Scanner(System.in)) {

System.out.println("Enter first Number::");

int n1 = scanner.nextInt();

System.out.println("Enter Second Number::");

int n2 = scanner.nextInt();

cs.registerOutParameter(1, Types.INTEGER);

cs.setInt(2, n1);

cs.setInt(3, n2);

cs.execute();

System.out.println(cs.getInt(1));

} catch (Exception e) {

e.printStackTrace();

}

If you run this client program this will ask you to enter two numbers for which you want to find average.

Enter first Number::
10
Enter Second Number::
20
Average:15

Enter first Number::

Enter Second Number::

Average:15

That’s all for topic Calling database custom Function Using CallableStatement

You May Also Like:

Calling StoredProcedure Using CallableStatement
Get ResultSet By Calling StoredProcedure Using CallableStatement
JDBC batch update with Statement
JDBC batch update with PreparedStatement
JDBC batch update with CallableStatement
How to retrieve data from JDBC resultset in java
How to update a Row in a Database Table Using an updatable ResultSet
How to insert a Row in a Database Table Using an updatable ResultSet
JDBC ResultSet navigation methods example in java

If you have any feedback or suggestion please feel free to drop in below comment box.

Leave a Reply Cancel reply