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.
- 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.
- 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.
- 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.
- 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
- [MySQL](https://www.mysql.com/).
- [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!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Securing MySQL Connections with SSL/TLS Encryption
Jul 21, 2025 am 02:08 AM
Why do I need SSL/TLS encryption MySQL connection? Because unencrypted connections may cause sensitive data to be intercepted, enabling SSL/TLS can prevent man-in-the-middle attacks and meet compliance requirements; 2. How to configure SSL/TLS for MySQL? You need to generate a certificate and a private key, modify the configuration file to specify the ssl-ca, ssl-cert and ssl-key paths and restart the service; 3. How to force SSL when the client connects? Implemented by specifying REQUIRESSL or REQUIREX509 when creating a user; 4. Details that are easily overlooked in SSL configuration include certificate path permissions, certificate expiration issues, and client configuration requirements.
how to connect excel to mysql database
Jul 16, 2025 am 02:52 AM
There are three ways to connect Excel to MySQL database: 1. Use PowerQuery: After installing the MySQLODBC driver, establish connections and import data through Excel's built-in PowerQuery function, and support timed refresh; 2. Use MySQLforExcel plug-in: The official plug-in provides a friendly interface, supports two-way synchronization and table import back to MySQL, and pay attention to version compatibility; 3. Use VBA ADO programming: suitable for advanced users, and achieve flexible connections and queries by writing macro code. Choose the appropriate method according to your needs and technical level. PowerQuery or MySQLforExcel is recommended for daily use, and VBA is better for automated processing.
Choosing appropriate data types for columns in MySQL tables
Jul 15, 2025 am 02:25 AM
WhensettingupMySQLtables,choosingtherightdatatypesiscrucialforefficiencyandscalability.1)Understandthedataeachcolumnwillstore—numbers,text,dates,orflags—andchooseaccordingly.2)UseCHARforfixed-lengthdatalikecountrycodesandVARCHARforvariable-lengthdata
Automating MySQL Deployments with Infrastructure as Code
Jul 20, 2025 am 01:49 AM
To achieve MySQL deployment automation, the key is to use Terraform to define resources, Ansible management configuration, Git for version control, and strengthen security and permission management. 1. Use Terraform to define MySQL instances, such as the version, type, access control and other resource attributes of AWSRDS; 2. Use AnsiblePlaybook to realize detailed configurations such as database user creation, permission settings, etc.; 3. All configuration files are included in Git management, support change tracking and collaborative development; 4. Avoid hard-coded sensitive information, use Vault or AnsibleVault to manage passwords, and set access control and minimum permission principles.
Setting up semi-synchronous replication in MySQL
Jul 15, 2025 am 02:35 AM
The steps for setting MySQL semi-synchronous replication are as follows: 1. Confirm the version supports and load the plug-in; 2. Turn on and enable semi-synchronous mode; 3. Check the status and operation status; 4. Pay attention to timeout settings, multi-slave library configuration and master-slave switching processing. It is necessary to ensure that MySQL 5.5 and above versions are installed, rpl_semi_sync_master and rpl_semi_sync_slave plugins, enable corresponding parameters in the master and slave library, and configure automatic loading in my.cnf, restart the service after the settings are completed, check the status through SHOWSTATUS, reasonably adjust the timeout time and monitor the plug-in operation.
mysql incorrect string value for column
Jul 15, 2025 am 02:40 AM
MySQL error "incorrectstringvalueforcolumn" is usually because the field character set does not support four-byte characters such as emoji. 1. Cause of error: MySQL's utf8 character set only supports three-byte characters and cannot store four-byte emoji; 2. Solution: Change the database, table, fields and connections to utf8mb4 character set; 3. Also check whether the configuration files, temporary tables, application layer encoding and client drivers all support utf8mb4; 4. Alternative solution: If you do not need to support four-byte characters, you can filter special characters such as emoji at the application layer.
mysql revoke privileges from user
Jul 16, 2025 am 03:56 AM
To recycle MySQL user permissions using REVOKE, you need to specify the permission type, database, and user by format. 1. Use REVOKEALLPRIVILEGES, GRANTOPTIONFROM'username'@'hostname'; 2. Use REVOKEALLPRIVILEGESONmydb.FROM'username'@'hostname'; 3. Use REVOKEALLPRIVILEGESONmydb.FROM'username'@'hostname'; 3. Use REVOKE permission type ON.*FROM'username'@'hostname'; Note that after execution, it is recommended to refresh the permissions. The scope of the permissions must be consistent with the authorization time, and non-existent permissions cannot be recycled.
how to create pivot table in mysql
Jul 21, 2025 am 01:47 AM
Methods that implement Excel pivot table functions similar to MySQL mainly include using CASE or IF statements to combine aggregate functions for row conversion. 1. Use CASEWHEN to realize static row-to-column conversion, which is suitable for situations where column values are known to be converted. New columns are generated for different values and data are summarized through SUM (CASEWHEN...). 2. Generate columns dynamically, suitable for situations where specific values are uncertain. You need to obtain a unique value before constructing a CASE expression. Usually, it is combined with stored procedures or application layer logic to splice and execute SQL strings; 3. Use IF functions to simplify syntax to achieve the same effect as CASE but the writing method is more compact. In actual applications, if the dimension is fixed, the column can be hard-coded directly. If the dimension changes frequently, it is recommended to use scripts or store them.
