How to call mysql stored procedure in java

With the continuous development and popularization of the Internet, there are more and more various types of websites and applications, and there are more and more scenarios involving database operations. In these scenarios, the use of stored procedures is very important. universal. As a relatively popular programming language, Java also has its own characteristics and advantages. How to call MySQL's stored procedure in Java? Let’s introduce the specific implementation method below.

1. Overview of Stored Procedures

Stored Procedure (Stored Procedure) is a pre-compiled set of SQL statements that are stored in the database and provide external interfaces for calling. Stored procedures can encapsulate some complex operations and provide them to applications, so that applications can be easily called and used, which reduces the coupling between applications and databases and improves the maintainability of applications. Stored procedures can be executed in the database and support transaction operations, which can effectively ensure data integrity and consistency.

2. Java calls stored procedures

In Java, you can use the CallableStatement object to call stored procedures. This object is a sub-interface of PreparedStatement and is specifically used to execute SQL calls to stored procedures. The CallableStatement object can be used to call the stored procedure, and at the same time, it can also obtain the return result after the stored procedure is executed.

The following is a sample code for Java to call a MySQL stored procedure:

public class Test {
    public static void main(String[] args) {
        Connection conn = null;
        CallableStatement call = null;
        ResultSet rs = null;

        try {
            // 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 建立数据库连接
            conn = DriverManager.getConnection(url, username, password);

            // 调用存储过程
            call = conn.prepareCall("{CALL 存储过程名(?,?)}");
            call.setString(1, "参数1");  // 设置输入参数
            call.registerOutParameter(2, Types.INTEGER);  // 设置输出参数
            call.execute();  // 执行存储过程

            // 获取输出参数
            int result = call.getInt(2);
            System.out.println("执行结果：" + result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

The key steps in the code are as follows:

1. Load the database driver.
2. Establish a database connection.
3. Use the prepareCall method to create a CallableStatement object and call the stored procedure.
4. Use the setXXX method to set input parameters.
5. Use the registerOutParameter method to set output parameters.
6. Use the execute method to execute the stored procedure.
7. Use the getXXX method to obtain the output parameters.
8. Close the database connection and related resources.

It should be noted that when calling a stored procedure, the input parameters must be set using the setXXX method, and the output parameters must be registered using the registerOutParameter method. When executing the stored procedure, use the execute method to execute and obtain the output. Parameters, use the getXXX method to obtain them.

3. Summary

Java can use the CallableStatement object to call the MySQL stored procedure. When calling the stored procedure, you need to set the input parameters and output parameters. When executing the stored procedure, you need to use the execute method to obtain You need to use the getXXX method when outputting parameters. The use of stored procedures can optimize database operation efficiency, reduce duplicate code, and improve application performance.

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!