In our last blog, we briefly introduced the use of choose when other wise. In this blog, we will talk about another similar label: where, set, trim.
1. The role of where tags: Insert a where if there is a return value in the element contained in the tag; if the characters after where begin with AND and OR, they are excluded.
For instance:
<select id="selectByUser" resultType="cd.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassWord,
user_email userEmail,
user_info userInfo,
user_img headImg,
create_time createTime
from sys_user
where 1=1
<if test="userName != null and userName !=''">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userEmail !=''and userEmail !=null">
and user_email=#{userEmail}
</if>
</select>Modified with where tag:
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
select id,
user_name userName,
user_password userPassWord,
user_email userEmail,
user_info userInfo,
user_img headImg,
create_time createTime
from sys_user
<where>
<if test="userName != null and userName !=''">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userEmail !=''and userEmail !=null">
and user_email=#{userEmail}
</if>
</where>
</select>In the first example, if there is no where 1 = 1, when both if conditions are not met, the final generated SQL will end with where, which does not conform to the SQL specification, so you need to add a condition that defaults to true.
For the second example, if two if conditions are not satisfied, the code wrapped in the where tag will be removed, and this code will not appear in SQL.
2. The function of the set tag: Insert a set if there is a return value in the element contained in the tag; if the string after the set ends with a comma, remove the comma.
For instance:
<!--Batch Update of Student Information-XX-2017-7-24 17:00:08-->
<update id="updateStudentList">
<foreach collection="studentEntitys" item="item" index="index" open="" close="" separator=";">
update t_student
<set>
<if test="item.classesId!='' and item.classesId!=null">
classes_id=${item.classesId}
</if>
<if test="item.code!='' and item.code!=null">
code=${item.code}
</if>
<if test="item.roomId!='' and item.roomId!=null">
room_id=${item.roomId}
</if>
</set>
where id = ${item.id}
</foreach>
</update>Note that the last where id=${item.id} is not omitted, and if the set contains empty content, only the last comma problem can be avoided.
3.trim Usage: where and set tags can be implemented using trim tags, and at the bottom, through TrimSqlNode.
where tag corresponding trim implementation:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>The trim implementation corresponding to set tag:
<trim prefix="SET" prefixOverrides=",">
...
</trim>trim attribute
- Prefix: When a trim element contains content, prefix is added to the content to specify a prefix
- Preix Overrides: When the trim element contains content, the prefix string matching the content is removed
- Suffix: When the trim element contains content, the suffix specified by suffix is added to the content.
- Suffix Overrides: When trim contains content, the suffix string matching the content is removed