JDBC CallableStatement Interface

CallableStatement in JDBC is an interface, which is coming from the java.sql package, statements in JDBC are 3 types. Here we are going to discuss one of the type called CallableStatement.

CallableStatement in JDBC :

  1. 1. CallableStatement in JDBC is a sub interface of PreparedStatement.
  2. 2. 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.
  3. 3. CallableStatement is only for calling a procedure or a function of a database. But it is not for creating a procedure or function.
  4. 4. CallableStatement has two syntaxes, one is for executing commands and another is for calling the procedure or function.
  5. 5. We can get the CallableStatement object by calling the prepareCall() method on connection object.

Syntax:

                        CallableStatement cstmt = connection.prepareCall("sql command"); // for executing command
                        CallableStatement cstmt = connection.prepareCall("{call procedure(args)}"); // calling procedure
                        CallableStatement cstmt = connection.prepareCall("{?=call function(args)}"); // calling function
Creating a procedure:

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

                        package com.java.session.ninteen;

                        import java.sql.*;

                        public class JdbcProceduresExample {
                            static final String DB_URL = "jdbc:mysql://localhost/emp";
                            static final String USER = "root";
                            static final String PASS = "PASSWORD";
                            static final String QUERY = "{call square(?,?)}";
                            public static void main(String[] args) throws Exception {
                                Connection connection = null;
                                CallableStatement cstmt = null;

                                try {
                                    connection = DriverManager.getConnection(DB_URL, USER, PASS);
                                    cstmt = connection.prepareCall(QUERY);
                                    cstmt.setInt(1, 5);
                                    cstmt.registerOutParameter(2, Types.INTEGER);
                                    cstmt.execute();
                                    System.out.println("The Square is : " + cstmt.getInt(2));
                                } catch (SQLException se) {
                                    se.printStackTrace();
                                } finally {
                                    try {
                                        cstmt.close();
                                        connection.close();
                                    } catch (SQLException se) {
                                        se.printStackTrace();
                                    }
                                }
                            }
                        }

Before creation of function in mysql, please execute the following statement SET GLOBAL log_bin_trust_function_creators = 1;

Creating a function:

                        CREATE FUNCTION multiplicaiton(a INT, b INT) RETURNS int
                        BEGIN
                        declare result INT;
                        set result = a*b;
                        RETURN result;
                        END
Example Program:

                        package com.java.session.ninteen;

                        import java.sql.*;

                        public class JDBCFunctionExample {
                            static final String DB_URL = "jdbc:mysql://localhost/emp";
                            static final String USER = "root";
                            static final String PASS = "PASSWORD";
                            static final String QUERY = "{?=call multiplicaiton(?,?)}";
                            public static void main(String[] args) throws Exception {
                                Connection connection = null;
                                CallableStatement cstmt = null;

                                try {
                                    connection = DriverManager.getConnection(DB_URL, USER, PASS);
                                    cstmt = connection.prepareCall(QUERY);
                                    cstmt.registerOutParameter(1, Types.INTEGER);
                                    cstmt.setInt(2, 20);
                                    cstmt.setInt(3, 60);
                                    cstmt.execute();
                                    System.out.println("The multiplication of given two numbers : " + cstmt.getInt(1));
                                } catch (SQLException se) {
                                    se.printStackTrace();
                                } finally {
                                    try {
                                        cstmt.close();
                                        connection.close();
                                    } catch (SQLException se) {
                                        se.printStackTrace();
                                    }
                                }
                            }
                        }