mybatis之动态SQL

一、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);
    }