Callablestatement example 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 the Callable Statement interface. This tutorial explains in detail how to create, set input parameters as well as execute a CallableStatement in Java.
- Stored Procedure vs Function
- Creating a CallableStatement
- Setting Values to the Input Parameters
- Executing the Callable Statement
- How to Call Stored Procedure using CallableStatement?
- Types of Parameters in CallableStatement
- Example to Call a Stored Procedure using JDBC
Stored Procedure vs Function
Callablestatement example java: The difference between stored procedure and function is given below:
Creating a CallableStatement
Callable statement in java: You can create an object of the CallableStatement (interface) by utilizing the prepareCall() Method of Connection Interface. This particular method accepts a string variable denoting a query to call the stored procedure and returns a CallableStatement Object.
Callable Statements can have different types of parameters such as IN, OUT, INOUT. In order to pass these input parameters to the procedure call you can use place holder and set values to these by using the setter methods (setInt(), setString(), setFloat()) given by the CallableStatement Interface.
Setting Values to the Input Parameters
Callable statements in java: You can set values to the input parameters of the procedure call by taking the help of setter methods. They accept two arguments. One is an integer value denoting the placement index of the input parameter and the other is int, string, float, etc. to represent the value you need to pass as an input parameter to the procedure.
Note: Other than Index you can even pass the name of the parameter in String format.
Do Read:
Executing the Callable Statement
After you create a CallableStatement Object you can execute it using one of the execute() methods.
Types of Parameters in CallableStatement
| Parameter | Description |
|---|---|
| IN | The parameter in which the value is unknown when the SQL Statement is created. Bind Values to IN Parameters using the setXXX() Methods. |
| OUT | This is the parameter whose value is supplied by the SQL Statement it returns. You can retrieve values from the OUTParemeter by taking the help of getXXX() methods. |
| INOUT | This parameter provides both input and output values. Bind variables using the setXXX() methods and retrieve values using the getXXX() methods. |
How to Call Stored Procedure using CallableStatement?
1. Make sure the stored procedure available in the database:
create procedure add proc(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 the stored procedure call, all input parameters should set with values, and for 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 the 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));
}
}