How to call MySQL stored procedure in Java program

Java is a widely used programming language. When developing web applications, it is often necessary to call MySQL stored procedures to process data. This article will introduce in detail how to call MySQL stored procedures in Java programs.

1. MySQL stored procedures

MySQL stored procedures are a collection of SQL codes that can encapsulate complex SQL queries and data operations and be called when needed. Stored procedures usually execute faster than ordinary SQL statements.

MySQL stored procedures can be created through the MySQL client or management tools, such as MySQL Workbench. The method of creating a stored procedure is similar to writing a SQL statement, but you need to use the DELIMITER command to specify the terminator. For example, the following code creates a simple stored procedure:

DELIMITER //
CREATE PROCEDURE GetProductList()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;

In the above code, DELIMITER specifies the end character as "//", the stored procedure name is GetProductList, and the SELECT statement is from the table "products" Get all data.

2. Calling MySQL stored procedures in Java

Calling MySQL stored procedures is similar to calling ordinary SQL statements and requires the use of Java JDBC API. Following are some basic steps to call MySQL stored procedures using JDBC API.

1. Loading the JDBC driver

When using the JDBC API in a Java program, the MySQL JDBC driver must be loaded first. You can use the Class.forName() method to load the driver, or register the driver through the JDBC driver jar package. For example:

Class.forName("com.mysql.cj.jdbc.Driver");

2. Establishing a database connection

To establish a database connection, you need to specify the server address, database name, user name, password and other information. A database connection can be established through the getConnection() method of the DriverManager class, for example:

String url = "jdbc:mysql://localhost:3306/mydb";
String user = "myuser";
String password = "mypassword";

Connection conn = DriverManager.getConnection(url, user, password);

3. Creating a CallableStatement object

Calling a MySQL stored procedure requires the use of a CallableStatement object, which can be passed Created by Connection.prepareCall() method. For example:

String call = "{call GetProductList()}";
CallableStatement stmt = conn.prepareCall(call);

Note that the stored procedure name needs to be enclosed in curly brackets "{}" and prefixed with "{call}".

4. Set parameters and execute stored procedures

Before calling the MySQL stored procedure, you need to set the input and output parameters (if any). Input parameters are set using the setXXX() method, and output parameters are registered using the registerOutParameter() method after calling the procedure. To execute a stored procedure use the CallableStatement.execute() or CallableStatement.executeQuery() method. For example:

ResultSet rs = stmt.executeQuery();
while (rs.next()) {
    // 处理查询结果
}

5. Close the connection and release resources

After using the database connection and CallableStatement object, you should close the connection and release the resources in time to avoid resource waste and memory leaks .

The complete Java code example for calling MySQL stored procedures is as follows:

import java.sql.*;

public class CallStoredProcedure {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://localhost:3306/mydb";
            String user = "myuser";
            String password = "mypassword";

            Connection conn = DriverManager.getConnection(url, user, password);

            String call = "{call GetProductList()}";
            CallableStatement stmt = conn.prepareCall(call);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                // 处理查询结果
            }

            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3. Summary

Through the introduction of this article, readers can learn to call MySQL stored procedures in Java programs Basic steps and considerations for the process. Using stored procedures can improve SQL execution efficiency and data processing performance, and has great practical value for complex web application development.

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