What is CallableStatement in JDBC?

JDBCJava 8MySQLMySQLi

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)
raja
Published on 20-Mar-2019 15:13:21
Advertisements