«Back to Home

Core Java
Topics

CallableStatement Interface In Java

CallableStatement Interface
 
In JDBC, CallableStatement interface is used to call the stored procedures and functions. We can have a business logic on the database with the use of the stored procedures and functions, which will make the performance better because these are precompiled.
 
Difference between Stored Procedures and Functions

 Stored Procedure  Function
Stored Procedure is used to perform the business logic. Function is used to perform the calculation.
Stored procedure must not have the return type. Function must have the return type.
Stored procedure may return 0 or more values. Function may return only one value.
We can call functions from the procedure. Procedure cannot be called from function.
Stored procedure supports input and output parameters. Function supports only an input parameter.
In the stored procedures, exception handling uses try/catch block can be used.  In the user defined functions, exception handling uses try/catch can't be used.
 
Get the object of CallableStatement

The connection interface prepareCall() method is used to return the object of CallableStatement.
 
Syntax

public CallableStatement prepareCall("{ call procedurename(?,?...?)}");
 
For example

CallableStatement st=con.prepareCall("{call mysprocedure(?,?)}");
 
It calls the procedure mysprocedure which receives two arguments.
 
Call the Stored procedure using JDBC

Let’s see an example, given below, to call the stored procedure, using JDBC.
 
Code

To call the stored procedure, first we need to create it in the database.
  1. create or replace procedure "STRPRO"  
  2. (id IN NUMBER,  
  3. name IN VARCHAR2)  
  4. is  
  5. begin  
  6. insert into Student values(id,name);  
  7. end;  
  8. /  
3
 
The table structure is given below.
 
create table student(id number(10), name varchar2(100));
 
Code
  1. import java.sql.*;  
  2. public class StoredProcExample {  
  3.     public static void main(String args[]) throws Exception {  
  4.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  5.         String url = "jdbc:derby://localhost:1527/Student";  
  6.         String username = "Student";  
  7.         String password = "student";  
  8.         Connection conn = DriverManager.getConnection(url, username, password);  
  9.         CallableStatement stmt = conn.prepareCall("{call STRPRO(?,?)}");  
  10.         stmt.setInt(1, 2569);  
  11.         stmt.setString(2, "James");  
  12.         stmt.execute();  
  13.         System.out.println("insert successfully....");  
  14.     }  
  15. }  
4
 
In the above example, we want to call the stored procedure STRPRO that receives id and name as the parameter and inserts it into the table Student. Now check the table in the database, value is inserted in the Student table.
 
Call the function using JDBC

Let’s see another example: to call the function using JDBC
 
Code

First create the simple function in the database.
  1. create or replace function sum1  
  2. (n1 in number,n2 in number)  
  3. return number  
  4. is  
  5. temp number(8);  
  6. begin  
  7. temp :=n1+n2;  
  8. return temp;  
  9. end;  
  10. /   
5
 
In this program, we are calling the sum1 function, which receives two inputs and returns the sum of the given number. Now, we have used the registerOutParameter method of CallableStatement interface, which registers the output parameter with its corresponding type. It gives information to the CallableStatement about the type of result being displayed.
 
The Types class describes many constants such as INTEGER, VARCHAR, FLOAT, DOUBLE etc.
 
Code
  1. import java.sql.*;  
  2. public class FunctionExample {  
  3.     public static void main(String args[]) throws Exception {  
  4.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  5.         String url = "jdbc:derby://localhost:1527/Student";  
  6.         String username = "Student";  
  7.         String password = "student";  
  8.         Connection conn = DriverManager.getConnection(url, username, password);  
  9.         CallableStatement st = conn.prepareCall("{?= call sum1(?,?)}");  
  10.         st.setInt(2, 25);  
  11.         st.setInt(3, 10);  
  12.         st.registerOutParameter(1, Types.INTEGER);  
  13.         st.execute();  
  14.         System.out.println(st.getInt(1));  
  15.     }  
  16. }  
6

Summary

Thus, we learnt, JDBC CallableStatement interface is used to call the stored procedures and functions. We also learnt the difference between the stored procedures and functions in Java.