Detailed explanation of examples of calling MySQL stored procedures through Mybatis

This article mainly introduces the implementation of MySQL stored procedures called through Mybatis, which has certain reference value. Interested friends can refer to it.

1. Introduction to stored procedures

Our commonly used operating database language SQL statements need to be compiled first and then executed when executed, while stored procedures (Stored Procedure ) is a set of SQL statements designed to accomplish specific functions. They are compiled and stored in the database. The user calls and executes the stored procedure 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 the database. It can consist of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same function on different applications or platforms, or encapsulate specific functionality. Stored procedures in a database can be seen as a simulation of the object-oriented approach in programming. It allows control over how data is accessed.

2. Advantages of stored procedures

1. Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements, are highly flexible, 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. And database professionals can modify stored procedures at any time without affecting 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 stored procedure will execute much faster than batch processing. Because stored procedures are precompiled. When a stored procedure is run for the first time, the query is analyzed and optimized by the optimizer and an execution plan is finally stored in the system table. The batch Transaction-SQL statement must be compiled and optimized every time it is run, and the speed is relatively slower.

4. Stored procedures can reduce network traffic. For operations on the same database object (such as query, modification), if the Transaction-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client computer, only the call is transmitted over the network statements, thereby greatly increasing network traffic and reducing network load.

5. Stored procedures can be fully utilized as a security mechanism. By restricting the permissions for executing a certain stored procedure, the system administrator can limit the access permissions of the corresponding data, avoid unauthorized users from accessing the data, and ensure the security of the data.

3. Disadvantages of stored procedures

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

2. If the person who wrote this stored procedure resigns, it will probably be a disaster for the person who takes over her code, because others will have to understand your program logic and your storage logic. Not conducive to expansion.

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

4. Syntax of stored procedure

4.1 Create stored procedure

create procedure sp_name()
begin
.........
end

4.2 Call stored procedure

call sp_name()

Note: Parentheses must be added after the stored procedure name, even if the stored procedure has no parameters to pass

4.3 Delete stored procedures

drop procedure sp_name//

Note: You cannot delete another stored procedure in one stored procedure. Only another stored procedure can be called

4.4 Other common commands

show procedure status

Display the basic information of all stored procedures in the database, including the database to which it belongs, the name of the stored procedure, the creation time, etc.

show create procedure sp_name

Display detailed information of a certain MySQL stored procedure

5. Case implementation of MyBatis calling MySQL stored procedure

5.1 Brief description of the case

The case is mainly implemented by simply counting the total number of devices with a certain name.

5.2 Creation of database table

DROP TABLE IF EXISTS `cus_device`;
CREATE TABLE `cus_device` (
 `device_sn` varchar(20) NOT NULL COMMENT '设备编号',
 `device_cat_id` int(1) DEFAULT NULL COMMENT '设备类型',
 `device_name` varchar(64) DEFAULT NULL COMMENT '设备名称',
 `device_type` varchar(64) DEFAULT NULL COMMENT '设备型号',
 PRIMARY KEY (`device_sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.3 Creation of stored procedure

Take the name of the device as the input parameter and the total number of counted devices as the output parameter

DROP PROCEDURE IF EXISTS `countDevicesName`;
DELIMITER ;;
CREATE PROCEDURE `countDevicesName`(IN dName VARCHAR(12),OUT deviceCount INT)
BEGIN
SELECT COUNT(*) INTO deviceCount FROM cus_device WHERE device_name = dName;

END
;;
DELIMITER ;

5.4 Mybatis calls MySQL stored procedures

1. 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> 

  <settings>
  <!-- 打印查询语句 -->
    <setting name="logImpl" value="STDOUT_LOGGING" />
  </settings>
  <!-- 配置别名 --> 
  <typeAliases> 
    <typeAlias type="com.lidong.axis2demo.DevicePOJO" alias="DevicePOJO" />  
  </typeAliases> 

  <!-- 配置环境变量 --> 
  <environments default="development"> 
    <environment id="development"> 
      <transactionManager type="JDBC" /> 
      <dataSource type="POOLED"> 
        <property name="driver" value="com.mysql.jdbc.Driver" /> 
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/bms?characterEncoding=GBK" /> 
        <property name="username" value="root" /> 
        <property name="password" value="123456" /> 
      </dataSource> 
    </environment> 
  </environments> 

  <!-- 配置mappers --> 
  <mappers> 
    <mapper resource="com/lidong/axis2demo/DeviceMapper.xml" /> 
  </mappers> 

</configuration>

2. CusDevice.java

public class DevicePOJO{

   private String devoceName;//设备名称
   private String deviceCount;//设备总数


  public String getDevoceName() {
    return devoceName;
  }
  public void setDevoceName(String devoceName) {
    this.devoceName = devoceName;
  }
  public String getDeviceCount() {
    return deviceCount;
  }
  public void setDeviceCount(String deviceCount) {
    this.deviceCount = deviceCount;
  }
}

3. Implementation of DeviceDAO

package com.lidong.axis2demo;

public interface DeviceDAO {

  /**
   * 调用存储过程 获取设备的总数
   * @param devicePOJO
   */
  public void count(DevicePOJO devicePOJO);

}

4.Mapper implementation

<?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.lidong.axis2demo.DeviceDAO">

  <resultMap id="BaseResultMap" type="CusDevicePOJO">
    <result column="device_sn" property="device_sn" jdbcType="VARCHAR" />
  </resultMap>

  <sql id="Base_Column_List">
    device_sn, device_name,device_mac
  </sql>

  <select id="count" parameterType="DevicePOJO" useCache="false"
    statementType="CALLABLE"> 
    <![CDATA[ 
    call countDevicesName(
    #{devoceName,mode=IN,jdbcType=VARCHAR},
    #{deviceCount,mode=OUT,jdbcType=INTEGER});
    ]]>
  </select>
</mapper>

Note: statementType="CALLABLE" must be CALLABLE, telling MyBatis to execute the stored procedure, otherwise an error will be reported
Exception in thread "main" org.apache.ibatis. exceptions.PersistenceException

mode=IN The input parameter mode=OUT and the output parameter jdbcType is the field type defined by the database.
Writing like this Mybatis will help us automatically backfill the output deviceCount value.

5. Test

package com.lidong.axis2demo;

import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
 * MyBatis 执行存储过程
 * @author Administrator
 *
 */
public class TestProduce {

  private static SqlSessionFactoryBuilder sqlSessionFactoryBuilder; 
  private static SqlSessionFactory sqlSessionFactory; 
  private static void init() throws IOException { 
    String resource = "mybatis-config.xml"; 
    Reader reader = Resources.getResourceAsReader(resource); 
    sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); 
    sqlSessionFactory = sqlSessionFactoryBuilder.build(reader); 
  } 

  public static void main(String[] args) throws Exception {
    testCallProduce();
  }

  /**
   * @throws IOException
   */
  private static void testCallProduce() throws IOException {
    init();
    SqlSession session= sqlSessionFactory.openSession(); 
    DeviceDAO deviceDAO = session.getMapper(DeviceDAO.class); 
    DevicePOJO device = new DevicePOJO(); 
    device.setDevoceName("设备名称"); 
    deviceDAO.count(device);
    System.out.println("获取"+device.getDevoceName()+"设备的总数="+device.getDeviceCount());
  }

}

Result

The above is the detailed content of Detailed explanation of examples of calling MySQL stored procedures through Mybatis. For more information, please follow other related articles on the PHP Chinese website!