CallableStatement in Java

CallableStatement in JDBC is an interface present in a java.sql package and it is the child interface of Prepared Statement. Callable Statement is used to execute the Stored Procedure and functions. The driver software vendor is responsible for providing the implementations for Callable Statement interface.

Stored Procedure vs Function:

The difference between stored procedure and function are given below:

The process to call a stored procedure from java application by using Callable Statement:

1) Make sure the stored procedure available in the database:

create procedure addproc(num1 IN number,num2 IN number,num3 OUT number)as

BEGIN

              num3=num1+num2;

END;

2) Create a callable statement with the procedure call:

CallableStatement cst = con.preparecall("{call addproc(? ? ? )}");

3) provides values for every IN parameter by using a corresponding setter method

                            cst.setInt(1,100);

                            cst.setInt(2,100);

4) Register every out parameter with JDBC types:

if the stored procedure has OUT parameter then to hold that output value we should register every OUT parameter by using the following method.

              public void registerOutParameter(int index,int jdbc type)

eg:           cst.registerOutParameter(3,Type.INTEGER)

note: Before executing stored procedure call, all input parameter should set with values and every OUT parameter, we have to register with JDBC types.

5) Execute procedure call:

To execute the procedure, we used the execute method.

                           cst.execute ();

6) Get the result from OUT parameter by using the corresponding getXXX() method:

                   int result = cst.getInt(3);

Example to call a stored procedure using JDBC:

To call the stored procedure, make the sure stored procedure available in the database. Here we are assuming that the stored procedure looks like this:

create procedure getempinfo(id IN number, name OUT varchar, sal OUT number) as

BEGIN 

       

         select ename, esal, into name, sal from employee where eno=id;

END;

In this example, we are going to call the stored procedure getempinfo that receives employee no: as an input and provides corresponding name and salary of the employee.

import java.sql.*;
class EmployeeInfo
{
public static void main(String args[]) throws Exception
{
Connection con = DriverManager.getConnection("jdbc.mysql://localhost:3306/emp", "root", " ");
CallableStatement cst = con.prepareCall("{call getempinfo(? ? ?)}");
cst.setInt(1,100);
cst.registerOutParameter(2,Types.varchar);
cst.registerOutParameter(3,Types.Float);
cst.execute();

System.out.println("Employee name is" , +cst.getString(2));
System.out.println("Employee salary is" , +cst.getFloat(3));
}
}

You may also like:
JDBC Driver in Java with Example
Java MySQL Connectivity with Example
Prepared Statement in Java with Example
How to create and execute a stored procedure in MySQL?