MyBatis 存储过程



  • 存储过程

    您可以使用MyBatis调用存储过程。首先,让我们了解如何在MySQL中创建存储过程。
    我们在MySQL中有以下EMPLOYEE表-
    
    CREATE TABLE details.student(
       ID int(10) NOT NULL AUTO_INCREMENT,
       NAME varchar(100) NOT NULL,
       BRANCH varchar(255) NOT NULL,
       PERCENTAGE int(3) NOT NULL,
       PHONE int(11) NOT NULL,
       EMAIL varchar(255) NOT NULL,
       PRIMARY KEY (`ID`)
    );
    
    
    让我们在MySQL数据库中创建以下存储过程-
    
    DELIMITER //
       DROP PROCEDURE IF EXISTS details.read_recordById //
       CREATE PROCEDURE details.read_recordById (IN emp_id INT)
            
       BEGIN 
          SELECT * FROM STUDENT WHERE ID = emp_id; 
       END// 
            
    DELIMITER ;
    
    
    假设名为STUDENT的表具有两个记录,分别为-
    
    mysql> select * from STUDENT;
    +----+----------+--------+------------+-----------+----------------------+
    | ID |   NAME   | BRANCH | PERCENTAGE |  PHONE    |       EMAIL          |
    +----+----------+--------+------------+-----------+----------------------+
    |  1 | Mohammad |  It    |     80     | 900000000 | mohamad123@yahoo.com |
    |  2 | Shyam    |  It    |     75     | 984800000 | shyam@gmail.com      |
    +----+----------+--------+------------+-----------+----------------------+
    2 rows in set (0.00 sec)
    
    
  • Student POJO类

    要使用存储过程,您无需修改​​Student.java文件。让我们保持上一章的样子。
    
    public class Student {
    
       private int id;
       private String name;
       private String branch;
       private int percentage;
       private int phone;
       private String email;
    
       public Student(int id, String name, String branch, int percentage, int phone, String email) {
          super();
          this.id = id;
          this.name = name;
          this.setBranch(branch);
          this.setPercentage(percentage);
          this.phone = phone;
          this.email = email;
       }
            
       public Student() {}
    
       public int getId() {
          return id;
       }
            
       public void setId(int id) {
          this.id = id;
       }
            
       public String getName() {
          return name;
       }
            
       public void setName(String name) {
          this.name = name;
       }
            
       public int getPhone() {
          return phone;
       }
            
       public void setPhone(int phone) {
          this.phone = phone;
       }
            
       public String getEmail() {
          return email;
       }
            
       public void setEmail(String email) {
          this.email = email;
       }
    
       public String getBranch() {
          return branch;
       }
    
       public void setBranch(String branch) {
          this.branch = branch;
       }
    
       public int getPercentage() {
          return percentage;
       }
    
       public void setPercentage(int percentage) {
          this.percentage = percentage;
       }
            
       public String toString(){
          StringBuilder sb = new StringBuilder();
                    
          sb.append("Id = ").append(id).append(" - ");
          sb.append("Name = ").append(name).append(" - ");
          sb.append("Branch = ").append(branch).append(" - ");
          sb.append("Percentage = ").append(percentage).append(" - ");
          sb.append("Phone = ").append(phone).append(" - ");
          sb.append("Email = ").append(email);
                    
          return sb.toString();
       }
            
    }
    
    
  • Student.xml文件

    与CURD不同,MyBatis中没有<procedure>标记。为了映射过程的结果,我们创建了一个名为Student的结果映射,并调用了名为read_recordById的存储过程。我们已经定义了一个id为callById的select标记,并且在应用程序中使用相同的ID来调用该过程。
    
    <?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 = "Student">    
                    
       <resultMap id = "result" type = "Student">
          <result property = "id" column = "ID"/>
          <result property = "name" column = "NAME"/>
          <result property = "branch" column = "BRANCH"/>
          <result property = "percentage" column = "PERCENTAGE"/>
          <result property = "phone" column = "PHONE"/>
          <result property = "email" column = "EMAIL"/>
       </resultMap>   
       
       <select id = "callById" resultMap = "result" parameterType = "Student" statementType = "CALLABLE">
          {call read_record_byid(#{id, jdbcType = INTEGER, mode = IN})}
       </select>   
            
    </mapper>
    
    
  • mybatisSP.java文件

    该文件具有应用程序级逻辑,可使用ResultMap从Employee表读取雇员的姓名-
    
    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;
    
    public class getRecords { 
    
       public static void main(String args[]) throws IOException{
          
          Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
          SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);               
          SqlSession session = sqlSessionFactory.openSession();
          
          //select a particular student  by  id     
          Student student = (Student) session.selectOne("Student.callById", 3);
          
          //Print the student details
          System.out.println("Details of the student are:: ");
          System.out.println("Id :"+student.getId());
          System.out.println("Name :"+student.getName());
          System.out.println("Branch :"+student.getBranch());
          System.out.println("Percentage :"+student.getPercentage());      
          System.out.println("Email :"+student.getEmail());      
          System.out.println("Phone :"+student.getPhone());
          session.commit();
          session.close();
                            
       }
       
    }
    
    
  • 编译并运行

    以下是编译和运行getRecords程序的步骤。在继续进行编译和执行之前,请确保已正确设置了PATH和CLASSPATH。
    • 如上所示创建Student.xml。
    • 如本教程的MYBATIS-配置XML章节中所示,创建SqlMapConfig.xml 。
    • 如上所示创建Student.java并进行编译。
    • 如上所示创建getRecords.java并进行编译。
    • 执行getRecords二进制文件以运行程序。
    您将获得以下结果-
    
    Details of the student are:: 
    Id :2
    Name :Shyam
    Branch :It
    Percentage :75
    Email :shyam@gmail.com
    Phone :984800000