一、if 标签
<mapper namespace="com.powernode.mybatis.mapper.CarMapper">
<select id="selectByMultiCondition" resultType="car">
select * from t_car where
<if test="brand != null and brand != ''">
brand like #{brand}"%"
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price >= #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</select>
</mapper>如果第一个条件为空，剩下两个条件不为空，报错，SQL 语法有问题，where 后面出现了 and
解决方案：where 后面添加一个恒成立的条件
此时不管 if 标签是否生效都不会影响 SQL 语句正常执行
<mapper namespace="com.powernode.mybatis.mapper.CarMapper">
<select id="selectByMultiCondition" resultType="car">
select * from t_car where 0 = 0
<if test="brand != null and brand != ''">
and brand like #{brand}"%"
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price >= #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</select>
</mapper>二、where 标签
where 标签的作用：让 where 子句更加动态智能。
-
所有条件都为空时，where 标签保证不会生成 where 子句
-
自动去除某些条件前面多余的 and 或 or（后面多余的 and 不会被去除）
<select id="selectByMultiConditionWithWhere" resultType="car">
select * from t_car
<where>
<if test="brand != null and brand != ''">
and brand like #{brand}"%"
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price >= #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</where>
</select>三、tirm 标签
trim 标签的属性：
-
prefix：在 trim 标签中的语句前添加内容
-
suffix：在 trim 标签中的语句后添加内容
-
prefixOverrides：前缀覆盖掉（去掉）
-
suffixOverrides：后缀覆盖掉（去掉）
如果所有条件为空，where 不会被加上
<select id="selectByMultiConditionWithTrim" resultType="car">
select * from t_car
<trim prefix="where" suffixOverrides="and|or">
<if test="brand != null and brand != ''">
brand like #{brand}"%" and
</if>
<if test="guidePrice != null and guidePrice != ''">
guide_price >= #{guidePrice} and
</if>
<if test="carType != null and carType != ''">
car_type = #{carType}
</if>
</trim>
</select>四、set 标签
主要使用在 update 语句当中，用来生成 set 关键字，同时去掉最后多余的 “,”
比如只更新提交的不为空的字段，如果提交的数据是空或者""，那么这个字段我们将不更新。
<update id="updateWithSet">
update t_car
<set>
<if test="carNum != null and carNum != ''">
car_num = #{carNum},
</if>
<if test="brand != null and brand != ''">
brand = #{brand},
</if>
<if test="guidePrice != null and guidePrice != ''">
guide_price = #{guidePrice},
</if>
<if test="produceTime != null and produceTime != ''">
produce_time = #{produceTime},
</if>
<if test="carType != null and carType != ''">
car_type = #{carType},
</if>
</set>
where id = #{id}
</update>五、choose when otherwise
这三个标签是在一起使用的
<choose>
<when></when>
<when></when>
<when></when>
<otherwise></otherwise>
</choose>等同于
if(){
}else if(){
}else if(){
}else if(){
}else{
}只有一个分支会被选择！！！！
六、foreach 标签
循环数组或集合，动态生成 sql，比如这样的 SQL：
delete from t_car where id in(1,2,3);
delete from t_car where id = 1 or id = 2 or id = 3;insert into t_car values
(null,'1001','凯美瑞',35.0,'2010-10-11','燃油车'),
(null,'1002','比亚迪唐',31.0,'2020-11-11','新能源'),
(null,'1003','比亚迪宋',32.0,'2020-10-11','新能源')批量删除
-
使用 in 删除
/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach(@Param("ids") Long[] ids);<!--
collection：集合或数组
item：集合或数组中的元素
separator：分隔符
open：foreach 标签中所有内容的开始
close：foreach 标签中所有内容的结束
-->
<delete id="deleteBatchByForeach">
delete from t_car where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>@Test
public void testDeleteBatchByForeach(){
CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
int count = mapper.deleteBatchByForeach(new Long[]{40L, 41L, 42L});
System.out.println("删除了几条记录：" + count);
SqlSessionUtil.openSession().commit();
}
-
使用 or 删除
/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach2(@Param("ids") Long[] ids);<delete id="deleteBatchByForeach2">
delete from t_car where
<foreach collection="ids" item="id" separator="or">
id = #{id}
</foreach>
</delete>@Test
public void testDeleteBatchByForeach2(){
CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
int count = mapper.deleteBatchByForeach2(new Long[]{40L, 41L, 42L});
System.out.println("删除了几条记录：" + count);
SqlSessionUtil.openSession().commit();
}
批量添加
/**
* 批量添加，使用foreach标签
* @param cars
* @return
*/
int insertBatchByForeach(@Param("cars") List<Car> cars);<insert id="insertBatchByForeach">
insert into t_car values
<foreach collection="cars" item="car" separator=",">
(null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
</foreach>
</insert>@Test
public void testInsertBatchByForeach(){
CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
Car car1 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");
Car car2 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");
Car car3 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");
List<Car> cars = Arrays.asList(car1, car2, car3);
int count = mapper.insertBatchByForeach(cars);
System.out.println("插入了几条记录" + count);
SqlSessionUtil.openSession().commit();
}
七、sql 标签与 include 标签
sql 标签用来声明 sql 片段
include 标签用来将声明的 sql 片段包含到某个 sql 语句当中
作用：代码复用、易维护
<sql id="carCols">id,car_num carNum,brand,guide_price guidePrice,produce_time produceTime,car_type carType</sql>
<select id="selectAllRetMap" resultType="map">
select <include refid="carCols"/> from t_car
</select>
<select id="selectAllRetListMap" resultType="map">
select <include refid="carCols"/> carType from t_car
</select>
<select id="selectByIdRetMap" resultType="map">
select <include refid="carCols"/> from t_car where id = #{id}
</select>一 叶 知 秋，奥 妙 玄 心
