Stored procedures or stored routines are created by using a group of SQL statements that perform a particular task. These procedures accept input and may return a value. Stored procedures are useful when you want to reuse the command multiple times as it reduces the redundant writing of code.
So when you encounter a repetition of a query or set of queries you can simply save it as a stored procedure and call it whenever required. Typically stored procedures are created by a DBA (Database Administrator). In MySQL, we use TSQL (transact-SQL) to create a stored procedure for MySQL servers.
Syntax of MySQL Stored Procedure:
CREATE PROCEDURE procedure_name (parameter1, parameter2, parameter3,..)procedure_body
BEGIN
//Write your queries here
ENDCode language: JavaScript (javascript)In this tutorial, we’ll learn to create and use stored procedures using JDBC.
Prerequisites for this tutorial are:
- Java and JDBC: You must have good knowledge of java programming language and must know how to use JDBC.
- MySQL: You must also possess good knowledge of MySQL DBMS to get started with this tutorial.
Also read: JDBC Tutorial: MySQL JDBC Transaction
What are callable statements and how to use them?
CallableStatement is an interface provided by JDBC API for calling stored procedures whenever we are using java to call a routine from a DBMS, in our case, MySQL. It extends PreparedStatement and also supports input, output, and input/output operations. We will learn about these operations in detail in the next section.
The syntax for calling a stored procedure is:
CallableStatement call = myConn.prepareCall("{call procedure_name()}");
call.execute();Code language: Java (java)In the above syntax, the procedure called has no parameters, in the next section, we will learn how to use parameters.
Call stored procedures with parameters
As we discussed in the introduction section, there are three types of parameters:
- IN: IN is a default parameter, and if you’re not considering any parameter then it will be taken as IN or input.
- INOUT: This parameter takes in input and returns itself as an output.
- OUT: This parameter only returns output and does not take any input.
In this section, we will see examples of how to call a procedure using JDBC and we’ll be seeing examples for each of the above parameters.
IN parameter
IN parameter also known as an input parameter, takes input for a stored procedure. In this section, we’ll create a procedure for the student’s table where we will reduce the fees of every student present in the table in accounts of covid. The student table has fields such as first name, last name, contact number, class, section, total_fees, and teacher. Let’s see the SQL code below to create a procedure for the same:
PROCEDURE 'reduce_student_fee'(
IN amount DECIMAL(10,2))
BEGIN
UPDATE student SET total_fee = total_fee - amount;
ENDCode language: JavaScript (javascript)In the above example, you saw how to create an SQL stored procedure. Now we’ll use this procedure to perform the changes, for that we’ll be using Java and JDBC will be used for connectivity to the database.
Below is the code to reduce the fees for all the students:
import java.sql.*;
public class DecreaseFee {
public static void main(String[] args) throws Exception {
Connection myConn = null;
CallableStatement myStmt = null;
try {
// Create connection
myConn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/db", "student", "password");
int decreaseAmount = 10000;
// Prepare the stored procedure call
myStmt = myConn.prepareCall("{call reduce_student_fee(?)}");
// Set the parameter
myStmt.setDouble(1, decreaseAmount);
// Call stored procedure
System.out.println("\nCalling stored procedure. reduce_student_fee(" + decreaseAmount + ")");
myStmt.execute();
System.out.println("Changes executed successfully..");
}
catch (Exception e) {
e.printStackTrace();
}
finally {
myStmt.close();
myConn.close();
}
}
}
Code language: Java (java)INOUT parameter
The INOUT parameter is a parameter that takes an input and returns that as an output. We’ll learn it with a simple example, let’s imagine that the students in the final year are graduating and what we need to do to bid them adieu. We’ll be using the student table just as in the previous section. Let’s see how we can create stored procedure around that:
PROCEDURE 'farewell_student' (INOUT class int(3))
BEGIN
SET fname = concat('We will miss you', class, '! All the best for the future..');
ENDCode language: SQL (Structured Query Language) (sql)Now that we have created MySQL procedure, let’s write the java code to call the same:
import java.sql.*;
public class FarewellStudent {
public static void main(String[] args) throws Exception {
Connection myConn = null;
CallableStatement myStmt = null;
try {
// Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/db", "student", "password");
String class = '12';
// Prepare the stored procedure call
myStmt = myConn.prepareCall("{call farewell_student}");
// Set the parameter
myStmt.registerOutParameter(1, Types.INTEGER);
myStmt.setInt(1, class);
// Call stored procedure
System.out.println("Calling stored procedure. farewell_student('" + class + "')");
myStmt.execute();
System.out.println("Processing Finished..");
// Get the value of the INOUT parameter
String result = myStmt.getInt(1);
System.out.println("\nThe result = " + result);
}
catch (Exception e) {
e.printStackTrace();
}
finally {
myStmt.close();
myConn.close();
}
}
}
OUT parameter
OUT parameter is used to return a particular value in a stored procedure. Let’s take an example of the employee table, where we will count all the employees belonging to a particular department. Let’s create the stored procedure:
PROCEDURE get_count_by_dept (IN dept_name varchar(30), out count_no INT)
BEGIN
SELECT COUNT(*) INTO count_no FROM employee
WHERE dept = dept_name;
ENDCode language: SQL (Structured Query Language) (sql)In the above-mentioned procedure, we can clearly see that dept_name is an input parameter, and the count_no is what this procedure will return. Now, let’s write the java code for the procedure to be called:
import java.sql.*;
public class GetCount {
public static void main(String[] args) throws Exception {
Connection myConn = null;
CallableStatement myStmt = null;
try {
// Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/db", "employee", "password");
String dept_name = "IT";
// Prepare the stored procedure call
myStmt = myConn.prepareCall("{call get_count_by_dept(?, ?)}");
// Set the parameters
myStmt.setString(1, dept_name);
myStmt.registerOutParameter(2, Types.INTEGER);
// Call stored procedure
System.out.println("Calling stored procedure. get_count_by_dept('" + dept_name + "', ?)");
myStmt.execute();
System.out.println("Processing finished..");
// Get the value of the OUT parameter
int count = myStmt.getInt(2);
System.out.println("The count = " + count);
}
catch (Exception e) {
e.printStackTrace();
}
finally {
myStmt.close();
myConn.close();
}
}
}
Code language: JavaScript (javascript)Conclusion
Stored procedures are very useful when it comes to repeated tasks that need to be done every day, these procedure or routines helps in shorting the time taken and hence increases efficiency. We can work with stored procedures using the Java programming language, and in this tutorial, we went through each and every section of the Java MySQL stored procedure.
For more details please visit the official website.