JDBC: Callable Statement and Stored Procedure

Callable Statement:

if we want to execute the Stored Procedure then we can use CallableStatement. so first we can learn what is Stored procedure then we can understand how Callable Statement will work.

What is the Stored Procedure?

In programming, if any code is repeatedly required, then we can define those code inside the method and we can call that method multiple times based on our requirement. here we can say methods are the best reusable component in programming.

similarly in Database programming, if any group of SQL statements is repeatedly required, then we can define those SQL statements as a single group and we can call that group repeatedly based on our requirement. This group of SQL statements that performs a particular task is called as "stored procedure".

stored procedure is the best reusable component at Database level.

In simple words, we can say stored procedure refers to the group of SQL statements that performs a particular task

stored procedures are stored permanently in the Database for future usage. Hence it is named a stored procedure. and usually stored procedures are created by Database Admin(DBA). Every Database has its own language to create a stored procedure.

a. Oracle --> PL/SQL

b. MySQL --> stored procedure Language

c. MicrosoftSQLserver --> Transact SQL(TSQL)

Stored Procedure Parameters()

similar to methods stored procedure has its own parameters.

stored procedure has 3 parameters.

a. IN (to provide the input values)

b. OUT (to collect the output values)

c. INOUT (to provide the input and collect the output values).

How to create stored procedure?

CREATE DEFINER=`root`@`localhost` PROCEDURE `Get_Details_By_ID`(IN id int, OUT name varchar(50), OUT rate int, OUT qnty int)
BEGIN

END

In the above procedure we have included both IN and OUT parameters. If we want to fetch any records from table, then we can write stored procedure as follows.

CREATE DEFINER=`root`@`localhost` PROCEDURE `Get_Details_By_ID`(IN id int, OUT name varchar(50), OUT rate int, OUT qnty int)
BEGIN
  select pname, prate, pqnty
  into name,rate,qnty
  from products
  where pid=id;
END

Syntax for calling stored procedure from MySQL DB:

CALL Get_Details_By_ID(2,@name, @rate, @qnty);
SELECT @name, @rate, @qnty;

Syntax for calling stored procedure from java program:

String storedprocedure=  "{CALL Get_Details_By_ID(?,?,?,?)}";
CallableStatement cstmt = connection.prepareCall(storedprocedure);

when JVM encounters the above line, first JVM will send a notification to Database. and Database Engine will check whether the specified stored procedure is available or not. If it is available, then it will return CallableStatement Object which represents that procedure.

Setting and Getting values from stored procedure:

To set the Input value to the stored procedure we use normal setter method

i.e.., setxxxx();

if we want to get the values from stored procedure, then first we should register OUT parameters with java specific datatype. (Java specific datatype and DB specific data type is different)

In Java --> String

In SQL --> VARCHAR

To match java data type with DB specific data type we can use one mechanism, that mechanism we can call it as JDBC Types or Bridge Types.

Example code:

//To set the input values to stored procedure
if(cstmt!=null) {
    cstmt.setInt(1, id);
}
/*To get values from stored procedure, first we should register OUT variables with java specific data type*/
if(cstmt!=null) {
     cstmt.registerOutParameter(2, Types.VARCHAR);
     cstmt.registerOutParameter(3, Types.INTEGER);
     cstmt.registerOutParameter(4, Types.INTEGER);
}
//To get the values from stored procedure
if(cstmt!=null) {
      System.out.println("pname\tprate\tpqnty");
      System.out.println(cstmt.getString(2)+"\t"+cstmt.getInt(3)
       +"\t"+cstmt.getInt(4));
}

Note:

• In the above stored procedure we have used OUT parametes. so we won't get group of records. so we can get OUT values by calling cstmt.getString(2).

• If we have specified only IN parameters in stored procedure, then we will get a group of records in the form of ResultSet.

Conclusion: If we want to execute stored procedure then we can use Callable Statement.