JDBC CallableStatement interface example

A java.sql.CallableStatement interface object is used to call stored procedures from the database. It is the standard way to execute stored procedure for all DBMS/RDBMS. A stored procedure is an object stored in a database.  

The database stored procedure can be in following form :

  • Without parameter
  • With input parameter
  • With output parameter
  • With input and output parameter

Both input and output may have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (INOUT parameters). A question mark serves as a placeholder for a parameter.

Syntax :-

  • Without parameter - {call procedure_name}
  • With input parameter - {call procedure_name[(?, ?, ...)]}
  • Stored procedure that returns value - {? = call procedure_name[(?, ?, ...)]}

It is derived from java.sql.PreparedStatement interface. So we can use all the methods from Statement and PreparedStatement interface type for setting and getting value.

First establish connection with database [ Type1 driver ]         [ Type4 driver ]

Table structure in MySQL as follows :

use test;
create table Emp
(
code varchar(10primary key,
name varchar(40null,
city varchar(20),
salary int
);
insert into Emp values('a001','Ram Kumar','Noida',10000);

Here creating different type of Stored Procedure in MySQL database server and call in Java source code

Stored Procedure without parameter

We create a stored procedure without parameter in MySQL database server. I create proc1 procedure, it shows average salary of employee from EMP table.  

SQL statement for stored procedure


DELIMITER //

CREATE PROCEDURE proc1()
BEGIN
   SELECT Avg(salaryAS salary
   FROM emp;
END; //

DELIMITER ;

Here is the Java source code to call stored procedure proc1 from database


// This method use proc1 procedure
// proc1 display average salary of employee from EMP table
public void callProcedure1() {
  try {
    clmt = con.prepareCall("{call proc1}");
    ResultSet res = clmt.executeQuery();
    if (res.next()) {
      System.out.println(res.getString(1));
    }
  catch (Exception e) {
    e.printStackTrace();
  }
}

Stored procedure that return records from table

We create a stored procedure without parameter in MySQL database server. I create proc2 procedure, it shows whole employee records from EMP table
 

SQL statement for stored procedure


DELIMITER //

CREATE PROCEDURE proc2()
BEGIN
   SELECT * FROM emp;
END; //

DELIMITER ;

Here is the Java source code to call stored procedure proc2 from database

// This method use proc2 procedure
// It displays the whole records from Emp table
public void callProcedure2() {
  try {
    clmt = con.prepareCall("{call proc2}");
    ResultSet res = clmt.executeQuery();
    while (res.next()) {
      System.out.println(res.getString(1"\t" + res.getString(2)
          "\t" + res.getString(3"\t" + res.getString(4));
    }
  catch (Exception e) {
    e.printStackTrace();
  }
}

Stored procedure with two IN parameter

We create a stored procedure with parameter in MySQL database server. I create a procedure - proc3, it takes two input parameters as an employee code and employee name and store it in Emp table  

SQL statement for stored procedure

DELIMITER //

CREATE PROCEDURE proc3(IN code1 varchar(10),IN name1 varchar(10))
BEGIN
   insert into emp(code,namevalues(code1,name1);
END; //

DELIMITER ;

Here is the Java source code to call stored procedure proc3 from database

// call procedure proc3 with two IN parameters and store in emp table
public void callProcedure3(String code, String name) {
  try {
    clmt = con.prepareCall("{call proc3(?,?)}");
    clmt.setString(1, code);
    clmt.setString(2, name);
    int i = clmt.executeUpdate();
    if (i != 0)
      System.out.println("Inserted successfully");
    else
      System.out.println("Not Inserted");
  catch (Exception e) {
  }
}

Stored procedure with one IN and OUT parameter

We create a stored procedure with parameter in MySQL database server. I create proc4 procedure, it takes an input parameter as employee code and return employee name as an OUTPUT parameter from EMP table  

SQL statement for stored procedure

DELIMITER //

CREATE PROCEDURE proc4(IN code1 varchar(10),OUT name1 varchar(10))
BEGIN
  SELECT name from emp where code=code1
  INTO name1;
END; //

DELIMITER ;

Here is the Java source code to call stored procedure proc4 from database

// procedure with one in and one out parameter
// It takes emp code as parameter and return emp name as output parameter
public void callProcedure4(String code) {
  try {
    clmt = con.prepareCall("{call proc4(?,?)}");
    clmt.setString(1, code);
    clmt.registerOutParameter(2, Types.VARCHAR);
    clmt.execute();
    System.out.println(clmt.getString(2));
  catch (Exception e) {
  }
}

Stored procedure with INOUT parameter

We create a stored procedure with parameter in MySQL database server. I create proc5 procedure, it takes an input parameter as employee code and return employee name as an OUTPUT parameter using INOUT parameter from EMP table  

SQL statement for stored procedure
DELIMITER //

CREATE PROCEDURE proc5(INOUT var1 varchar(20))
BEGIN
  SELECT name from emp where code=var1
  INTO var1;
END; //

DELIMITER ;
Download SQL statement

Here is the Java source code to call stored procedure proc5 from database


// proc5 procedure takes one IN parameter and return value in OUT parameter
// It uses single INOUT parameter for both purpose
public void callProcedure5(String var1) {
  try {
    clmt = con.prepareCall("{call proc5(?)}");
    clmt.registerOutParameter(1, Types.VARCHAR);
    clmt.setString(1, var1);
    clmt.execute();
    System.out.println(clmt.getString(1));
  catch (Exception e) {
  }
}
Download Java Source Code

Previous Next

Comment by : sonia Suri August 17th, 2016
Comment : really beneficial,nicely explained ,thanks a lot

Comment by : Dr monika November 19th, 2014
Comment : very nicely explained. really helped a lot. thanks

Comment by : Aju Cyriac September 27th, 2014
Comment : nice.... thankYoU

Comment by : Evelyn June 19th, 2014
Comment : Thank you very much!

Comment by : Jitendra May 28th, 2014
Comment : Nicen Post

Comment by : Kimi January 30th, 2014
Comment : Nice Examples..... Thankyou.......

Comment by : Dinesh Bruse January 1st, 2014
Comment : This tutorial was so good... easy to understand.. Friends Please share Java/J2EE related stuff. in FB My ID Dinesh Bruse , JAVA CODE group.

Comment by : nizam December 23rd, 2013
Comment : this is most useful for beginners

Comment by : Lakshmu Naidu September 5th, 2013
Comment : really nice tutorial.

Comment by : raj July 9th, 2013
Comment : nice supurve

Comment by : Venkata Reddy G July 6th, 2013
Comment : Its very understandble examples....great job..

Comment by : Avinash Thakur June 30th, 2013
Comment : thanks!!! great work..

Comment by : sakshi April 26th, 2013
Comment : All callable statement examples at one place. Gr88 Job. Thanks

Comment by : shankar kumar March 2nd, 2013
Comment : Its really nice tutorial. I was searching for jdbc callable with parameter example. Thanks a lot

Comment by : shankar kumar March 2nd, 2013
Comment : Its fantastic. I was searching jdbc example with callable( with parameter). Thanks a lot


Write your Comment

Name :
Email Id : [ We will not publish your email id ]
Your Comment : [ Maximum 200 chars ]
Enter Security code