什么是动态SQL? 动态SQL有什么作用?
传统的使用JDBC的方法,相信大家在组合复杂的的SQL语句的时候,需要去拼接,稍不注意哪怕少了个空格,都会导致错误。
Mybatis的动态SQL功能正是为了解决这种问题, 其通过 if, choose, when, otherwise, trim, where, set, foreach标签,可组合成非常灵活的SQL语句,从而提高开发人员的效率。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。我们可通过mybatis提供的各种标签方法实现动态拼接sql。
动态SQL标签
1)if
需求:传递pojo类综合查询用户信息,使用用户的id和username灵活地查询用户信息。
<select id="findUserList" parameterType="user" resultType="user">
select * from user
where 1=1
<if test="id!=null">
and id=#{id}
</if>
<if test="name!=null and name!=''">
and name like '%${name}%'
</if>
</select>
注意:
- name需要做不等于空字符串的校验。
- User类中id属性的类型要改为Integer包装类型,因为int类型的id是不可能为null的。
以上案例你会发现,如果传入的参数id和name均为null,则还是会附带一个默认条件where 1 = 1。虽然不影响结果,但是还是让我们的sql变的复杂了一点,下面我们可以使用where标签来解决此问题。
2)where
<select id="findUserList" parameterType="user" resultType="user">
select * from user
<where>
<if test="id!=null">
and id=#{id}
</if>
<if test="name != null and name != ''">
and name like '%${name}%'
</if>
</where>
</select>
**注意:**有些同学在想这种情况会不会出现sql中有 where and id=?的情况,看起来是的,不过实质上, mybatis是对它做了处理,where标签会自动处理第一个AND或者OR
3)foreach
现有这样一个需求:传入多个id查询用户信息。如若编写sql语句,可用下边两个sql实现:
- SELECT * FROM USER WHERE username LIKE ‘%张%’ AND (id =1 OR id =2 OR id=3)
- SELECT * FROM USER WHERE username LIKE ‘%张%’ id IN (1,2,3)
为了解决这个需求,首先在QueryVo类中定义List属性ids存储多个用户id,并添加getter/setter方法:
public class QueryVo {
private String query;
private User user;
private List<Integer> ids;
//...
}
然后在UserMapper.xml映射文件中添加如下< select>元素:
<!-- 动态sql foreach测试 -->
<select id="findUserByIds" parameterType="queryvo" resultType="user">
SELECT * FROM `user`
<where>
<!-- and id IN(1,10,20,21,31) -->
<foreach collection="ids" item="id" open="and id in(" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
-
collection:需要循环遍历的集合
-
item:集合中每一个元素
-
open :以什么开头
-
close:以什么结尾
-
separator:每个元素之间以什么连接
4)choose
与JSTL的choose标签类似
<select id="findUserList" parameterType="user" resultType="user">
select * from user
<where>
<choose>
<when test="id!=null">
and id=#{id}
</when>
<otherwise>
and name like '%${name}%'
</otherwise>
</chosse>
</where>
</select>
5)sql片段
sql中可将重复的sql提取出来,使用时用include引用即可,最终达到sql重用的目的:
<select id="findUserList" parameterType="user" resultType="user">
select * from user
<where>
<if test="id!=null">
and id=#{id}
</if>
<if test="username != null and username != ''">
and username like '%${username}%'
</if>
</where>
</select>
将where条件抽取出来,同时我们也可将要查询的字段抽取出来:
<sql id="find_user_list_where">
<where>
<if test="id!=null">
and id=#{id}
</if>
<if test="username != null and username != ''">
and username like '%${username}%'
</if>
</where>
</sql>
<sql id="user_field_list">
id,username,birthday,sex,address
</sql>
使用include引用:
<select id="findUserList" parameterType="user" resultType="user">
select <include refid="user_field_list"/> from user
<include refid="find_user_list_where"/>
</select>
注意:如果引用其它mapper.xml映射文件的sql片段,则在引用时需要加上namespace
<include refid="namespace.sql片段"/>