【MyBatis】第七篇：动态sql

【MyBatis】第七篇：动态sql

mybatis中的动态sql，其实就是在mybatis中映射配置文件中通过if等判断语句写sql。现在聊一下，常用的的判断语句。

前面准备：

CREATE TABLE `student` (`sid` int DEFAULT NULL,`sname` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,`sage` int DEFAULT NULL,`ssex` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,`gid` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;insert  into `student`(`sid`,`sname`,`sage`,`ssex`,`gid`) values (1,'张三',18,'男',2),(2,'赛貂蝉',14,'女',1),(3,'胡铁花',23,'男',2),(4,'白飞飞',16,'女',1),(5,'李寻欢',16,'男',1),(4,'宫崎',18,'男',2);

if

if标签是通过test数据的表达式进行判断，如果true就执行其标签中的内容，如果false则向反。

常用场景如下，比如在很多伤害数据库中的字段的条件为NULL的时候会影响结果。保留web项目中，前端传递的参数又三个条件，但是三个条件不一定都会被用户选择。然后生成不同的sql从数据库中取出数据。

当然也可以通过如果通过java代码进行判断，然后也可以解决这个问题，不过mybatis的通过xml配置sql语句，所以可能会更直观方便一些。

映射文件：

 <select id="getStudent" resultType="Student"><!--   为什么会写 1=1 因为比如例子中sql的条件会通过and 连接 防止出现问题采用这个方法 -->SELECT  sid ,  sname ,  sage ,  ssex ,  gid  FROM  testmybatis.student WHERE 1=1<if test="sid!='' and sid !=null">and sid=#{sid}</if><if test="sname!='' and sname !=null">and sname=#{sname}</if><if test="sage!='' and sage !=null">and sage=#{sage}</if><if test="ssex!='' and ssex !=null">and ssex=#{ssex}</if><if test="gid!='' and gid !=null">and gid=#{gid}</if></select>

映射接口类中的方法：

    List<Student>  getStudent(Student student);

调用方法：

  Student student=new Student(null,null,null,"女",null);System.out.println("  --------------------       " +  Student(student) );

注意：

• if中的test属性表达式中，直接用对象的属性进行判断，不需要通过#{}或者${}.
• 一般因为有连接关键字，所以在where后面会通过一个伪条件而实现动态代码。

where标签

当然如果不想在where中天津一个伪条件的话，还可以通过where标签进行实现，具体如下：

 <select id="getStudent" resultType="Student"><!--   为什么会写 1=1 因为比如例子中sql的条件会通过and 连接 防止出现问题采用这个方法 -->SELECT  sid ,  sname ,  sage ,  ssex ,  gid  FROM  testmybatis.student<where><if test="sid!='' and sid !=null">sid=#{sid}</if><if test="sname!='' and sname !=null">and sname=#{sname}</if><if test="sage!='' and sage !=null">and sage=#{sage}</if><if test="ssex!='' and ssex !=null">and ssex=#{ssex}</if><if test="gid!='' and gid !=null">and gid=#{gid}</if></where></select>

然后看一下结果，现在换成性别为男：

也是没有问题，这个时候可以看出where标签有两个作用：

• 第一：sql语句需要些where 关键字，where标签会自动添加这个关键字。
• 第二：如果第一个条件不成立，而导致where后面and关键字的话，where标签会自动将首个and优化掉，让sql符合要求。而这种取消，只是取消条件的前面and。

如果这样写呢？把and放在后面。

<select id="getStudent" resultType="Student"><!--   为什么会写 1=1 因为比如例子中sql的条件会通过and 连接 防止出现问题采用这个方法 -->SELECT sid , sname , sage , ssex , gid FROM testmybatis.student<where><if test="sid!='' and sid !=null">sid=#{sid} and</if><if test="sname!='' and sname !=null">sname=#{sname} and</if><if test="sage!='' and sage !=null">sage=#{sage} and</if><if test="ssex!='' and ssex !=null">ssex=#{ssex} and</if><if test="gid!='' and gid !=null">gid=#{gid}</if></where></select>

直接保存了，而这个时候需要使用另一个标签trim。

trim

先看一下trim标签：

可以看出trim标签有四个属性。具体有什么用呢？

属性	描述
prefix	在tirm标中的内容前添加指定内容
prefixOverrides	在tirm标中的内容后添加指定内容
suffix	在tirm标中的内容前面删除指定内容
suffixOverrides	在tirm标中的内容后面删除指定内容

看着有些绕，还是老规矩用代码演示：

 <select id="getStudent" resultType="Student"><!--   为什么会写 1=1 因为比如例子中sql的条件会通过and 连接 防止出现问题采用这个方法 -->SELECT sid , sname , sage , ssex , gid FROM testmybatis.student<trim prefix="where" suffixOverrides="and"><if test="sid!='' and sid !=null">sid=#{sid} and</if><if test="sname!='' and sname !=null">sname=#{sname} and</if><if test="sage!='' and sage !=null">sage=#{sage} and</if><if test="ssex!='' and ssex !=null">ssex=#{ssex} and</if><if test="gid!='' and gid !=null">gid=#{gid}</if></trim></select>

看一下结果：

解读

<trim prefix="where" suffixOverrides="and">

因为trim标签不会主动添加where ，所以需要通过prefix在条件语句前面添加where。因为and是写在后面的，如果最后出现and会影响sql语句，所以通过设置suffixOverrides来将最后面的and删除，如果没有也就不必删除了

choose —when—otherwise

这个语句判断记住一点其更像是是java中switch–case—default。

意思就是所有的条件，只要最上面的满足后面的就直接跳出。

<select id="getStudent" resultType="Student"><!--   为什么会写 1=1 因为比如例子中sql的条件会通过and 连接 防止出现问题采用这个方法 -->SELECT sid , sname , sage , ssex , gid FROM testmybatis.student<where ><choose><when test="sid!='' and sid !=null"><!-- 因为只会判断一个条件满足，所以不需要添加and这样的连接符 -->sid=#{sid}</when><when test="sname!='' and sname !=null">sname=#{sname}</when><when test="sage!='' and sage !=null">sage=#{sage}</when><when test="ssex!='' and ssex !=null">ssex=#{ssex}</when><otherwise>gid=#{gid}</otherwise></choose></where>
</select>

然后调用方法：

  Student student=new Student(null,null,14,"男",null);System.out.println("  --------------------       " +  Student(student) );

然后看一下结果：

这里注意数据库中没有男性14岁的学生，而且性别为男的条件也没有其效果，所以说只会选择一个条件。

注意：choose中的必须有一个when标签，而otherwise标签最多只有一个

foreach

这个标签看见就知道是一个循环语句，具体是如何用呢？看实例吧。

foreach 元素的属性主要有 item，index，collection，open，separator，close。

属性	描述
item	表示集合中没一个元素的迭代的名字，这个在jsp中也有。毕竟这个元素有可能是一个对象，方便取出属性值。
index	指定一个名字，用户表示在迭代过程中，每次迭代到的位置，可以理解为for（int i=0;i++;i<5）中的i。可以为起一个名字。
open	表示该语句什么时候开始
separator	表述迭代数据中间用什么内容作为分隔符。
close	表示什么时候结束

还是老规矩，用代码演示：

先创建一个list，然后用来插入演示：

 List<Student> list=new ArrayList<Student>();list.add(new Student(null,"v胖",3,"男",3));list.add(new Student(null,"奎爷",33,"男",1));list.add(new Student(null,"娜美",15,"女",3));studentMapper.insertListStudent(list);

  <insert id="insertListStudent">INSERT INTO  testmybatis.student ( sid ,  sname ,  sage ,  ssex ,  gid ) VALUES<foreach collection="studentList" item="student"   separator="," ><!--sql插入数应该有一对（） 不可以使用 open和close会在前后放内容，包裹整个数据，比如应该是(student),(student),(student) 却变成(student,student,student)-->( #{student.sid}, #{student.sname}, #{student.sage}, #{student.ssex}, #{student.gid})</foreach></insert>

接口类的方法：

 void insertListStudent(@Param("studentList") List<Student> studentList);

运行结果：

现在来一个批量删除的操作，这个可以使用多个属性如下操作：

   void deleteArr(@Param("arr") int[]  arr);

 <delete id="deleteArr">delete from student where sid in<foreach collection="arr" separator="," open="(" close=")" item="sid">#{sid}</foreach></delete>

然后调用方法：

        int[] arr={12,13,14};studentMapper.deleteArr(arr);

现在看一下结果：

所以可以看在foreach中的open和close的具体效果。

sql

还有一个sql标签，这个标签可以记录一段sql，在需要使用的适合用incluede标签进行引用。

还是老规矩，进程代码演示，用上面的例子进行演示：

先不用sql的如下：

<select id="getStudent" resultType="Student"><!--   为什么会写 1=1 因为比如例子中sql的条件会通过and 连接 防止出现问题采用这个方法 -->SELECT sid , sname , sage , ssex , gid FROM testmybatis.student<where ><choose><when test="sid!='' and sid !=null"><!-- 因为只会判断一个条件满足，所以不需要添加and这样的连接符 -->sid=#{sid}</when><when test="sname!='' and sname !=null">sname=#{sname}</when><when test="sage!='' and sage !=null">sage=#{sage}</when><when test="ssex!='' and ssex !=null">ssex=#{ssex}</when><otherwise>gid=#{gid}</otherwise></choose></where>
</select>

然后是使用sql标签的：

<select id="getStudent" resultType="Student"><!--   为什么会写 1=1 因为比如例子中sql的条件会通过and 连接 防止出现问题采用这个方法 -->SELECT <include refid="studentColumns"></include> FROM testmybatis.student<where><choose><when test="sid!='' and sid !=null"><!-- 因为只会判断一个条件满足，所以不需要添加and这样的连接符 -->sid=#{sid}</when><when test="sname!='' and sname !=null">sname=#{sname}</when><when test="sage!='' and sage !=null">sage=#{sage}</when><when test="ssex!='' and ssex !=null">ssex=#{ssex}</when><otherwise>gid=#{gid}</otherwise></choose></where>

这样方便将一些常用的sql公用部分拆出来，然后进行使用，有点像是java中的公共方法提出的感觉，这样方便使用。

script

这个标签是搭配着注解使用的，还是老规矩进行代码演示：

  @Delete(" <script>" +"delete from student where sid in " +"<foreach collection="arr" separator="," open="(" close=")" item="sid"> " +" #{sid} " +"  </foreach>" +" </script>")void deleteArr(@Param("arr") int[]  arr);

可以看出通过script标签可以将本来在xml中写的配置信息，通过注解写在java文件中，虽然方便但是说实话没有通过xml看着配置信息清晰。

bind 标签

元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。

然后简单演示：

  <select id="selectStudentLike" resultType="Student"><bind name="pattern" value="'%' + name + '%'" />SELECT * FROM studentWHERE sname LIKE #{pattern}</select>

    List<Student>  selectStudentLike(  Map<String, String> params);

   Map<String, String> params =new HashMap();params.put("name", "三");List<Student> studentList = studentMapper.selectStudentLike(params);System.out.println(studentList);

看一下结果：

不过出发在一个sql中使用了多次某个片段，不然不如直接写的方便。