Include
<sql id="Base_Column_List" >
collegeID, collegeName
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
select
<include refid="Base_Column_List" />
from t_notification_template
where id = #{id,jdbcType=BIGINT}
</select>
这个在MyBatis查询数据库的sql中经常会出现。它的在上面已经定义,作用相当于 * ,
Base_Column_List是固定的几个字段,而用*号的话会降低查询效率,因为后期数据库的字段会不断增加。
Set
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio},</if>
</set>
where id=#{id}
</update>
trim
trim 是更灵活用来去处多余关键字的标签,它可以用来实现 where 和 set 的效果
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
trim代替where
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</trim>
trim 代替 set
!-- if/trim代替set(判断参数) - 将 User 类不为空的属性更新 -->
<update id="updateUser_if_trim" parameterType="com.yiibai.pojo.User">
UPDATE user
<trim prefix="SET" suffixOverrides=",">
<if test="username != null and username != '' ">
username = #{username},
</if>
<if test="sex != null and sex != '' ">
sex = #{sex},
</if>
<if test="birthday != null ">
birthday = #{birthday},
</if>
</trim>
WHERE user_id = #{user_id}
</update>
trim 元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是 prefix 和 suffix;可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是 prefixOverrides 和 suffixOverrides;正因为 trim 有这样的功能,所以我们也可以非常简单的利用 trim 来代替 where 元素的功能l