Let's talk about Java calling MySQL stored procedures

MySQL stored procedure is a precompiled block of SQL code that can be reused in an application. Stored procedures can be used to implement complex business logic, thereby helping developers improve application performance and maintainability. Calling MySQL stored procedures in Java applications is very simple and convenient. This article will introduce how Java calls MySQL stored procedures.

1. Create a stored procedure

First, we need to create a stored procedure in the MySQL database. The following is a simple example. This stored procedure will query a specific user and return the user's detailed information:

CREATE PROCEDURE get_user_info(IN user_id INT)
BEGIN
    SELECT * FROM user WHERE id = user_id;
END;

2. JDBC Connection Database

Next, Use JDBC API in Java application to connect to MySQL database. Make sure to include the MySQL JDBC driver in your project. The following is the sample code to connect to the MySQL database:

import java.sql.*;

public class MySQLConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost/mydatabase";
        String user = "root";
        String password = "mypassword";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to MySQL database");

            // ...
            // Invoke stored procedure here
            // ...

            con.close();
        } catch (Exception e) {
            System.err.println("Error: " + e.getMessage());
        }
    }
}

3. Calling the stored procedure

After completing the JDBC connection, we will call the MySQL stored procedure. The following is a sample code for calling a MySQL stored procedure using Java:

CallableStatement cs = con.prepareCall("{call get_user_info(?)}");
cs.setInt(1, 123);  // Set input parameter
ResultSet rs = cs.executeQuery();

if (rs.next()) {
    // Get output parameters here
}

In the above code, we use the CallableStatement object to execute the stored procedure. The first step is to prepare the SQL statement to be executed, which is a string containing question marks, representing the stored procedure name and parameter list. In this case, we are calling the stored procedure named get_user_info and passing the user ID as input parameter. In the second step, we set the value of the input parameter using the setInt method. The third step is to execute the stored procedure. This method will return a ResultSet object containing the result set of the stored procedure. Finally, we can use the ResultSet object to get the value of the output parameter.

4. Handling the results

Once we call the stored procedure, we can use the ResultSet object to process the results. The following is a sample code to read the results of a stored procedure:

while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    int age = rs.getInt("age");

    System.out.println("User ID: " + id);
    System.out.println("User Name: " + name);
    System.out.println("User Age: " + age);
}

In the above code, we use a while loop to iterate through the result set of the ResultSet object. For each row, we use the getInt method to get the value of the id column, the getString method to get the value of the name column, and the getInt method to get the value of the age column. Finally, we can display the output to the user.

Conclusion

Calling MySQL stored procedures in Java applications is quite simple and convenient. Using the JDBC API and the CallableStatement object, you can execute stored procedures and process the results. Stored procedures are powerful programming tools in the MySQL database that can help you write applications more efficiently.

The above is the detailed content of Let's talk about Java calling MySQL stored procedures. For more information, please follow other related articles on the PHP Chinese website!