{"id":3502,"date":"2015-05-17T01:37:41","date_gmt":"2015-05-17T09:37:41","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3502"},"modified":"2024-01-11T02:17:49","modified_gmt":"2024-01-11T09:17:49","slug":"calling-mysql-stored-procedures-from-jdbc","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-jdbc-tutorial\/calling-mysql-stored-procedures-from-jdbc\/","title":{"rendered":"How to Call MySQL Stored Procedures from JDBC"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to call a stored procedure in MySQL from JDBC using CallableStatement<\/code> object.<\/p>\n\n\n\n

This tutorial picks up where the MySQL JDBC Transaction<\/a> tutorial left off.<\/p>\n\n\n\n

Calling a stored procedure from JDBC<\/h2>\n\n\n\n

The mysqljdbc<\/code> sample database has a stored procedure get_candidate_skill<\/code> that returns the skills of a candidate. It has a parameter candidate_id<\/code> that identifies a candidate.<\/p>\n\n\n\n

The following calls the get_candidate_skill<\/code> stored procedure get_candidate_skill<\/code> that returns the skills of the candidate with the id 133:<\/p>\n\n\n

 CALL<\/span> get_candidate_skill(133<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----+------------+-----------+-------+\n| id  | first_name | last_name | skill |\n+-----+------------+-----------+-------+\n| 133 | John       | Doe       | Java  |\n| 133 | John       | Doe       | JDBC  |\n| 133 | John       | Doe       | MySQL |\n+-----+------------+-----------+-------+\n3 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
To call stored procedures in JDBC, you use the CallableStatement<\/code> interface. The CallableStatement<\/code> interface is a subinterface of the PreparedStatement<\/code> interface.<\/p>\n\n\n\n
Instead of sending an SQL statement from JDBC to MySQL, you send a stored procedure call statement to MySQL.<\/p>\n\n\n\n
Here’s the basic syntax for calling a stored procedure in JDBC:<\/p>\n\n\n
{?= call procedure_name<\/span>(argument1,argument2,...)<\/span>}<\/span><\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you place the stored procedure call within the curly braces ({}<\/code>). <\/p>\n\n\n\n
If the stored procedure returns a value, you need to add the question mark and equal (?=<\/code>) before the call<\/code> keyword. <\/p>\n\n\n\n
If a stored procedure does not return any values, you can skip the ?=<\/code> sign. <\/p>\n\n\n\n
If the stored procedure accepts any parameters, you list them within the opening and closing parentheses ()<\/code> after the stored procedure’s name.<\/p>\n\n\n\n
The following are examples of using the syntax for calling stored procedures in different contexts:<\/p>\n\n\n\n
Syntax<\/th>Stores Procedures<\/th><\/tr><\/thead>
{  call procedure_name() }<\/td>Accept one parameter and return a value<\/td><\/tr>
{ call procedure_name(?,?) }<\/td>Accept two parameters and return no value<\/td><\/tr>
{?= call procedure_name() }<\/td>Accept no parameter and return a value<\/td><\/tr>
{?= call procedure_name(?) }<\/td>Accept one parameter and return a value<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Notice that the question mark placeholder (?<\/code>) can be used for both IN, OUT, and INOUT parameters. <\/p>\n\n\n\n
For detailed information on different parameter types for stored procedures, check the MySQL stored procedure parameters tutorial<\/a>.<\/p>\n\n\n\n
JDBC MySQL stored procedure example<\/h2>\n\n\n\n
The following defines the getSkills()<\/code> method in the Candidate class to get the skills of a candidate specified by the candidate id:<\/p>\n\n\n
import<\/span> java.sql.SQLException;\nimport<\/span> java.sql.Date;\nimport<\/span> java.sql.Statement;\nimport<\/span> java.util.ArrayList;\n\npublic<\/span> class<\/span> Candidate<\/span> <\/span>{\n\n    public<\/span> static<\/span> ArrayList<String> getSkills<\/span>(int<\/span> candidateId)<\/span> <\/span>{\n\n        var<\/span> query = \"{ call get_candidate_skill(?) }\"<\/span>;\n        var<\/span> skills = new<\/span> ArrayList<String>();\n\n        try<\/span> (var<\/span> conn = MySQLConnection.connect();\n             var<\/span> stmt = conn.prepareCall(query);\n             ) {\n\n            stmt.setInt(1<\/span>, candidateId);\n\n            try<\/span>(var<\/span> rs = stmt.executeQuery()){\n                while<\/span> (rs.next()) {\n                    skills.add(rs.getString(\"skill\"<\/span>));\n                }\n            } catch<\/span> (SQLException e) {\n                e.printStackTrace();\n            }\n\n        } catch<\/span> (SQLException e) {\n            e.printStackTrace();\n        }\n\n        return<\/span> skills;\n    }\n\n    \/\/ ...<\/span>\n\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the SQLException<\/code>, Date<\/code>, and Statement<\/code> classes from java.sql<\/code> package and ArrayList<\/code> from the java.util<\/code> package to handle SQL exceptions, work with dates, execute SQL statements, and manage lists:<\/p>\n\n\n
import<\/span> java.sql.SQLException;\nimport<\/span> java.sql.Date;\nimport<\/span> java.sql.Statement;\nimport<\/span> java.util.ArrayList;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define a method getSkills()<\/code> that takes an integer parameter candidateId<\/code> and returns an ArrayList<\/code> of strings representing the candidate’s skills:<\/p>\n\n\n
public<\/span> static<\/span> ArrayList<String> getSkills<\/span>(int<\/span> candidateId)<\/span> <\/span>{<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, construct a call to the get_candidate_skill<\/code> stored procedure that takes one parameter:<\/p>\n\n\n
var<\/span> query = \"{ call get_candidate_skill(?) }\"<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, create a new ArrayList of strings  to store the skills:<\/p>\n\n\n
var<\/span> skills = new<\/span> ArrayList<String>();<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, open a connect to the database and prepare the statement for execution:<\/p>\n\n\n
try<\/span> (var<\/span> conn = MySQLConnection.connect();\n     var<\/span> stmt = conn.prepareCall(query);\n) {<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the try-with-resources will automatically close the database connection and prepared statement.<\/p>\n\n\n\n
Sixth, set the parameter for the prepared statement and execute the query:<\/p>\n\n\n
stmt.setInt(1<\/span>, candidateId);\ntry<\/span> (var<\/span> rs = stmt.executeQuery()) {\n    \/\/ Process result set<\/span>\n} catch<\/span> (SQLException e) {\n    e.printStackTrace();\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, add the skill retrieved from the result set to the skills<\/code> array list inside the result set processing block;<\/p>\n\n\n
while<\/span> (rs.next()) {\n    skills.add(rs.getString(\"skill\"<\/span>));\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Eighth, display an error message if any SQL exceptions occur:<\/p>\n\n\n
} catch<\/span> (SQLException e) {\n    e.printStackTrace();\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return the candidate’s skills:<\/p>\n\n\n
return<\/span> skills;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
The following calls the getSkills()<\/code> method of the Candidate<\/code> class in the main()<\/code> method:<\/p>\n\n\n
public<\/span> class<\/span> Main<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> main<\/span>(String[] args)<\/span><\/span>{\n        var<\/span> skills = Candidate.getSkills(133<\/span>);\n        for<\/span> (var<\/span> skill: skills) {\n            System.out.println(skill);\n        }\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Java\nJDBC\nMySQL<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the JDBC CallableStatement<\/code> object to call a stored procedure in the MySQL database.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t