Our commonly used operating database language SQL statements need to be compiled first and then executed when executing. Stored Procedure is a set of SQL statements to complete specific functions. After compilation, it is stored in the database. The user calls and executes it by specifying the name of the stored procedure 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 very useful when you want to execute the same function on different applications or platforms, or encapsulate specific functions. Stored procedures in databases can be regarded as simulations of object-oriented methods in programming. It allows control over how data is accessed.
1. Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written in flow control statements, which have strong flexibility and can complete complex judgments and more complex operations.
2. Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. Moreover, database professionals can modify stored procedures at any time without any 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 respectively, then stored procedures are much faster than batch processing. Because stored procedures are precompiled. When a stored procedure is first run, the optimizer analyzes and optimizes it and gives the execution plan that is ultimately stored in the system table. The batch processing Transaction-SQL statements must be compiled and optimized every time they run, which is relatively slow.
4. Stored procedures can reduce network traffic by over-the-counter. For operations (such as query, modification) of the same database object, if the Transaction-SQL statement involved in this operation is stored by the organization process, then when the stored procedure is called on the client computer, only the call statement is transmitted in the network, thereby greatly increasing network traffic and reducing network load.
5. Stored procedures can be fully utilized as a security mechanism. System administrators can restrict access rights to corresponding data by executing permissions to a certain stored procedure, avoiding access to data by unauthorized users and ensuring data security.
1. Not easy to maintain, once the logic changes, it is troublesome to modify it
2. If the person who wrote this stored procedure leaves, it is probably a disaster for the person who took over her code, because others still have to understand your program logic and your storage logic. Not conducive to expansion.
3. The biggest disadvantage! Although stored procedures can reduce the amount of code and improve development efficiency. But one thing is very fatal, it is too performance-consuming.
The following is a code to introduce the MYSQL stored procedure in mybatis;
##1. There is student table student (id, name, age, money)##2. Create a stored procedure for querying student table information: delimiter | create Procedure showAllstu() BEGIN SELECT * FROM student ORDER BY id DESC LIMIT 6; ENDdelimiter##2. Create a stored procedure for deleting records (through student id): ```delimiter | create Procedure delById(d int(11)) BEGIN delete from student where id=d; ENDdelimiter##3. Create a project in maven: (omitted) //pox.xml Configuration: <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.metar</groupId> <artifactId>Mybatis-mysql</artifactId> <packaging>war</packaging> <version>1.0</version> <name>Mybatis-mysql Maven Webapp</name> <url>http://maven.apache.org</url> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.20</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> <scope>test</scope> </dependency> </dependencies> <build> <finalName>${project.artifactId}</finalName> <testSourceDirectory>src/test/java</testSourceDirectory> <sourceDirectory>src/main/java</sourceDirectory> <!-- Handling the resource configuration file cannot be loaded--> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> <include>**/*.properties</include> </includes> </resource> <resource> <directory>src/main/resources</directory> <includes> <include>**/*.xml</include> <include>**/*.properties</include> </resource> </build> </project>##4. Link database entry configuration (omitted), configuration: mybatis-config.xml: <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- This is the configuration portal for the resource database --> <properties resource="db.properties"/> <!-- Turn on log settings --> <settings> <setting name="logPrefix" value="dao."/> </settings> <typeAlias> <!-- Configure model class alias--> <!-- <typeAlias type="com.fz.entity.Student" alias="st"/> --> <!-- Configure all the aliases under the specified package // The model class small-character alias under the com.fz.entity package Book.java book is the alias --> <package name="com.fz.entity"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${db.driver}"/> <property name="url" value="${db.url}"/> <property name="username" value="${db.user}"/> <property name="password" value="${db.password}"/> </dataSource> </environment> </environments> <mappers> <!--<mapper/>--> <package name="com.fz.mapper"/> </mappers> </configuration>##5. Create entity class object: // Package: com/fz/entity/Student @Data public class Student { private int id; private String name; private int age; private double money; } ##6. Create StudentMapper interface class and StudentMapper.xml configuration; // StudentMapper interface StudentMapper { //Stored procedure query 6 records; public List<Student> query(); //Stored procedure deletes a record (by id) public int delById(int id); } //StudentMapper.xml configuration```` <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.fz.mapper.StudentMapper"> <select id="query" resultType="student"> {call showAllstu()} </select> <delete id="delById" parameterType="int"> {call delById(#{id})} </delete> </mapper> ##7. Test class: //test/java/com/Demo01 package com; import com.fz.entity.Student; import com.fz.mapper.StudentMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class Demo01 { protected SqlSessionFactory sf; protected SqlSession ss; @Test public void test(){ StudentMapper sdd = this.ss.getMapper(StudentMapper.class); List<Student> atd = sdd.query(); for (Student sd:atd){ System.out.println(sd); } sdd.delById(18); } @Before public void init(){ InputStream is=null; try { is= Resources.getResourceAsStream("mybatis-config.xml"); this.sf=new SqlSessionFactoryBuilder().build(is); this.ss=this.sf.openSession(); } catch (IOException e) { e.printStackTrace(); } } @After public void close(){ this.ss.commit(); this.ss.close(); } }Replenish:
Let's see the syntax of stored procedures
1 Create a stored procedure
create procedure sp_name() begin.........end
2 Calling stored procedures
call sp_name()
Note: The stored procedure name must be followed by brackets, even if the stored procedure has no parameters passed.
3 Delete stored procedures
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 basic information of all stored procedures stored in the database, including the database to which it belongs, the name of the stored procedure, creation time, etc.
show create procedure sp_name
Displays detailed information of a MySQL stored procedure
Summarize
The above is the method of using Mysql stored procedures in Mybatis introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!