JDBC Callable Statement – Execute Store Procedure

In this tutorial you will learn

1. What is Callable Statement in JDBC?
2. How to use Store Procedure in JDBC?
3. Programming Example

What is Callable Statement in JDBC?

The CallableStatement Interface is used in accessing and executing Store Procedure and Function. Store Procedure is a group of SQL Statement that encapsulates all the queries and gets compiled. Once store procedure gets compiled it can be executed so many times without compilation. This makes it robust, faster and high performing. It accepts parameter for sql queries that is hidden inside it and gets executed. The Callable Statement in JDBC is used to manipulating these store procedure.

How to use Callable Statement to Call Store Procedure?

Here, I am going to present a simple callable statement example. This example includes following steps.

1. First step is creating a store procedure. I will create a store procedure to insert data into ITEM table.
2. Second Step is Using CallableStatement, I will execute this store procedure and insert data to table.

Programming Example

1. Store Procedure: Creating Store Procedure

You need to execute this statement in your database server sql window. Here, I am using MySQL and used PHPMyAdmin page to create this store procedure.

DELIMITER //
CREATE PROCEDURE InsertProc(IN prd varchar(50), IN prc varchar(10))
BEGIN
  INSERT INTO ITEM(PRODUCT,PRICE) VALUES(prd,prc);
END //
DELIMITER ;

This statement will create InsertProc Store procedure which takes 2 parameter prd and prc.

2. Use CallableStatement in JDBC to call and execute this store procedure.
package AdvanceJDBC;

import java.sql.*;

import com.mysql.jdbc.CallableStatement;

public class Callable_StoreProc
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	CallableStatement csmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize CallableStatement
	    csmt=(CallableStatement)con.prepareCall("{call InsertProc(?,?)}");
	    //Step 3 : Execute CallableStatement with Store Procedure
	    csmt.setString(1, "Intel i7 Processor");
	    csmt.setString(2, "27000");
	    csmt.execute();
	    System.out.println("Record Inserted Successfully");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Summary

In this chapter you learned how to use CallableStatement in JDBC to execute store procedure in MySQL Database. I have kept this tutorial simple and clean with complete programming example. Hope, this will help you to understand basics of callable statement in JDBC.


Leave a Reply

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