mybatis-动态sql

1、if标签

需求：多条件查询。
可能的条件包括：品牌（brand）、指导价格（guide_price）、汽车类型（car_type）

List<Car> selectCar(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);

1
2

 <select id="selectCar" 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>
1
2
3
4
5
6
7
8
9
10
11
12
13

 @Test
    public void testmanyParams(){
        SqlSession sqlSession = MybatisUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectCar("丰田", 1.0, "氢能源");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }

1
2
3
4
5
6
7
8
9

但是如果第一个为空，第二三个不为空呢？

我们可以看到 出现了错误，即前边多了一个and。
此时就需要有where 标签

2、where标签

where标签的作用：让where子句更加动态智能。
● 所有条件都为空时，where标签保证不会生成where子句。
● 自动去除某些条件前面多余的and或or。
继续使用if标签中的需求。

 <select id="selectCar" 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>
        </where>
    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如果让第一个为空，此时不会出现错误，会自动帮我们去掉多余的and。

如果and 在后边，则无法去掉.只能去掉前边的and或者or

  <select id="selectCar" resultType="car">
        select  * from t_car
        <where>
        <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>
        </where>
    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3、trim标签

可以自动增加前缀，后缀，删除前缀，删除后缀。
举例
会自动帮助我们增加前缀 where 删除多余的后缀and

 <select id="selectCar" resultType="car">
        select  * from t_car
        <trim prefix="where" suffixOverrides="and">
            <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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

  @Test
    public void testmanyParams(){
        SqlSession sqlSession = MybatisUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectCar("丰田", null, "");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
1
2
3
4
5
6
7
8

4、set标签

主要使用在update语句当中，用来生成set关键字，同时去掉最后多余的“,”
比如我们只更新提交的不为空的字段，如果提交的数据是空或者""，那么这个字段我们将不更新。

/**
* 更新信息，使用set标签
* @param car
* @return
*/
int updateWithSet(Car car);
1
2
3
4
5
6

<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>
1
2
3
4
5
6
7
8
9
10
11

  @Test
    public void testUpdateWithSet(){
        SqlSession sqlSession = MybatisUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(8,"1001","丰田霸道2",10.0,"",null);
        int count = mapper.updateWithSet(car);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }
1
2
3
4
5
6
7
8
9
10

5、choose when otherwise

这三个标签是在一起使用的：

<choose>
  <when></when>
  <when></when>
  <when></when>
  <otherwise></otherwise>
</choose>
1
2
3
4
5
6

等同于

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

}
1
2
3
4
5
6
7
8
9
10
11

只有一个分支会被选择！！！！
需求：先根据品牌查询，如果没有提供品牌，再根据指导价格查询，如果没有提供指导价格，就根据生产日期查询。

List<Car> selectWithChoose(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("produceTime") String produceTime);
1

<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

@Test
public void testSelectWithChoose(){
    CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
    //List<Car> cars = mapper.selectWithChoose("丰田霸道", 20.0, "2000-10-10");
    //List<Car> cars = mapper.selectWithChoose("", 20.0, "2000-10-10");
    //List<Car> cars = mapper.selectWithChoose("", null, "2000-10-10");
    List<Car> cars = mapper.selectWithChoose("", null, "");
    System.out.println(cars);
}
1
2
3
4
5
6
7
8
9

只会执行一个分支！！一定会执行一个，如果前几个都没有对应上，就一定会执行最后一个。

6 、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;
1
2

批量添加

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','新能源')
1
2
3
4

6.1 用in来删除

    int deleteBatchByForeach(@Param("ids") Integer[] ids);
1

    <delete id="deleteBatchByForeach">
        delete from t_car where id in
        (
        <foreach collection="ids" item="id" separator=",">#{id}
        </foreach
        >)
    </delete>
1
2
3
4
5
6
7

也可以

    <delete id="deleteBatchByForeach">
        delete from t_car where id in
        
        <foreach collection="ids" item="id" separator="," open="(" close=")">#{id}
        </foreach
        >
    </delete>
1
2
3
4
5
6
7

   @Test
    public void testDeleteBatchByForeach(){
        SqlSession sqlSession = MybatisUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        int count = mapper.deleteBatchByForeach(new Integer[]{2, 3, 4});
        System.out.println("删除了几条记录：" + count);
        sqlSession.commit();
    }
1
2
3
4
5
6
7
8

6.2 用or来删除

    <delete id="deleteBatchByForeach">
        delete from t_car where
        <foreach collection="ids" item="id" separator="or">
         id=#{id}
        </foreach
        >
    </delete>
1
2
3
4
5
6
7

    @Test
    public void testDeleteBatchByForeach(){
        SqlSession sqlSession = MybatisUtils.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        int count = mapper.deleteBatchByForeach(new Integer[]{2, 3, 4});
        System.out.println("删除了几条记录：" + count);
        sqlSession.commit();
    }
1
2
3
4
5
6
7
8

6.3 批量添加

int insertBatchByForeach(@Param("cars") List<Car> cars);
1

 <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>
1
2
3
4
5
6

   @Test
    public void testInsertBatchByForeach(){
        SqlSession sqlSession = MybatisUtils.openSession();
        CarMapper mapper = sqlSession.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+"条记录");
        sqlSession.commit();
        sqlSession.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

7、 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>
1
2
3
4
5
6
7
8
9
10
11
12
13