What is CallableStatement Interface in JDBC

CallableStatement Interface:

To call the stored procedures and functions, CallableStatement interface is used.
We can have business logic on the database by the use of stored procedures and functions that will make the performance better because these are precompiled. Suppose you need the get the age of the employee based on the date of birth, you may create a function that receives date as the input and returns age of the employee as the output.

How to get the instance of CallableStatement ?

The prepareCall() method of Connection interface returns the instance of CallableStatement.

Syntax:

public CallableStatement prepareCall( " {  call procedureName(?,?....?) } " );

The example to get the instance of CallableStatement is given below:

CallableStatement stmt=con.prepareCall("{call findMaxNumber(?,?)}");

It calls the procedure INSERT_VALUE that receives 3 arguments.

Example to call the stored procedure using JDBC.

To call the stored procedure, you need to create it in the database.Here we are assuming that stored procedure looks like this.

create or replace procedure "INSERT_VALUE"
 (id IN NUMBER, name IN VARCHAR2,college IN VARCHAR2)
 is
 begin
 insert into student values(id,name,college);
 end;
 /
 The table structure is given below:

create table student( id number(10), name varchar2(300), college varchar2(300) );

In this example, we are going to call the stored procedure INSERT_VALUE that receives id, name and college as the parameter and inserts it  into the table student.

Note that you need to create the student table as well to run this application.

 import java.sql.*;

 public class CallProcedure {
 public static void main(String[] args) throws Exception{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 CallableStatement stmt=con.prepareCall( " {call INSERT_VALUE(?,?,?)} " );
 stmt.setInt(1, 111);
 stmt.setString(2, "Vishwa");
 stmt.setString(3, "KEC");
 stmt.execute();
 System.out.println("successfully called");
   }
 }

 Output: successfully called

 Now check the table in the database, value is inserted in the Student table.

 Example to call the function using JDBC:

In this example, we are calling the sum3 function that receives three input and returns the sum of the given number. Here, we have used the  registerOutParameter method of CallableStatement interface, that registers the output parameter with its corresponding type. It provides information
to the CallableStatement about the type of result being displayed.
The Types class defines many constants such as INTEGER, VARCHAR, FLOAT, DOUBLE, BLOB, CLOB etc. You have to create a function in the database first.

create or replace function sum3
(x1 in number,x2 in number.x3 in number)
return number
is
temp number(10); // it is a temp variable to store final sum result
begin
temp :=x1+x2+x3;
return temp;
end;
 /

Simple program to call the function.

 import java.sql.*;

 public class FuncSum {
 public static void main(String []args) throws Exception{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 CallableStatement stmt=con.prepareCall("{?= call sum3(?,?,?)}");
 stmt.registerOutParameter(1,Types.INTEGER);
 stmt.setInt(2,11);
 stmt.setInt(3,22);
 stmt.setInt(4,20);
 stmt.execute();
 stmt.execute();
 System.out.println("sum of the three numbers="+stmt.getInt(1));
  }
 }

 Output: sum of the three numbers =53