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