上一节:

  MyBatis 动态SQL

  • 动态SQL

    动态SQL是MyBatis的一项非常强大的功能。它使程序员能够根据场景动态构建查询。例如，如果要基于MyBatis中学生的姓名搜索Student数据库，则必须使用动态SQL编写查询。MyBatis使用功能强大的动态 SQL语言，可在任何映射的SQL语句中使用。以下是MyBatis提供的基于OGNL的动态SQL表达式。
    • if
    • choose (when, otherwise)
    • trim (where, set)
    • foreach

  • if语句

    动态SQL中最常见的操作是有条件地包含where子句的一部分。例如- 
    
<select id = "getRecByName" parameterType = "Student" resultType = "Student">

   SELECT * FROM STUDENT                 
   <if test = "name != null">
      WHERE name LIKE #{name}
   </if> 
</select>
    该语句提供了可选的文本搜索功能。如果您不输入名称，那么将返回所有活动记录。但是，如果你传递了一个名字，它会寻找与给定的名称类似的条件。您可以将多个if条件包括在内- 
    
<select id = "getRecByName_Id" parameterType = "Student" resultType = "Student">

   SELECT * FROM STUDENT                 
   <if test = "name != null">
      WHERE name LIKE #{name}
   </if>

   <if test = "id != null">
      AND id LIKE #{id}
   </if> 
</select>

  • where

    查看我们之前的示例，看看如果不满足任何条件会发生什么。您最终将得到一个看起来像这样的SQL 
    
SELECT * FROM Student
WHERE
    这将失败，但是MyBatis有一个简单的解决方案，只需进行一次简单的更改，一切就可以正常工作- 
    
<select id = "getName_Id_phone" parameterType = "Student" resultType = "Student">
   SELECT * FROM STUDENT
        
   <where>
      <if test = "id != null">
         id = #{id}
      </if>

      <if test = "name != null">
         AND name LIKE #{name}
      </if>
   </where>
                
</select>
    where元素只在包含标记返回任何内容时插入where。此外，如果内容以AND或or开头，它知道如何去掉它。

  • foreach

    使用foreach元素，您可以指定一个集合，并声明可以在该元素主体内使用的项目和索引变量。它还允许您指定打开和关闭字符串，并添加分隔符以放置在两次迭代之间。您可以按如下方式建立IN条件- 
    
<select id = "selectPostIn" resultType = "domain.blog.Post">
   SELECT *
   FROM POST P
   WHERE ID in
        
   <foreach item = "item" index = "index" collection = "list"
      open = "(" separator = "," close = ")">
      #{item}
   </foreach>
        
</select>

  • 动态SQL示例

    如果使用动态SQL，这是一个示例。考虑，我们在MySQL中有以下Student表- 
    
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`)
);
Query OK, 0 rows affected (0.37 sec)
    让我们假设这个表有两个记录为- 
    
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中有一个Student类，如下所示 
    
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.branch = branch;
      this.percentage = 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;
   }    
  
}

  • Student.xml文件

    该文件包含名为Student的结果映射，以映射SELECT查询的结果。我们将定义一个“id”，它将在mybatisRead.java中用于在数据库上执行动态SQL SELECT查询。 
    
<?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 = "getRecByName" parameterType = "Student" resultType = "Student">
      SELECT * FROM STUDENT              
                
      <if test = "name != null">
         WHERE name LIKE #{name}
      </if>
                
   </select>
        
</mapper>

  • GetRecordByName.java文件

    该文件具有应用程序级逻辑，可从Student表中读取条件记录- 
    
import java.io.IOException;
import java.io.Reader;
import java.util.List;

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 GetRecordByName { 

   public static void main(String args[]) throws IOException{
           
      String req_name = "Mohammad";
      Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);               
      SqlSession session = sqlSessionFactory.openSession();
      Student stud = new Student();
      stud.setName(req_name);
      
      //select contact all contacts             
      //List<Student> student = session.selectList("getRecByName",stud);
      
      stud.setId(1);
      List<Student> student = session.selectList("getRecByName_Id",stud);
          
      for(Student st : student ){         
                  
         System.out.println("++++++++++++++details of the student named Mohammad are "+"+++++++++++++++++++" );
          
         System.out.println("Id :  "+st.getId());
         System.out.println("Name :  "+st.getName());
         System.out.println("Branch :  "+st.getBranch());
         System.out.println("Percentage :  "+st.getPercentage());         
         System.out.println("Email :  "+st.getEmail());        
         System.out.println("Phone :  "+st.getPhone());           
         
      }     
      
      System.out.println("Records Read Successfully ");          
      session.commit();   
      session.close();                  
   }
}

  • 编译并运行

    以下是编译和运行getRecords程序的步骤。在继续进行编译和执行之前，请确保已正确设置了PATH和CLASSPATH。
    • 如上所示创建Student.xml。
    • 如本教程的MYBATIS-配置XML章节中所示，创建SqlMapConfig.xml 。
    • 如上所示创建Student.java并进行编译。
    • 如上所示创建GetRecordByName.java并进行编译。
    • 执行GetRecordByName二进制文件以运行程序。
    您将得到以下结果，并且将从Student表中读取一条记录。 
    
++++++++++++++details of the student named Mohammad are +++++++++++++++++++
Id :  1
Name :  Mohammad
Branch :  It
Percentage :  80
Email :  mohamad123@yahoo.com
Phone :  90000000
Records Read Successfully
上一节: