Calling MySQL Stored Procedures from JDBC?

In this tutorial, you will learn how to call MySQL stored procedures from JDBC using CallableStatement object.

Before you start

For the sake of demonstration, we will create a new stored procedure named get_candidate_skill that accepts candidate_id as the IN parameter and returns a result set that contains the skills of the candidate.

1 2 3 4 5 6 7 8 9 10

DELIMITER $$ CREATE PROCEDURE get_candidate_skill(IN candidate_id INT) BEGIN SELECT candidates.id, first_name,last_name, skills.name AS skill FROM candidates INNER JOIN candidate_skills ON candidates.id = candidate_skills.candidate_id INNER JOIN skills ON skills.id = candidate_skills.skill_id WHERE candidates.id = candidate_id;     END$$ DELIMITER ;

Let’s call this stored procedure for candidate id with value 122.

1	CALL get_candidate_skill(122);

Introducing to CallableStatement and stored procedure call syntax

To call stored procedures or stored functions in MySQL from JDBC, you use CallableStatement object, which inherits from PreparedStatement object. The general syntax of calling a stored procedure is as follows:

1	{?= call procedure_name(param1,param2,...)}

You wrap the stored procedure call within 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 just omit the ?= sign. In case 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:

Syntax	Stores Procedures
{  call procedure_name() }	Accept no parameters and return no value
{ call procedure_name(?,?) }	Accept two parameters and return no value
{?= call procedure_name() }	Accept no parameter and return value
{?= call procedure_name(?) }	Accept one parameter and return value

Notice that question mark placeholder (?) can be used for both IN ,OUT, and INOUT parameters. For detailed information on different parameter types in stored procedures, check it out MySQL stored procedure parameters tutorial.

JDBC MySQL stored procedure example

First, open a connection to MySQL server by creating a new Connection object.

1	Connection conn = DriverManager.getConnection();

Then, prepare a stored procedure call and create a CallableStatement object by calling prepareCall()method of the Connection object.

1 2	String query = "{CALL get_candidate_skill(?)}"; CallableStatement stmt = conn.prepareCall(query)

Next, pass all the parameters to the stored procedure. In this case, the get_candidate_skill stored procedure accepts only one IN parameter.

1	stmt.setInt(1, candidateId);

After that, execute the stored procedure by calling the executeQuery() method of the CallableStatement object. It returns a result set in this case.

1	ResultSet rs = stmt.executeQuery();

Finally, traverse the ResultSet to display the results.

1 2 3 4 5 6

while (rs.next()) {    System.out.println(String.format("%s - %s",                       rs.getString("first_name") + " "                       + rs.getString("last_name"),                       rs.getString("skill"))); }

The following is the complete example of calling the MySQL stored procedure from JDBC.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

package org.mysqltutorial;   import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.CallableStatement;   /** * * @author mysqltutorial.org */ public class Main {       /**      * Get skills by candidate id      *      * @param candidateId      */     public static void getSkills(int candidateId) {         //         String query = "{ call get_candidate_skill(?) }";         ResultSet rs;           try (Connection conn = MySQLJDBCUtil.getConnection();                 CallableStatement stmt = conn.prepareCall(query)) {               stmt.setInt(1, candidateId);               rs = stmt.executeQuery();             while (rs.next()) {                 System.out.println(String.format("%s - %s",                         rs.getString("first_name") + " "                         + rs.getString("last_name"),                         rs.getString("skill")));             }         } catch (SQLException ex) {             System.out.println(ex.getMessage());         }     }       /**      *      * @param args      */     public static void main(String[] args) {         getSkills(122);     } }

Let’s run the program.

The program works as expected.

In this tutorial, we have shown you how to call a stored procedure in MySQL database from a Java program using JDBC CallableStatement object.