How to call mysql stored procedure in java

As an object-oriented programming language, Java is a very common operation to interact with the database, and Mysql is one of the most widely used relational databases in the industry. In Mysql, a stored procedure is a powerful program unit that executes multiple SQL statements and returns results. Calling Mysql's stored procedure in Java is also a very common operation. Next, let's learn more about how to implement this process.

1. Creation of Mysql stored procedures

In Mysql, you can create a stored procedure through the following command:

CREATE PROCEDURE procedure_name [parameters]
BEGIN
   DECLARE local_variables;
   SQL_statements;
END;

Among them, procedure_name is the name of the stored procedure, and parameters are The input parameters of the stored procedure can be one or more, separated by commas. local_variables are the local variables used in the stored procedure, and SQL_statements are all SQL statements to be executed by the stored procedure. For details, you can check the official documentation of Mysql.

2. Steps to call Mysql stored procedure in Java

1. Prepare database connection

To call Mysql stored procedure in Java, you first need to prepare the database connection. You can use JDBC to obtain the Mysql connection object, as shown below:

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "username", "password");

It should be noted here that "com.mysql.jdbc.Driver" is the driver class name of Mysql, and you need to ensure that it has been imported in the project The corresponding driver jar package is available.

2. Call a stored procedure

To call a stored procedure in Java, you need to use CallableStatement, which represents the statement object that calls the stored procedure. The specific syntax is as follows:

CallableStatement cstmt = con.prepareCall("{call procedure_name(?, ?, ...)}");

Among them, procedure_name is the name of the stored procedure to be called, and within the brackets is the parameter list of the stored procedure. There can be one or more question marks, separated by commas. Assuming that the stored procedure has two parameters, the above statement should be modified as:

CallableStatement cstmt = con.prepareCall("{call procedure_name(?, ?)}");

3. Set parameters

To set the parameter value of the stored procedure, you can use the setXX() method, where XX means type of data. For example:

cstmt.setInt(1, 123);
cstmt.setString(2, "hello");

The first parameter indicates the position of the parameter, starting from 1; the second parameter is the value of the parameter.

4. Execute the stored procedure

After setting the parameters, you can execute the stored procedure through the execute() method:

cstmt.execute();

The execute() method will return a boolean type The value indicates whether the stored procedure was executed successfully.

5. Get results

If the stored procedure returns a result, you can obtain the result set through the getResultSet() method:

ResultSet rs = cstmt.getResultSet();
while (rs.next()) {
   // 处理结果集
}

If the stored procedure does not return a result, but If there are output parameters, you can get the value of the output parameter through the getXX() method:

int result = cstmt.getInt(1);

Among them, the first parameter still indicates the position of the parameter.

6. Close the connection

Finally, be sure to remember to close the database connection:

if (con != null) {
   con.close();
}

3. Sample code

The following is a complete sample code, Demonstrates how to call Mysql's stored procedure in Java:

import java.sql.*;

public class Main {
   public static void main(String[] args) {
      try {
         // 准备连接
         Class.forName("com.mysql.jdbc.Driver");
         Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "username", "password");

         // 调用存储过程
         CallableStatement cstmt = con.prepareCall("{call procedure_name(?, ?)}");

         // 设置参数
         cstmt.setInt(1, 123);
         cstmt.setString(2, "hello");

         // 执行存储过程
         boolean success = cstmt.execute();
         if (success) {
            // 处理结果集
            ResultSet rs = cstmt.getResultSet();
            while (rs.next()) {
               // 处理结果集
               int id = rs.getInt("id");
               String name = rs.getString("name");
            }
         } else {
            // 处理输出参数
            int result = cstmt.getInt(1);
         }

         // 关闭连接
         if (con != null) {
            con.close();
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

4. Notes

1. Make sure that the Mysql driver has been loaded and is available in the project;
2. The parameters of the stored procedure can be input parameters of the in type, output parameters of the out type, or input and output parameters of the inout type;
3. If the stored procedure has output parameters, it should be in Set the parameter type before calling the execute() method; if there are no output parameters, you do not need to set this item;
4. After executing the stored procedure, the CallableStatement and the connection must be closed.

The above is the detailed content of How to call mysql stored procedure in java. For more information, please follow other related articles on the PHP Chinese website!