一、if标签
- if标签中的set属性是必须的。
- if标签中的test属性如果是true，SQL语句就会拼接。反之则不拼接
- 在mybatis中的动态SQL语句当中，不能使用&&，只能使用and、
【CarMapper.xml样例】
<select id="selectByMultiCondition" resultType="car">
select
car_num as carNum,
brand,
guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
<!--'2=2'语句是为了确保三个条件都为空时，SQL语句还能够正常执行-->
where 2=2
<if test="brand != null and brand != '' ">
brand like "%"#{brand}"%"
</if>
<if test="guidePrice != null and guidePrice != '' ">
and guide_price > #{guidePrice}
</if>
<if test="brand != null and brand != '' ">
and car_type = #{carType}
</if>
</select>【测试样例】：
@Test
public void testSelectByMultiCondition() {
SqlSession sqlSession = SqlSessionUtil.openSession();
com.mapper.CarMapper mapper = sqlSession.getMapper(com.mapper.CarMapper.class);
List<Car> cars = mapper.selectByMultiCondition("比亚迪", 2.0, "新能源");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}二、where标签
【作用】：让where标签更加动态智能。
- 所有条件都为空时，where标签保证不会生成where语句
- 自动某些条件前面(注意条件后面跟的and是去不掉的多余的and或or
【CarMapper.xml样例】：
<select id="selectByMultiConditionWithWhere" resultType="Car">
select
car_num as carNum,
brand,
guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
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="brand != null and brand != '' ">
and car_type = #{carType}
</if>
</where>
</select>【测试样例】：
@Test
public void testSelectByMultiConditionWithWhere() {
SqlSession sqlSession = SqlSessionUtil.openSession();
com.mapper.CarMapper mapper = sqlSession.getMapper(com.mapper.CarMapper.class);
//List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", 2.0, "新能源");
List<Car> cars = mapper.selectByMultiConditionWithWhere("", null, "");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}三、trim标签
- prefix：在trim标签中的语句前面添加内容
- suffix: 在trim标签中的语句后面添加内容
- prefixOverrides:前缀覆盖掉(去除)
- suffixOverrides:后缀覆盖掉(去除)
【CarMapper.xml样例】：
<select id="selectByMultiConditionWithTrim" resultType="car">
select
car_num as carNum,
brand,
guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
<!--
prefix="where" :在trim标签所有内容的前面加上where
suffixOverrides="and|or" :把trim标签内容后面的and|or去掉
-->
<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="brand != null and brand != '' ">
car_type = #{carType} and
</if>
</trim>
</select>【测试样例】：
@Test
public void testselectByMultiConditionWithTrim() {
SqlSession sqlSession = SqlSessionUtil.openSession();
com.mapper.CarMapper mapper = sqlSession.getMapper(com.mapper.CarMapper.class);
//List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪", 2.0, "新能源");
List<Car> cars = mapper.selectByMultiConditionWithTrim("比亚迪", null, "");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}四、set标签
【作用】：主要使用在update语句当中，用来生成set关键字，同时去掉最后多余的" ，",就是说只更新我们提交的不为空的数据
【CarMapper.xml样例】
<update id="updateBySet">
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>【测试样例】：
public void testUpdateBySet() {
SqlSession sqlSession = SqlSessionUtil.openSession();
com.mapper.CarMapper mapper = sqlSession.getMapper(com.mapper.CarMapper.class);
Car car = new Car(19L, null, "丰田霸道", null, null, "燃油车");
int i = mapper.updateBySet(car);
System.out.println(i);
sqlSession.commit();
sqlSession.close();
}五、choose标签
【注意】：choose when otherwise三个标签是在一起的
<choose>
<when></when>
<when></when>
<when></when>
<otherwise></otherwise>
</choose>if () {
} else if () {
} else if () {
} else if () {
} else {
}只有一个条件会被选择
【需求示范】：先根据品牌查询，如果没有提供品牌，再根据指导价格查询，如果没有提供指导价格，就根据知道日期查询。
【CarMapper.xml样例】：
<select id="selectByChoose" resultType="car">
select
car_num as carNum,
brand,
guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from
t_car
<where>
<choose>
<!--语句前面不需要加and，应为此时where后面只会有一条语句-->
<when test="brand != null and brand != '' ">
brand like "%"#{brand}"%"
</when>
<when test="guidePrice != null and guidePrice != '' ">
guide_price > #{guidePrice}
</when>
<otherwise>car_type=#{carType}</otherwise>
</choose>
</where>
</select>【测试样例】：
@Test
public void testSelectByChoose() {
SqlSession sqlSession = SqlSessionUtil.openSession();
com.mapper.CarMapper mapper = sqlSession.getMapper(com.mapper.CarMapper.class);
List<Car> cars = mapper.selectByChoose("东风", null, null);
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}六、foreach标签
foreach标签属性：
- collection：指定数组或集合
- item：代表数组或集合中的元素
- separator:循环之间的分隔符
- open：foreach循环拼接的SQL语句的最前面以什么开始
- close：foreach循环拼接的SQL语句的最前面以什么结束
①批量删除
【CarMapper.xml样例】：
<delete id="deleteByIds">
delete from t_car where id in(
<!--相当于ids集合的遍历，将集合中的id动态转换为#{id1},#{id2},#{id3}.....-->
<foreach collection="ids" item="id" separator=",">
#{id}
</foreach>
<!-- 第二种写法
<foreach collection="ids" item="id" separator="," open="(" close="）">
#{id}
</foreach>
-->
)
</delete>【测试样例】：
@Test
public void testDeleteById() {
SqlSession sqlSession = SqlSessionUtil.openSession();
com.mapper.CarMapper mapper = sqlSession.getMapper(com.mapper.CarMapper.class);
Long[] ids={12L,9L};
int i = mapper.deleteByIds(ids);
System.out.println("i = " + i);
}②批量插入
【CarMapper.xml样例】：
<insert id="insertBatch">
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 testInsertBatch() {
SqlSession sqlSession = SqlSessionUtil.openSession();
com.mapper.CarMapper mapper = sqlSession.getMapper(com.mapper.CarMapper.class);
List<Car> cars=new ArrayList<>();
Car car1 = new Car(null,"1211","帕萨特",23.0,"2022-10-22","燃油车");
Car car2 = new Car(null,"1212","梅赛德斯",21.0,"2022-11-23","燃油车");
cars.add(car1);
cars.add(car2);
int i = mapper.insertBatch(cars);
System.out.println("i = " + i);
}
