How to call MySQL stored procedure using Java

MySQL stored procedure is a user-defined function that encapsulates multiple SQL statements into a single repeatable execution unit. Calling stored procedures can improve database operation efficiency and security. As a mainstream development language for application development, Java can interact well with MySQL. In this article, we will learn how to call MySQL stored procedures using Java.

1. Preparation

Before we start, we need to ensure that the following two aspects of preparation have been completed.

1.1 MySQL database

First, you need to install and configure the MySQL database. You can download the installation package from the official website of MySQL and install and configure it according to the official documentation.

1.2 Java development environment

Secondly, you need to have a Java development environment, including JDK and development tools. There are many Java development tools, including Eclipse, IntelliJ IDEA, NetBeans, etc. This article will use Eclipse as an example for demonstration.

2. Create MySQL stored procedure

This article takes a simple MySQL stored procedure as an example. The stored procedure receives two parameters, adds them and returns the result.

First log in to the MySQL database and use the following statement to create a stored procedure named add_demo.

CREATE PROCEDURE add_demo (IN a INT, IN b INT, OUT c INT)
BEGIN
SET c = a + b;
END

After running the above statement, a stored procedure named add_demo is created in the MySQL database.

3. Java calls MySQL stored procedures

To call MySQL stored procedures in Java, you usually need to use the JDBC CallableStatement object.

3.1 Load the JDBC driver

In Java, you need to load the MySQL driver through the Class.forName() method, the code is as follows:

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

3.2 Connect to the MySQL database

Use DriverManager's getConnection() method to create a database connection object. The code is as follows:

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

Among them, url is the connection string of the database, username and password are the username and password to connect to the database.

3.3 Create a CallableStatement object

After connecting to the database, you need to create a CallableStatement object to execute the stored procedure. The code is as follows:

CallableStatement cstmt = conn.prepareCall("{call add_demo(?,?,?)}");

Among them, {call add_demo(?,?,?)} is the syntax for calling a stored procedure. Since the stored procedure has three parameters, three question marks are used to represent these three parameters.

3.4 Set parameters

After creating the CallableStatement object, you need to set the parameter values ​​of the stored procedure. The code is as follows:

cstmt.setInt(1, 1);
cstmt.setInt(2, 2);
cstmt.registerOutParameter(3, Types.INTEGER);

Among them, the setInt() method is used to set the first two parameters of the stored procedure, and the registerOutParameter() method is used to register the third parameter of the stored procedure. This parameter is an output parameter, indicating The return value type of the stored procedure execution result.

3.5 Execute the stored procedure

After setting the parameters, you can execute the stored procedure. The code is as follows:

cstmt.execute();

3.6 Obtain the execution result of the stored procedure

After executing the stored procedure, you need to obtain the execution result of the stored procedure. The code is as follows:

int result = cstmt.getInt(3);

getInt() method is used to obtain the value of the output parameter of the stored procedure.

4. Full sample code

The following is a complete Java example showing how to call a MySQL stored procedure.

import java.sql.*;

public class Demo {
  public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/test";
      String username = "root";
      String password = "123456";

      try {
          Class.forName("com.mysql.jdbc.Driver");
          Connection conn = DriverManager.getConnection(url, username, password);
          CallableStatement cstmt = conn.prepareCall("{call add_demo(?,?,?)}");
          cstmt.setInt(1, 1);
          cstmt.setInt(2, 2);
          cstmt.registerOutParameter(3, Types.INTEGER);
          cstmt.execute();
          int result = cstmt.getInt(3);
          System.out.println("Result: " + result);
      } catch (ClassNotFoundException | SQLException e) {
          e.printStackTrace();
      }
  }
}

Reference

1. [MySQL](https://www.mysql.com/).
2. [JDBC](https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html).

Conclusion

Java calling MySQL stored procedures is a common database operation method. It can improve the efficiency and security of database operations. In this article, we learned how to create MySQL stored procedures and call MySQL stored procedures using Java. This is crucial for developing efficient and reliable Java applications.

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