How to Call MySQL Stored Procedures from JDBC

Summary: in this tutorial, you will learn how to call a stored procedure in MySQL from JDBC using CallableStatement object.

This tutorial picks up where the MySQL JDBC Transaction tutorial left off.

Calling a stored procedure from JDBC

The mysqljdbc sample database has a stored procedure get_candidate_skill that returns the skills of a candidate. It has a parameter candidate_id that identifies a candidate.

The following calls the get_candidate_skill stored procedure get_candidate_skill that returns the skills of the candidate with the id 133:

 CALL get_candidate_skill(133);Code language: SQL (Structured Query Language) (sql)

Output:

+-----+------------+-----------+-------+
| id  | first_name | last_name | skill |
+-----+------------+-----------+-------+
| 133 | John       | Doe       | Java  |
| 133 | John       | Doe       | JDBC  |
| 133 | John       | Doe       | MySQL |
+-----+------------+-----------+-------+
3 rows in set (0.00 sec)Code language: plaintext (plaintext)

To call stored procedures in JDBC, you use the CallableStatement interface. The CallableStatement interface is a subinterface of the PreparedStatement interface.

Instead of sending an SQL statement from JDBC to MySQL, you send a stored procedure call statement to MySQL.

Here’s the basic syntax for calling a stored procedure in JDBC:

{?= call procedure_name(argument1,argument2,...)}Code language: Java (java)

In this syntax, you place the stored procedure call within the curly braces ({}).

If the stored procedure returns a value, you need to add the question mark and equal (?=) before the call keyword.

If a stored procedure does not return any values, you can skip the ?= sign.

If the stored procedure accepts any parameters, you list them within the opening and closing parentheses () after the stored procedure’s name.

The following are examples of using the syntax for calling stored procedures in different contexts:

Notice that the question mark placeholder (?) can be used for both IN, OUT, and INOUT parameters.

For detailed information on different parameter types for stored procedures, check the MySQL stored procedure parameters tutorial.

JDBC MySQL stored procedure example

The following defines the getSkills() method in the Candidate class to get the skills of a candidate specified by the candidate id:

import java.sql.SQLException;
import java.sql.Date;
import java.sql.Statement;
import java.util.ArrayList;

public class Candidate {

    public static ArrayList<String> getSkills(int candidateId) {

        var query = "{ call get_candidate_skill(?) }";
        var skills = new ArrayList<String>();

        try (var conn = MySQLConnection.connect();
             var stmt = conn.prepareCall(query);
             ) {

            stmt.setInt(1, candidateId);

            try(var rs = stmt.executeQuery()){
                while (rs.next()) {
                    skills.add(rs.getString("skill"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return skills;
    }

    // ...

}Code language: Java (java)

How it works.

First, import the SQLException, Date, and Statement classes from java.sql package and ArrayList from the java.util package to handle SQL exceptions, work with dates, execute SQL statements, and manage lists:

import java.sql.SQLException;
import java.sql.Date;
import java.sql.Statement;
import java.util.ArrayList;Code language: Java (java)

Second, define a method getSkills() that takes an integer parameter candidateId and returns an ArrayList of strings representing the candidate’s skills:

public static ArrayList<String> getSkills(int candidateId) {Code language: Java (java)

Third, construct a call to the get_candidate_skill stored procedure that takes one parameter:

var query = "{ call get_candidate_skill(?) }";Code language: Java (java)

Fourth, create a new ArrayList of strings to store the skills:

var skills = new ArrayList<String>();Code language: Java (java)

Fifth, open a connect to the database and prepare the statement for execution:

try (var conn = MySQLConnection.connect();
     var stmt = conn.prepareCall(query);
) {Code language: Java (java)

Note that the try-with-resources will automatically close the database connection and prepared statement.

Sixth, set the parameter for the prepared statement and execute the query:

stmt.setInt(1, candidateId);
try (var rs = stmt.executeQuery()) {
    // Process result set
} catch (SQLException e) {
    e.printStackTrace();
}Code language: Java (java)

Seventh, add the skill retrieved from the result set to the skills array list inside the result set processing block;

while (rs.next()) {
    skills.add(rs.getString("skill"));
}Code language: Java (java)

Eighth, display an error message if any SQL exceptions occur:

} catch (SQLException e) {
    e.printStackTrace();
}Code language: Java (java)

Finally, return the candidate’s skills:

return skills;Code language: Java (java)

The following calls the getSkills() method of the Candidate class in the main() method:

public class Main {
    public static void main(String[] args){
        var skills = Candidate.getSkills(133);
        for (var skill: skills) {
            System.out.println(skill);
        }
    }
}Code language: Java (java)

Output:

Java
JDBC
MySQLCode language: plaintext (plaintext)

Summary

• Use the JDBC CallableStatement object to call a stored procedure in the MySQL database.