Callable Statement in Java

The Callable statement in Java is used to call the functions and Stored procedures.

Example:

If we want to know about the age of a person based on their date of birth, we can create a function that can get the age by giving date of birth as input.

Stored Procedure:

The Stored Procedure is used for the logic purpose. It can give both input and output. We can be able to call functions from procedure. The Exception handing can be done in the Stored Procedure. It may return 0 or many values.

Function:

It is used to perform the Calculation. It only works with input parameters. We cannot be able to call functions. The Exception handling cannot be done in Functions. It may return only one value.

Creating a Callable Statement

The prepareCall() method is used to create an object for the Callable statement , this method is present in the Connection Interface. This method takes the String as query input and call Stored procedure and return Callable Statement. The CallableStatements can have both input and output parameters . To give the inputs, we ca use the methods which are there in the CallableStatement Interface.

Example:

CallableStatement Cs = con.prepareCall(“{call myProcedure(?,?)}”);

Input parameters:

We can be able to give the input to the Callable Statement by using the “set” methods. There are two arguments while giving the inputs to the Callable Statements i.e., First argument represent the index as Integer and the Second argument represents the content in String or integer or float etc.

Syntax:

ct.setString(1, “Kotte”);
ct.setString(2, 10000); 

Execution of Callable Statement:

The Execution of the Callable statement is done by the execute() method.

Syntax:

ct.execute();

Example:

For Calling a procedure in Callable Statement, we need to create the procedure in the database and create a table like STUDENT. The procedure is written below:

Create procedure “PRO” (IN Id INT, IN Name VARCHAR(10))

BEGIN

Insert into STUDENT values (Id,Name);

END;

//Example program for Creating new records into Student table using Callable Statement
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Cs
{
public static void main(String args[])
{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
String url =“jdbc:mysql://localhost//DB”;
Connection con = DriverManager.getConnection(url,”root”,”password”);
CallableStatement ct = con.prepareCall(“{call PRO}”);
ct.setInt(1, 101);
ct.setString(2, “Kotte”);
ct.setInt(1, 102);
ct.setString(2, “Sai”);


ct.setInt(1, 103);
ct.setString(2, “Karan”);


ct.setInt(1, 104);
ct.setString(2, “Bablu”);


ct.setInt(1, 105);
ct.setString(2, “Krishna”);

//execution of Callable statement.
ct.execute();
System.out.println(“Rows are Inserted”);


} 
}

Output:

Rows Inserted

To check whether the rows are inserted in the database, then we have to use the query like “SELECT * FROM STUDENT;” in the SQL.

mysql>SELECT * FROM STUDENT;

ID	Name
101	Kotte
102	Sai
103	Karan
104	Bablu
105	Krishna

The above table is the result of the query “SELECT * FROM STUDENT;”. It has the elements which are inserted using the Callable Statement.

Summary:

The Callable Statements are used to call the functions and Stored procedures.To insert the records in the table in database , we have to create a procedure in the database according to the table then we can able to insert the records into the table using the Callable Statement.