Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is CallableStatement in JDBC?
The CallableStatement interface provides methods to execute the stored procedures. Since the JDBC API provides a stored procedure SQL escape syntax, you can call stored procedures of all RDBMS in single standard way.
Creating a CallableStatement
You can create an object of the CallableStatement (interface) using the prepareCall() method of the Connection interface. This method accepts a string variable representing a query to call the stored procedure and returns a CallableStatement object.
A Callable statement can have input parameters, output parameters or both. To pass input parameters to the procedure call you can use place holder and set values to these using the setter methods (setInt(), setString(), setFloat()) provided by the CallableStatement interface.
Suppose you have a procedure name myProcedure in the database you can prepare a callable statement as:
//Preparing a CallableStatement
CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");Setting values to the input parameters
You can set values to the input parameters of the procedure call using the setter methods.
These accepts two arguments, one is an integer value representing the placement index of the input parameter and, the other is a int or, String or, float etc… representing the value you need to pass as input parameter to the procedure.
Note: Instead of index you can also pass the name of the parameter in String format.
cstmt.setString(1, "Raghav"); cstmt.setInt(2, 3000); cstmt.setString(3, "Hyderabad");
Executing the Callable Statement
Once you have created the CallableStatement object you can execute it using one of the execute() method.
cstmt.execute();
Example
Suppose we have a table named Employee in the MySQL database with the following data:
+---------+--------+----------------+ | Name | Salary | Location | +---------+--------+----------------+ | Amit | 30000 | Hyderabad | | Kalyan | 40000 | Vishakhapatnam | | Renuka | 50000 | Delhi | | Archana | 15000 | Mumbai | +---------+--------+----------------+
And we have created a procedure named myProcedure to insert values in to this table as shown below:
Create procedure myProcedure (IN name VARCHAR(30), IN sal INT, IN loc VARCHAR(45)) -> BEGIN -> INSERT INTO Employee(Name, Salary, Location) VALUES (name, sal, loc); -> END //
Following is a JDBC example which inserts new records into the Employee table by calling the above created procedure, using a callable statement.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class CallableStatementExample {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/testdb";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Preparing a CallableStateement
CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");
cstmt.setString(1, "Raghav");
cstmt.setInt(2, 3000);
cstmt.setString(3, "Hyderabad");
cstmt.setString(1, "Kalyan");
cstmt.setInt(2, 4000);
cstmt.setString(3, "Vishakhapatnam");
cstmt.setString(1, "Rukmini");
cstmt.setInt(2, 5000);
cstmt.setString(3, "Delhi");
cstmt.setString(1, "Archana");
cstmt.setInt(2, 15000);
cstmt.setString(3, "Mumbai");
cstmt.execute();
System.out.println("Rows inserted ....");
}
}Output
Connection established...... Rows inserted ....
If you retrieve the contents of the Employee table using the select query, you can observe newly added records as shown below:
mysql> select * from employee; +---------+--------+----------------+ | Name | Salary | Location | +---------+--------+----------------+ | Amit | 30000 | Hyderabad | | Kalyan | 40000 | Vishakhapatnam | | Renuka | 50000 | Delhi | | Archana | 15000 | Mumbai | | Raghav | 3000 | Hyderabad | | Raghav | 3000 | Hyderabad | | Kalyan | 4000 | Vishakhapatnam | | Rukmini | 5000 | Delhi | | Archana | 15000 | Mumbai | +---------+--------+----------------+ 9 rows in set (0.00 sec)
6K+ Views
