Advanced Java (2160707)

BE | Semester-4   Winter-2018 | 20-11-2018

Q3) (c)

What is CallableStatement? Show that how to use it to call a stored procedure running at database layer.

What is Callable Statement

  • CallableStatement interface is used to call the stored procedures.
  • We can have business logic on the database by the use of stored procedures that will make the performance better as they are precompiled.
  • Three types of parameters exist: IN, OUT, and INOUT. The PreparedStatement object only uses the IN parameter. The CallableStatement object can use all the three.
Sr. Parameter Description
1 IN A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
2 OUT A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.
3 INOUT A parameter that provides both input and output values. You bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Stored Procedure

Stored Procedure
(Figure: Stored Procedure)

Program To Use Stored Procedure

  1. import java.sql.*;
  2. public class CallableDemo {
  3.     public static void main(String[] args) {
  4.     try {
  5.         Class.forName("com.mysql.jdbc.Driver");
  6.         Connection conn= DriverManager.getConnection
  7.         ("jdbc:mysql://localhost:3306/gtu", "root",“pwd");
  9.         CallableStatement cs=conn.prepareCall("{call gettitle(?,?)}");
  10.         cs.setInt(1,1201);
  11.         cs.registerOutParameter(2,Types.VARCHAR);
  12.         cs.execute();
  13.         System.out.println(cs.getString(2));
  15.         cs.close();
  16.         conn.close();
  17.     }catch(Exception e){System.out.println(e.toString());}
  18.     }//PSVM
  19. }//class