trim+if 标签

trim+if 标签构建插入语句

//mapper接口 int insert(User user); //SQL <insert id="insert"> insert into t_user <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null and id != ''"> id, </if> <if test="name != null and name != ''"> name, </if> <if test="age != null and age != ''"> age, </if> <if test="email != null and email != ''"> email, </if> </trim> <trim prefix=" values (" suffix=")" suffixOverrides=","> <if test="id != null and id != ''"> #{id}, </if> <if test="name != null and name != ''"> #{name}, </if> <if test="age != null and age != ''"> #{age}, </if> <if test="email != null and email != ''"> #{email}, </if> </trim> </insert>

prefix和suffix给trim标签内的语句加上左右括号，suffixOverrides则去掉最后一个逗号（这是在value值后写逗号的情况，如逗号写在前面则使用prefixOverrides）。

测试

//传入待插入的实体 User user = new User("Loki",15,"LOKI@QQ.com"); //SQL insert into t_user ( name, age, email ) values ( ?, ?, ? ) //传入待插入的实体 User user = new User("Loki",15); //SQL insert into t_user ( name, age) values ( ?, ?)

foreach标签

foreach实现批量查询/删除等

//第一种 <select id="selectList1" resultType="com.mp.mybatisplus.entity.User"> select * from t_user where id in <foreach collection="list" item="id" open="(" close=")" separator=","> #{id} </foreach> </select> //第二种，使用trim来实现id的拼接 <select id="selectList2" resultType="com.mp.mybatisplus.entity.User"> select * from t_user where id in <trim prefix="(" suffix=")" suffixOverrides=","> <foreach collection="list" item="id"> #{id}, </foreach> </trim> </select>

测试

List<Integer> idList = new ArrayList<>(); idList.add(1); idList.add(4); List<User> users = userMapper.selectList(idList); //SQL select * from t_user where id in ( ? , ? )

set标签

set标签实现update语句

<update id="Update"> update t_user <set> <if test="name != null and name != ''"> name = #{name}, </if> <if test="age != null and age != ''"> age = #{age}, </if> <if test="email != null and email != ''"> email = #{email}, </if> </set> where id = #{id} </update> //使用trim进行拼接 <update id="Update2"> update t_user <trim prefix="set" suffixOverrides=","> <if test="name != null and name != ''"> name = #{name}, </if> <if test="age != null and age != ''"> age = #{age}, </if> <if test="email != null and email != ''"> email = #{email}, </if> </trim> where id = #{id} </update>

bind标签

bind标签构建模糊查询参数

<select id="xmlSelectLike" resultType="com.mp.mybatisplus.entity.User"> <bind name="name" value="'%'+name+'%'"/> select * from t_user where name like #{name} </select> //模糊查询使用 CONCAT('%',#{name},'%') 更为方便

choose / when / otherwise 标签

一时间没想到什么好的例子，就随便写一个。

//根据ID查询，如果传入参数为空，则默认查询ID为1的用户 <select id="selectById" resultType="com.mp.mybatisplus.entity.User"> select * from t_user <choose> <when test="id != null and id != ''"> where id = #{id} </when> <otherwise> where id = 1 </otherwise> </choose> </select>