上一节:
下一节:

  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
上一节:
下一节: