How to call a stored procedure using callable statement in JDBC explain?

JDBCJava 8MySQLMySQLi Database

You can call the SQL stored procedures using the CallableStatement interface. A Callable statement can have input parameters, output parameters, or both.

You can create an object of the CallableStatement (interface) using the prepareCall() method of the Connection interface. This method accepts a string variable representing a query to call the stored procedures and returns a CallableStatement object.

Suppose you have a procedure name myProcedure in the database you can prepare a callable statement as:

//Preparing a CallableStatement
CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");

Then you can set values to the place holders using the setter methods of the CallableStatement interface and execute the callable statement using the execute() method as shown below.

cstmt.setString(1, "Raghav");
cstmt.setInt(2, 3000);
cstmt.setString(3, "Hyderabad");
cstmt.execute();

If there are no input values for the procedure you can simply Prepare the callable statement and execute it as shown below:

CallableStatement cstmt = con.prepareCall("{call myProcedure()}");
cstmt.execute();

Example

Suppose we have a table named Dispatches in the MySQL database with the following data:

+--------------+------------------+------------------+----------------+
| Product_Name | Date_Of_Dispatch | Time_Of_Dispatch | Location       |
+--------------+------------------+------------------+----------------+
| KeyBoard     | 1970-01-19       | 08:51:36         | Hyderabad      |
| Earphones    | 1970-01-19       | 05:54:28         | Vishakhapatnam |
| Mouse        | 1970-01-19       | 04:26:38         | Vijayawada     |
+--------------+------------------+------------------+----------------+

And if we have created a procedure named myProcedure to retrieve values from this table as shown below:

Create procedure myProcedure ()
-> BEGIN
-> SELECT * FROM Dispatches;
-> END //

Example

Following is a JDBC example calls the above mentioned stored procedure using JDBC program.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CallingProcedure {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());

      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");

      //Preparing a CallableStateement
      CallableStatement cstmt = con.prepareCall("{call myProcedure()}");

      //Retrieving the result
      ResultSet rs = cstmt.executeQuery();
      while(rs.next()) {
         System.out.println("Product Name: "+rs.getString("Product_Name"));
         System.out.println("Date of Dispatch: "+rs.getDate("Date_Of_Dispatch"));
         System.out.println("Time of Dispatch: "+rs.getTime("Time_Of_Dispatch"));
         System.out.println("Location: "+rs.getString("Location"));
         System.out.println();
      }
   }
}

Output

Connection established......
Product Name: KeyBoard
Date of Dispatch: 1970-01-19
Time of Dispatch: 08:51:36
Location: Hyderabad

Product Name: Earphones
Date of Dispatch: 1970-01-19
Time of Dispatch: 05:54:28
Location: Vishakhapatnam

Product Name: Mouse
Date of Dispatch: 1970-01-19
Time of Dispatch: 04:26:38
Location: Vijayawada
raja
Published on 20-Mar-2019 15:28:35
Advertisements