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 create a Stored procedure in a database using JDBC API?
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.
To create a stored procedure in (MySQL) a database using JDBC API you need to:
Register the driver: 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 Statement object using the createStatement() method of the Connection interface.
Execute the Query: Execute the query to create stored procedures, using the execute() method of the Statement interface.
Example
Assume we have a table named Customers in the database with the following description:
+---------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------+------+-----+---------+-------+ | ID | int(11) | NO | PRI | NULL | | | NAME | varchar(20) | NO | | NULL | | | AGE | int(11) | NO | | NULL | | | SALARY | decimal(18,2) | YES | | NULL | | | ADDRESS | char(25) | YES | | NULL | | +---------+---------------+------+-----+---------+-------+
Following JDBC program establishes connection with MySQL and creates a procedure named retrieveData in the database named mydatabase.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateStoredProcedureExample {
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......");
//Creating the Statement
Statement stmt = con.createStatement();
//Query to create stored procedures
String query = "CREATE PROCEDURE `retrieveData`() " +
"BEGIN "+
" SELECT * FROM Customers; "+
"END";
//Executing the query
stmt.execute(query);
System.out.println("Procedure Created......");
}
}Output
Connection established...... Procedure Created......
The SHOW CREATE PROCEDURE procedure_name command displays the source code of the specified procedure if it does not exist you will get an error.
Verify whether the procedure named retrieveData created in MySQL database, using this command as:
mysql> SHOW CREATE PROCEDURE retrieveData; +--------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +--------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | retrieveData | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `retrieveData`() BEGIN SELECT * FROM Customers; END | utf8 | utf8_general_ci | utf8_general_ci | +--------------+----------------------------------------------------------------+----------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec)
2K+ Views
