About the method of using MySQL stored procedure in mybatis

1. Introduction to stored procedures

The SQL statement of our commonly used operation database language needs to be compiled before execution, and the stored procedure is a set of SQL statements to complete specific functions, which are compiled and stored in the database. Users call and execute the stored procedure by specifying its name and giving parameters (if the stored procedure has parameters).

A stored procedure is a programmable function that is created and saved in a database. It can be composed of SQL statements and some special control structures. Stored procedures are useful when you want to execute the same functions on different applications or platforms, or encapsulate specific functions. The stored procedure in the database can be regarded as a simulation of the object-oriented method in programming. It allows you to control how data is accessed.

2. Advantages of stored procedures

1. Stored procedures enhance the function and flexibility of SQL language. Stored procedures can be written with flow control statements, have strong flexibility, and can complete complex judgment and more complex operations.

2. Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called many times in the program without having to rewrite the SQL statement of the stored procedure. Moreover, database professionals can modify the stored procedure at any time, which has no impact on the application source code.

3. Stored procedures can achieve faster execution speed. If an operation contains a large amount of transaction SQL code or is executed multiple times, the execution speed of the stored procedure is much faster than that of batch processing. Because stored procedures are precompiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives the execution plan finally stored in the system table. The batch transaction SQL statements need to be compiled and optimized every time they run, and the speed is relatively slow.

4. Stored procedures can reduce network traffic. For the operation (such as query and modification) of the same database object, if the transaction SQL statement involved in this operation is organized into a stored procedure, when the stored procedure is called on the client computer, only the call statement is transmitted in the network, which greatly increases the network traffic and reduces the network load.

5. Stored procedures can be fully utilized as a security mechanism. The system administrator can restrict the access right of the corresponding data by restricting the permission of executing a stored procedure, avoiding the access of unauthorized users to the data and ensuring the security of the data.

3. Disadvantages of stored procedures

1. It is not easy to maintain. Once the logic changes, it is troublesome to modify

2. If the person who wrote this stored procedure leaves, it will be a disaster for the person who took over her code, because others have to understand your program logic and your storage logic. It is not conducive to expansion.

3. The biggest disadvantage! Although stored procedures can reduce the amount of code and improve development efficiency. But one fatal thing is that it consumes too much performance.

The following code describes the use of MySQL stored procedures in mybatis;

Supplement:

Let's look at the syntax of stored procedures

1 create stored procedure

2 call stored procedure

call sp_ name()

Note: the stored procedure name must be followed by parentheses, even if the stored procedure has no parameters passed

3 delete stored procedure

drop procedure sp_ name//

Note: you cannot delete another stored procedure in one stored procedure. You can only call another stored procedure

4 other common commands

show procedure status

Displays the basic information of all stored procedures in the database, including the database, stored procedure name, creation time, etc

show create procedure sp_ name

Displays the details of a MySQL stored procedure

summary

The above is the method of using MySQL stored procedures in mybatis introduced by Xiaobian. I hope it will be helpful to you. If you have any questions, please leave me a message, and Xiaobian will reply to you in time. Thank you very much for your support for the programming tips website!