之前用mybatis在xml中写sql语句用到过<if>
标签,但是不知道这是动态SQL(尴尬),最近空闲整理一下常用的动态SQL。好记性不如烂笔头
以图 t_user 表为例:
1. if 语句
根据 userNo 和 name 来查询数据。如果userNo为空,那么将只根据name来查询;反之只根据userNo来查询.
<select id="getUserByUsernoAndName" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
<!-- 这里和普通的sql 查询语句差不多,对于只有一个参数,后面的 #{id}表示占位符,里面不一定要写id,
写啥都可以,但是不要空着,如果有多个参数则必须写pojo类里面的属性 -->
select * from t_user where userNo=#{userNo} and name=#{name}
</select>
上面的查询语句,我们可以发现,如果 #{userNo} 为空,那么查询结果也是空,如何解决这个问题呢?使用 if 来判断
<select id="getUserByUsernoAndName" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
select * from t_user where 1=1
<if test="userNo != null">
and userNo=#{userNo}
</if>
<if test="name != null">
and name=#{name}
</if>
</select>
但是加一个 1=1
作为条件是不是很奇怪,用where 语句来解决这个问题
2. if+where 语句
<select id="getUserByUsernoAndName" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
select * from t_user
<where>
<if test="userNo != null">
and userNo=#{userNo}
</if>
<if test="name != null">
and name=#{name}
</if>
</where>
</select>
这个“where”标签会知道如果它包含的标签中有返回值的话,它就插入一个‘where’。此外,如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。
3. if+set 语句
<update id="updateUserById" parameterType="com.xxb.domain.UserPO">
update t_user u
<set>
<if test="userNo != null">
u.userNo=#{userNo},
</if>
<if test="name != null">
u.name=#{name}
</if>
</set>
where id=#{id}
</update>
4. choose(when,otherwise) 语句
有时候,我们不想用到所有的查询条件,只想选择其中的一个,查询条件有一个满足即可,使用 choose 标签可以解决此类问题,类似于 Java 的 switch 语句
<select id="getUserByChoose" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
select * from t_user
<where>
<choose>
<when test="id != null">
and id=#{id}
</when>
<when test="userNo != null">
and userNo=#{userNo}
</when>
<otherwise>
and name=#{name}
</otherwise>
</choose>
</where>
</select>
这里我们有三个条件,id,userNo,name,只能选择一个作为查询条件
如果 id 不为空,那么查询语句为:select * from t_user where id=?
如果 id 为空,那么看userNo 是否为空,如果不为空,那么语句为 select * from _user where userNo=?;
如果 userNo 为空,那么查询语句为 select * from t_user where name=?
5. foreach 语句
我们需要查询 t_user 表中 id 分别为1,2,3的用户
sql语句:select * from t_user where id=1 or id=2 or id=3
select * from t_user where id in (1,2,3)
<select id="selectUserByListId" returnMap="BaseResultMap" parameterType="java.util.List">
select * from t_user
<where>
<!-- collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from t_user where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
6. trim 语句
trim标记是一个格式化的标记,可以完成set或者是where标记的功能
用 trim 改写上面第二点的 if+where 语句
<select id="getUserByUsernoAndName" returnMap="BaseResultMap" parameterType="com.xxb.domain.UserPO">
select * from t_user
<!--
<where>
<if test="userNo != null">
and userNo=#{userNo}
</if>
<if test="name != null">
and name=#{name}
</if>
</where>
-->
<trim prefix="where" prefixOverrides="and | or">
<if test="userNo != null">
and userNo=#{userNo}
</if>
<if test="name != null">
and name=#{name}
</if>
</trim>
</select>
prefix:前缀
prefixoverride:去掉第一个and或者是or
suffixoverride:去掉最后一个标记(就像是上面前缀中的and or一样)
7. SQL 片段
有时候可能某个 sql 语句我们用的特别多,为了增加代码的重用性,简化代码,我们需要将这些代码抽取出来,然后使用时直接调用。
比如:我们表中有blob类型字段,平时查询结果用不到这个字段,那么我们就把这个代码抽取出来,如下:
<sql id="Blob_Column_List">
base64
</sql>
<sql id="Base_Column_list">
id,name,preview,styleId,laytype,width,height
</sql>
<select id="getByStyleId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select <include refid="Base_Column_list"/> from t_style_inner where styleId=#{styleId} order by laytype asc
</select>
动态 sql 语句的编写往往是一个拼接的问题,要注意编写规范。
8. SQL 模糊查询
现在有个这样的场景,同一个输入框即可以输入id也可以输入name,返回的结果是两者的叠加,比如说输入框值为‘1’,返回结果包含id为‘1’、name中包含‘1’的结果集。
<select id="search" resultType="com.×××.×××.db.RoomPo">
<if test="roomPo.name != null">
<bind name="likeName" value="'%'+ roomPo.name +'%'"/>
</if>
<if test="roomPo.id != null">
<bind name="likeId" value="roomPo.id"/>
</if>
select * from t_room where status=1
<if test="roomPo.name != null">
<choose>
<when test="roomPo.id != null">
and ( id = #{likeId} or
</when>
<otherwise>
and (
</otherwise>
</choose>
name like #{likeName})
</if>
limit #{begin},#{pageSize}
</select>
bind 标签可以使用 OGNL 表达式创建一个变量井将其绑定到上下文中。