Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to call a stored procedure using callable statement in JDBC explain?
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
3K+ Views
