In this tutorial, we are going to learn bout CallableStatement in JDBC. CallableStatement in JDBC is an interface, which is coming from the java.sql package. We have already discussed in the previous tutorial Steps by Step JDBC program example, statements in JDBC are 3 types. Here we are going to discuss one of the type called CallableStatement.

CallableStatement in JDBC :

• CallableStatement in JDBC is a sub interface of PreparedStatement.
• CallableStatement in JDBC has all the benefits of PreparedStatement and also it has one more additional feature, that is we can call the procedures or functions of a database.
• CallableStatement is only for calling a procedure or a function of a database. But it is not for creating a procedure or function.
• CallableStatement has two syntaxes, one is for executing commands and another is for calling the procedure or function.
• We can get the CallableStatement object by calling the prepareCall() method on connection object.

CallableStatement for Executing command :

CallableStatement cstmt = connection.prepareCall("sql command");

CallableStatement for calling procedures :

CallableStatement cstmt = connection.prepareCall("{call procedure(args)}");

CallableStatement for calling functions :

CallableStatement cstmt = connection.prepareCall("{?=call function(args)}");

CallableStatement in JDBC Procedure Example :

Creating Procedure in MySql :

mysql> delimiter $
mysql> create procedure square(IN a int, OUT b int)
-> begin
-> set b=a*a;
-> end;
-> $

Example:

package com.onlinetutorialspoint.jdbc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class JDBC_Procedures_Example {

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/onlinetutorialspoint", "root",
                "123456");
        CallableStatement cStmt = connection.prepareCall("{call square(?,?)}");
        cStmt.setInt(1, 20);
        cStmt.registerOutParameter(2, Types.INTEGER);
        cStmt.execute();
        System.out.println("The Square is : " + cStmt.getInt(2));
        cStmt.close();
    }

}

In the above code, we register the out parameter to CallableStatement by using the registerOutParameter(). It is mandatory because if we do not register the “out” parameter, then by default CallableStatement will take all the parameters as “in” parameters. So to inform CallableStatement that it is an out parameter we need to  register it.

While registering the out parameter, we need to tell the CallableStatement about the parameter type also. CallableStatement can understand only JDBC Types, but not java or database types. In JDBC Types class contains all the JDBC data types. Types is a class in java.sql package.

Output :

The Square is : 400

CallableStatement in JDBC Function Example :

Creating Function in MySQL :

mysql> DELIMITER $
mysql> CREATE FUNCTION mul(a int, b int) RETURNS INT
-> BEGIN
-> DECLARE c INT;
-> SET c = a*b;
-> RETURN c;
-> END;
-> $

Example:

package com.onlinetutorialspoint.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

class JDBC_Function_Example {

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/onlinetutorialspoint", "root",
                "123456");
        CallableStatement cStmt = connection.prepareCall("{?=call mul(?,?)}");

        cStmt.registerOutParameter(1, Types.INTEGER);
        cStmt.setInt(2, 20);
        cStmt.setInt(3, 60);
        cStmt.execute();
        System.out.println("The Multiplication is : " + cStmt.getInt(1));
        cStmt.close();
    }
}

Output:

The Multiplication is : 1200

Happy Learning 🙂