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 that accepts input parameters, using JDBC program?
A. Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures.
Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.
You can call a stored procedure using the following syntax:
CALL procedure_name (input_parameter1, input_parameter2, input_parameter3)
JDBC provides a standard stored procedure SQL escape syntax using which you can procedures in all RDBMSs
To call a stored procedure using a JDBC program you need to:
Register the driver: class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.
Establish a connection: Connect ot the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.
Create Statement: Create a CallableStatement object using the prepareCall() method of the Connection interface.
Execute the Query: Execute the query using the execute() method of the Statement interface.
Example
Assume we have a table named customers in the database with the following contents:
+----+-----------+-----+---------+----------------+ | ID | NAME | AGE | SALARY | ADDRESS | +----+-----------+-----+---------+----------------+ | 1 | Amit | 25 | 3000.00 | Hyderabad | | 2 | Kalyan | 27 | 4000.00 | Vishakhapatnam | | 3 | Renuka | 30 | 5000.00 | Delhi | | 4 | Archana | 24 | 1500.00 | Delhi | | 5 | Koushik | 30 | 9000.00 | Delhi | | 6 | Hardik | 45 | 6400.00 | Delhi | +----+-----------+-----+---------+----------------+
We have created a stored procedure named insertData in the database as shown below:
mysql> DELIMITER //; mysql> Create procedure insertData( IN c_id INT, IN c_name VARCHAR(255), IN c_age INT, IN c_sal INT, IN c_add VARCHAR(255)) BEGIN INSERT INTO CUSTOMERS VALUES (c_id, c_name, c_age, c_sal, c_add); END// mysql> DELIMITER ; Query OK, 0 rows affected (0.00 sec)
This procedure accepts id (integer), name (String), age (integer), salary (integer) and, address (String) as IN parameters and inserts a new record in the customers table.
Following JDBC program establishes connection with MySQL database, inserts 6 records in the customers table using the procedure named insertData.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class CallngStoredProcedureExample_IN {
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/mydatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Preparing a CallableStatement to call a procedure
CallableStatement cstmt = con.prepareCall("{call insertData(?, ?, ?, ?, ?)}");
//Setting values for the IN parameters of the procedure
cstmt.setInt(1, 7);
cstmt.setString(2, "Trupthi");
cstmt.setInt(3, 33);
cstmt.setInt(4, 4360);
cstmt.setString(5, "Ahmedabad");
cstmt.execute();
cstmt.setInt(1, 8);
cstmt.setString(2, "Mithili");
cstmt.setInt(3, 26);
cstmt.setInt(4, 4100);
cstmt.setString(5, "Vijayawada");
cstmt.execute();
cstmt.setInt(1, 9);
cstmt.setString(2, "Maneesh");
cstmt.setInt(3, 39);
cstmt.setInt(4, 4000);
cstmt.setString(5, "Hyderabad");
cstmt.execute();
cstmt.setInt(1, 10);
cstmt.setString(2, "Rajaneesh");
cstmt.setInt(3, 30);
cstmt.setInt(4, 6400);
cstmt.setString(5, "Delhi");
cstmt.execute();
cstmt.setInt(1, 11);
cstmt.setString(2, "Komal");
cstmt.setInt(3, 29);
cstmt.setInt(4, 8000);
cstmt.setString(5, "Ahmedabad");
cstmt.execute();
cstmt.setInt(1, 12);
cstmt.setString(2, "Manyata");
cstmt.setInt(3, 25);
cstmt.setInt(4, 5000);
cstmt.setString(5, "Vijayawada");
cstmt.execute();
System.out.println("Procedure called by passing required values......");
}
}Output
Connection established...... Procedure called by passing required values......
If you retrieve the contents of the customers table using the select command, you can observe the newly added records as:
mysql> select * from customers; +----+-----------+-----+---------+----------------+ | ID | NAME | AGE | SALARY | ADDRESS | +----+-----------+-----+---------+----------------+ | 1 | Amit | 25 | 3000.00 | Hyderabad | | 2 | Kalyan | 27 | 4000.00 | Vishakhapatnam | | 3 | Renuka | 30 | 5000.00 | Delhi | | 4 | Archana | 24 | 1500.00 | Delhi | | 5 | Koushik | 30 | 9000.00 | Delhi | | 6 | Hardik | 45 | 6400.00 | Delhi | | 7 | Trupthi | 33 | 4360.00 | Ahmedabad | | 8 | Mithili | 26 | 4100.00 | Vijayawada | | 9 | Maneesh | 39 | 4000.00 | Hyderabad | | 10 | Rajaneesh | 30 | 6400.00 | Delhi | | 11 | Komal | 29 | 8000.00 | Ahmedabad | | 12 | Manyata | 25 | 5000.00 | Vijayawada | +----+-----------+-----+---------+----------------+ 12 rows in set (0.00 sec)
3K+ Views
