JDBC – CallableStatement Interface

CallableStatement interface:

  • The interface used to call stored procedures and functions.
  • SQL stored procedures and functions are used to write and store business logic inside the database.
Stored ProcedureFunction
It is used to perform business logic.It is used to perform calculation.
It must not have the return type.It must have the return type.
It may return 0 or more values.It can return only one value.
We can call functions from the procedure.Procedure cannot be called from function.
Procedure supports input and output parameters.Function supports only input parameter.

How to get the instance of CallableStatement?

  • The prepareCall() method of Connection interface returns the instance of CallableStatement.
    • CallableStatement stmt=con.prepareCall(“{call myprocedure(?,?)}”); 

Create ‘test’ table in datatbase:

SQL> create table test(num number(10));
Table created.

Create stored procedure to insert data into ‘test’ table:

SQL> create or replace procedure insertNum(num number)
  insert into test values(num);
procedure created successfully.

Insert Number into Table using StoredProcedure :

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class CallableInsertNum
            static Connection con;
            public static void main(String args[]) throws Exception
                                    System.out.println(“Driver is ready”);
                                    con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”, “system”, “admin”);
                                    System.out.println(“Connection is ready”);
                                    String pro = “{call insertNum(?)}”;
                                    CallableStatement cs = con.prepareCall(pro);
                                    cs.setInt(1, 250);
                                    System.out.println(“Record is inserted into test table through insertNum procedure!”);                                 
                                    if(con != null)
                                                System.out.println(“Connection closed”);

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top