Mybatis中动态sql小结

什么是动态sql

  • MyBatis 的强大特性之一便是它的动态 SQL,它极大的简化了我们拼接SQL的操作。
  • 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。
  • MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素:
  1. if
  2. choose (when, otherwise)
  3. trim (where, set)
  4. foreach

if条件判断

  • 查询学生,查询条件中携带了哪个字段,就把字段拼接上
<!-- 查询条件中携带了哪个字段,就把字段拼接上 -->
<!-- public List<Student>ListStudentByCondition(Student student) ; -->
<select id="ListStudentByCondition" resultType="com.cn.cmc.bean.Student" >
 select * 
 from student
 where
 <!-- test:OGNL表达式
 从参数中取值
 遇到特殊字符应用转义字符:见下图
 --> 
 <if test="id!=null">
 id = #{id}
 </if>
 <if test="name!=null and name.trim()!=''">
 and name = #{name}
 </if>
 <if test="age!=null && age!=""">
 and age = #{age}
 </if>
 <if test="sex=='M' or sex=='F'" >
 and trim(sex) = #{sex}
 </if>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  • 转义字符表:

Mybatis中动态sql小结


注:char类型查询时判断条件不正确的问题

特别注意:我使用的是ORACLE,性别字段为char长度为2,Mybatis在查出数据后会自动的将char的长度补为2得到的就是’F ‘(有一个空格),因此导致sex=#{sex}的判断结果不正确,解决办法就是在查出数据之后使用trim()方法去掉多余的空格。

问题:如上所述,可以使用if方法进行判断,符合条件的进行拼接,但也会出现问题,如果第一个字段为空的话就会导致字符串拼接失败造成如下结果:

### SQL: select * from student where and trim(sex) = ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00936: 缺失表达式
1
2

那么怎么解决这个问题呢?

  • 解决方式一:(给where加上永真条件1=1,之后全用and连接)
<select id="ListStudentByCondition" resultType="com.cn.cmc.bean.Student" >
 select * 
 from student
 where 1=1
 <!-- test:OGNL表达式
 从参数中取值
 遇到特殊字符应用转义字符:见下图
 --> 
 <if test="id!=null">
 and id = #{id}
 </if>
 <if test="name!=null and name.trim()!=''">
 and name = #{name}
 </if>
 <if test="age!=null && age!=""">
 and age = #{age}
 </if>
 <if test="sex=='M' or sex=='F'" >
 and trim(sex) = #{sex}
 </if>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
  • 解决方式二:(使用where标签)
<select id="ListStudentByCondition" resultType="com.cn.cmc.bean.Student" >
 select * 
 from student
 <where>
 <if test="id!=null">
 and id = #{id}
 </if>
 <if test="name!=null and name.trim()!=''">
 and name = #{name}
 </if>
 <if test="age!=null && age!=""">
 and age = #{age}
 </if>
 <if test="sex=='M' or sex=='F'" >
 and trim(sex) = #{sex}
 </if>
 </where>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Trim字符串拼接

以上问题还可以使用trim字符串拼接来解决问题。

  • 解决方式三:使用trim标签拼接
<select id="ListStudentByConditionTrim" resultType="com.cn.cmc.bean.Student" >
 <!-- trim标签拼接字符串
 prefix:给拼串后的字符串加上一个前缀
 prefixOverrides:去掉拼串后的字符串的一个前缀
 suffix:给拼串后的字符串加上一个后缀
 suffixOverrides:去掉拼串后字符串的一个前缀
 set标签:在更新中使用,可以去除多余的逗号
 -->
 select * 
 from student
 <trim prefix="where" prefixOverrides="and" suffix="" suffixOverrides="">
 <if test="id!=null">
 and id = #{id}
 </if>
 <if test="name!=null and name.trim()!=''">
 and name = #{name}
 </if>
 <if test="age!=null && age!=""">
 and age = #{age}
 </if>
 <if test="sex=='M' or sex=='F'" >
 and trim(sex) = #{sex}
 </if>
 </trim>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
  • bind标签的使用,按名字模糊查询学生
  • bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到元素中方便使用
<!-- 按名字模糊查询学生,假设在接口中有方法public Student getStudentByName(@Param("name")String name) ;
 bind标签:将OGNL表达式绑定到指定变量中
 name:绑定的变量名
 value:OGNL表达式
 -->
 <select id="getStudentByName" resultType="com.cn.cmc.bean.Student">
 <bind name="_name" value="'%'+name+'%'"/>
 select * 
 from student
 where name like #{_name}
 </select>
1
2
3
4
5
6
7
8
9
10
11

choose(选择分支)

  • 查询学生,要求传入什么字段按什么字段来查
<!-- choose, when, otherwise查询学生,有哪个字段按哪个字段查 -->
<!-- choose分支选择器:类似于switch-case-default的用法
 when:判断进入那个分支类似于case
 otherwise:当所有情况都不匹配时,执行的操作
 -->
 <select id="ListStudentByConditionChoose" resultType="com.cn.cmc.bean.Student">
 select * 
 from student
 <where>
 <choose>
 <when test="id!=null">
 id = #{id}
 </when>
 <when test="name!=null and name!=''">
 name = #{name}
 </when>
 <when test="sex=='F' or sex=='M'">
 trim(sex) = #{sex}
 </when>
 <when test="age!=0">
 age = #{age}
 </when>
 <otherwise>
 1=1 
 </otherwise>
 </choose>
 </where>
 </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

注:choose一次只能选择一条分支

特别注意:在设计的时候,Student的age字段我使用int这个类型,这是不合理的,因为java中默认int的属性为0,造成判断的时候age永不为空,应用Integer类型

foreach集合遍历

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历,通常是在构建 IN 条件语句的时候。

  • 按id条件构建In来查出学生
<!-- foreach集合的遍历,通常是构建in条件语句的时候使用
 collection:指定遍历的集合名:
 Mybatis会将传入的list封装到map中,map中的键就是list
 也可以用注解@param("")决定所封装的别名
 item:将当前遍历的对象取一个名方便调用,调用方法#{对象名}
 index:若遍历的对象是list时,index就表示list的索引,item表示当前的值
 若遍历的对象是一个map的时候,index表示map的key,item表示map的value
 open:遍历的开始符
 close:遍历的结束符
 separator:表示遍历元素之间的分隔符
 -->
 <select id="ListStudentByConditionForeach" resultType="com.cn.cmc.bean.Student">
 select * 
 from student
 <foreach collection="list" item="item_id" open="where id in(" close=")" separator=",">
 #{item_id}
 </foreach> 
 </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  • 批量保存学生
  • 相关SQL语句为:
begin
insert into student(id,name,sex,age)
values(?,?,?,?);
insert into student(id,name,sex,age)
values(?,?,?,?);
end ;
1
2
3
4
5
6
7
8
  • 在Mybatis中这样配置
<!-- 批量插入学生 -->
 <insert id="addAllStudent" parameterType="com.cn.cmc.bean.Student">
 <foreach collection="students" item="std_item" open="begin" close="end;" separator="">
 insert into student(id,name,sex,age)
 values (student_seq.nextval,#{std_item.name},#{std_item.sex},#{std_item.age});
 </foreach>
 </insert>

相关推荐