MyBatis进阶（＜if＞＜trim＞＜where＞＜set＞＜foreach＞＜include＞标签的使用）

目录

< if >标签

< trim >标签

< where >标签

< set >标签

< foreach >标签

< include >标签

---

< if >标签

非必填字段

xml实现

<insert id="insertUserByCondition">
 INSERT INTO userinfo (
 username,
 `password`,
 age,
 <if test="gender != null">
 gender,
 </if>
 phone)
 VALUES (
 #{username},
 #{age},
 <if test="gender != null">
 #{gender},
 </if>
 #{phone})
</insert>

注解实现（不推荐）

@Insert("<script>" +
 "INSERT INTO userinfo (username,`password`,age," +
 "<if test='gender!=null'>gender,</if>" +
 "phone)" +
 "VALUES(#{username},#{age}," +
 "<if test='gender!=null'>#{gender},</if>" +
 "#{phone})"+
 "</script>")
Integer insertUserByCondition(UserInfo userInfo);

< trim >标签

• prefix：表⽰整个语句块，以prefix的值作为前缀

• suffix：表⽰整个语句块，以suffix的值作为后缀

• prefixOverrides：表⽰整个语句块要去除掉的前缀

• suffixOverrides：表⽰整个语句块要去除掉的后缀

有多个字段是选填项时，考虑采用此方式

xml方式

<insert id="insertUserByCondition">
 INSERT INTO userinfo
 <trim prefix="(" suffix=")" suffixOverrides=",">
 <if test="username !=null">
 username,
 </if>
 <if test="password !=null">
 `password`,
 </if>
 <if test="age != null">
 age,
 </if>
 <if test="gender != null">
 gender,
 </if>
 <if test="phone != null">
 phone,
 </if>
 </trim>
 VALUES
 <trim prefix="(" suffix=")" suffixOverrides=",">
 <if test="username !=null">
 #{username},
 </if>
 <if test="password !=null">
 #{password},
 </if>
 <if test="age != null">
 #{age},
 </if>
 <if test="gender != null">
 #{gender},
 </if>
 <if test="phone != null">
 #{phone}
 </if>
 </trim>
</insert>

注解方式（不推荐）

@Insert("<script>" +
 "INSERT INTO userinfo " +
 "<trim prefix='(' suffix=')' suffixOverrides=','>" +
 "<if test='username!=null'>username,</if>" +
 "<if test='password!=null'>password,</if>" +
 "<if test='age!=null'>age,</if>" +
 "<if test='gender!=null'>gender,</if>" +
 "<if test='phone!=null'>phone,</if>" +
 "</trim>" +
 "VALUES " +
 "<trim prefix='(' suffix=')' suffixOverrides=','>" +
 "<if test='username!=null'>#{username},</if>" +
 "<if test='password!=null'>#{password},</if>" +
 "<if test='age!=null'>#{age},</if>" +
 "<if test='gender!=null'>#{gender},</if>" +
 "<if test='phone!=null'>#{phone}</if>" +
 "</trim>"+
 "</script>")
Integer insertUserByCondition(UserInfo userInfo);

< where >标签

xml实现

<select id="queryByCondition" resultType="com.example.demo.model.UserInfo">
 select id, username, age, gender, phone, delete_flag, create_time, 
update_time
 from userinfo
 <where>
 <if test="age != null">
 and age = #{age}
 </if>
 <if test="gender != null">
 and gender = #{gender}
 </if>
<if test="deleteFlag != null">
 and delete_flag = #{deleteFlag}
 </if>
 </where>
</select>

注解方式

@Select("<script>select id, username, age, gender, phone, delete_flag, 
create_time, update_time" +
 " from userinfo" +
 " <where>" +
 " <if test='age != null'> and age = #{age} </if>" +
 " <if test='gender != null'> and gender = #{gender} </if>" +
 " <if test='deleteFlag != null'> and delete_flag = #
{deleteFlag} </if>" +
 " </where>" +
 "</script>")
List<UserInfo> queryByCondition(UserInfo userInfo);

<where>只会在⼦元素有内容的情况下才插⼊where⼦句，⽽且会⾃动去除⼦句的开头的AND或 OR

以上标签也可以使⽤<trim prefix="where" prefixOverrides="and">替换,但是此种情况下,当⼦元素都没有内容时,where关键字也会保留

< set >标签

动态的在SQL语句中插⼊set关键字，并会删掉额外的逗号.(⽤于update语句中)

也可以使⽤<trim prefix="set" suffixOverrides=",">替换

xml方式

<update id="updateUserByCondition">
 update userinfo
 <set>
 <if test="username != null">
 username = #{username},
 </if>
 <if test="age != null">
 age = #{age},
 </if>
 <if test="deleteFlag != null">
 delete_flag = #{deleteFlag},
 </if>
 </set>
 where id = #{id}
</update>

注解方式

@Update("<script>" +
 "update userinfo " +
 "<set>" +
 "<if test='username!=null'>username=#{username},</if>" +
 "<if test='age!=null'>age=#{age},</if>" +
 "<if test='deleteFlag!=null'>delete_flag=#{deleteFlag},</if>" +
 "</set>" +
 "where id=#{id}" +
 "</script>")
Integer updateUserByCondition(UserInfo userInfo);

< foreach >标签

对集合进行遍历

• collection：绑定⽅法参数中的集合，如List，Set，Map或数组对象

• item：遍历时的每⼀个对象

• open：语句块开头的字符串

• close：语句块结束的字符串

• separator：每次遍历之间间隔的字符串

void deleteByIds(List<Integer> ids);

xml方式 

<delete id="deleteByIds">
 delete from userinfo
 where id in
 <foreach collection="ids" item="id" separator="," open="(" close=")">
 #{id}
 </foreach>
</delete>

注解方式 

@Delete("<script>" +
 "delete from userinfo where id in" +
 "<foreach collection='ids' item='id' separator=',' open='(' 
close=')'>" +
 "#{id}" +
 "</foreach>" +
 "</script>")
Integer deleteUser(Integer id);

< include >标签

对重复代码片段使用<sql>封装，再通过<include>标签进行引用

<sql id="allColumn">
 id, username, age, gender, phone, delete_flag, create_time, update_time
</sql>

<select id="queryAllUser" resultMap="BaseMap">
 select
 <include refid="allColumn"></include>
 from userinfo
</select>
<select id="queryById" resultType="com.example.demo.model.UserInfo">
 select
 <include refid="allColumn"></include>
 from userinfo where id= #{id}
</select>