MyBatis实验（三）——动态SQL

前言

在编译阶段，就可以确定的SQL语句称为静态SQL；在程序运行阶段，根据条件不同才能确定下来的SQL叫动态SQL。如：学生表数据查询时，查询条件有年龄、姓名、专业、班级等，SQL语句条件根据当前的使用场景可能是这些条件的任意组合。Mybaits提供了一种基于OGNL（对象导航图语言）的表达式来完成动态SQL，极大方便了动态sql编程。

实验目的

掌握基于OGNL表达式实现动态SQL的使用

实验内容

以查询学生信息为例，学习if、choose（when、otherwise）、where、trim、set、forEach、bind等元素的使用。

实验步骤

1. 创建学生表及学生信息维护，学生表结构如下：

给表中添加不少于10条数据，以备测试使用。

2. 使用maven创建控制台工程和环境搭建（参照实验2.1）

3. 实验准备

• 创建实体类，代码如下：

@Data
public class Student {  
    private Integer sno;  
    private String studentName;  
    private Integer studentAge; 
} 
1
2
3
4
5
6

• 创建接口文件

public interface StudentDao {
    List<StudentEntity> getStudentByCondition(Map<String,Object> param);
    List<StudentEntity> getStudentByCondition2(Map<String,Object> param);
    int UpdateStudentById(StudentEntity student);
    int insertStudent(StudentEntity student);
    int insertBatch(@Param("students") List<StudentEntity> students);
    List<StudentEntity> getStudentByBind(StudentEntity student);
}
1
2
3
4
5
6
7
8

3. 动态SQL主要元素的使用

• IF 元素，用于单条分支判断，如：根据学生的姓名、年龄的进行组合查询，示例代码如下

<select id="getStudentByCondition" resultType="com.bjwl.pojo7.StudentEntity">  
    select * from tb_student where 1=1  
    <if test="name !=null and name !=''">  
        and student_name like concat('%',#{name},'%')  
    if>  
    <if  test="age !=null and age !=''">  
        and student_age =#{age}  
    if>  
select>  
1
2
3
4
5
6
7
8
9

测试代码如下所示

public void testIf() throws IOException {
        SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);

        Map<String,Object> map = new HashMap<>();
        map.put("name","张");
        map.put("age",null);

        List<StudentEntity> students = dao.getStudentByCondition(map);
        for (StudentEntity student:students){
            System.out.println(student.toString());
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

运行结果如下

• Choose（when otherwise）元素：用于多条分支判断，只执行某一条分支，如：根据学生的姓名、年龄的进行组合查询，示例代码如下

<select id="getStudentByCondition2" resultMap="com.bjwl.pojo7.StudentEntity">  
     select * from tb_student where 1=1  
     <choose>  
         <when test="name !=null and name !=''">  
             and student_name like concat('%',#{name},'%')  
         when>  
         <when test="age !=null and age !=''">  
             and student_age =#{age}  
         when>  
         <otherwise>  
             and student_name = '王小丫'  
         otherwise>  
     choose>  
select>  
1
2
3
4
5
6
7
8
9
10
11
12
13
14

测试代码如下：

    public void testChoose() throws IOException {
        SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);

        Map<String,Object> map = new HashMap<>();
        /*map.put("name","王");
        map.put("age",18);*/

        /*屏蔽map中的name项试试 比较和两条if语句的区别*/
        List<StudentEntity> students = dao.getStudentByCondition2(map);
        for (StudentEntity student:students){
            System.out.println(student.toString());
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

运行结果如下：

• 辅助元素Where，myBaits提供辅助元素Where，当查询带条件时自动加where，不带条件时自动去掉where，代码如下所示

<select id="getStudentByCondition3" resultType="com.bjwl.pojo7.StudentEntity">  
    select * from tb_student  
    <where>  
        <if test="sname !=null and sname !=''">  
            and student_name like concat('%',#{sname},'%')  
        if>  
        <if test="age !=null and age !=''">  
            and student_age =#{age}  
        if>  
    where>  
select> 
1
2
3
4
5
6
7
8
9
10
11

• 辅助元素trim的使用：使用trim自动的增加或者取消多余的字符，用于去除sql语句中多余的and关键字，逗号

    <insert id="insertStudent">
        insert into  tb_student
        <trim prefix="("  suffix=")"  suffixOverrides=",">
           <if test="studentName != null">
               student_name,
           if>
            <if test="studentAge != null">
                student_age
            if>
        trim>

        <trim prefix="values("  suffix=")"  suffixOverrides=",">
            <if test="studentName != null">
                #{studentName},
            if>
            <if test="studentAge != null">
                #{studentAge}
            if>
        trim>
    insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Trim元素的有prefix、prefixOverrides、suffix、suffixOverrides四个属性，prefix开头添加一个，prefixOverrides开头去掉一个，suffix结尾添加一个，suffixOverrides结尾去掉一个，如代码的第3、12行，增加前缀和后缀，去掉多余的逗号。
测试代码如下

    public void testTrim2() throws IOException {
        SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession();
        StudentDao dao = sqlSession.getMapper(StudentDao.class);

        StudentEntity student = new StudentEntity();
        student.setStudentName(null);
        student.setStudentAge(30);
        dao.insertStudent(student);
        sqlSession.commit();

        /*去掉age的赋值，试试 查看sql语句，是不是没有了“，”*/
       /* List students = dao.getStudentByCondition3("任我行",null);*/
        List<StudentEntity> students = dao.getStudentByCondition3(null,30);
        for (StudentEntity stu:students){
            System.out.println(stu.toString());
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

运行结果

• 辅助元素set的使用： 在update语句中需要使用set语句完成对数据表中的字段进行更新，并能够自动去除多余的逗号，示例代码如下。

  <update id="UpdateStudentById">  
      update tb_student  
      <set>  
          <if test="studentName!=null">  
              student_name=#{studentName},  
          if>  
          <if test="studentAge!=null">  
              student_age=#{studentAge},  
          if>  
      set>  
      where sno=#{sno}  
  update>
1
2
3
4
5
6
7
8
9
10
11
12

省略测试类和执行结果

• Foreach元素的使用：循环语句的使用，通常使用在in语句环境中、批量插入、批量更新等语句中。其中：item是当前循环中元素；index当前元素所在位置下标；collection是传递过来的参数类型；open和close是使用什么样的符号，将这些元素包裹起来；separator是元素之间的间隔符号。如：批量增加学生信息，映射文件代码如下

<insert id="insertBatch">  
   insert into tb_student(student_name,student_age)  
   values  
   <foreach  collection="students" item="student" index="index" separator=",">  
      (#{student.studentName},#{student.studentAge})  
   foreach>  
insert> 
1
2
3
4
5
6
7

测试代码如下：

    public void testExecutorTypeBatch() throws IOException {
        SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession(ExecutorType.BATCH);
        StudentDao dao = sqlSession.getMapper(StudentDao.class);
        List<StudentEntity> students = new ArrayList<>();
        for (int i = 0; i <5 ; i++) {
            StudentEntity student=new StudentEntity();
            student.setStudentName("王大锤");
            student.setStudentAge(18);
            students.add(student);
        }
        dao.insertBatch(students);
        sqlSession.commit();
        sqlSession.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

运行界面如下图所示

• bind元素的使用：用来在映射文件中定义变量，该变量的值为参数的值和拼接一些其他字符串后组成的字符串。如：使用学生姓名进行查询时，使用bind元素，查询名为“小凡”的同学，其映射文件代码如下所示

<select id="getStudentByBind"  resultType="com.bjwl.pojo7.StudentEntity" >  
    <bind name="name" value="'%'+studentName"/>  
    select * from tb_student where student_name like #{name}  
select>
1
2
3
4

测试代码如下

    public void testBind() throws IOException {
        SqlSession sqlSession = BatisUtils.getSqlSessionFactory().openSession(ExecutorType.BATCH);
        StudentDao dao = sqlSession.getMapper(StudentDao.class);

        StudentEntity student = new StudentEntity();
        student.setStudentName("小凡");

        List<StudentEntity> stus = dao.getStudentByBind(student);
        for (StudentEntity stu : stus) {
            System.out.println(stu.toString());
        }

        sqlSession.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

运行结果如下：