Mybatis动态sql

1.if标签

需求：多条件查询

<select id="pageQuery" resultType="com.sky.entity.Employee">
    select * from employee
    <where>
        <if test="name!=null and name!=''">
            and name like concat('%',#{name},'%')
        </if>
    </where>
    order by create_time desc
</select>

if中test属性为true，if体内代码作为查询条件拼接，如上，根据名字进行模糊查询员工信息，如果名字为空则不拼接，不查询，如果不为空则进行模糊查询。

2.where标签

<select id="pageQuery" resultType="com.sky.entity.Employee">
    select * from employee
    <where>
        <if test="name!=null and name!=''">
            and name like concat('%',#{name},'%')
        </if>
    </where>
    order by create_time desc
</select>

同样是这段代码

where标签可以让mybatis更加智能化，它能自动去除前面的and，or关键字

完成条件查询

3.trim标签

trim标签的属性：

• prefix：在trim标签中的语句前添加内容
• suffix：在trim标签中的语句后添加内容
• prefixOverrides：前缀覆盖掉（去掉）
• suffixOverrides：后缀覆盖掉（去掉）

<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>
 

@Test
public void testSelectByMultiConditionWithTrim(){
    CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
    List<Car> cars = mapper.selectByMultiConditionWithTrim("丰田", 20.0, "");
    System.out.println(cars);
}

4.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>

@Test
public void testUpdateWithSet(){
    CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
    Car car = new Car(38L,"1001","丰田霸道2",10.0,"",null);
    int count = mapper.updateWithSet(car);
    System.out.println(count);
    SqlSessionUtil.openSession().commit();
}

5.choose when otherwise标签（这三个是一起用的）

<choose>
  <when></when>
  <when></when>
  <when></when>
  <otherwise></otherwise>
</choose>

等同于

if(){
    
}else if(){
    
}else if(){
    
}else if(){
    
}else{

}

需求：先根据品牌查询，如果没有提供品牌，再根据指导价格查询，如果没有提供指导价格，就根据生产日期查询。

<select id="selectWithChoose" resultType="car">
    select * from t_car
    <where>
        <choose>
            <when test="brand != null and brand != ''">
                brand like #{brand}"%"
            </when>
            <when test="guidePrice != null and guidePrice != ''">
                guide_price >= #{guidePrice}
            </when>
            <otherwise>
                produce_time >= #{produceTime}
            </otherwise>
        </choose>
    </where>
</select>

6.foreach标签

-->

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();
}