【MyBatis】动态SQL第一篇

需求：经常多条件查询或更新。

1.if标签

语法格式

 select * from 表名 1 = 1
      <if test="参数1!= null and 参数1 != ''">
             and 属性名1 like "%"#{参数1}"%"
      </if>
      <if test="参数2!= null and 参数2!= ''">
             and 属性名2 > #{参数2}
      </if>
      <if test="参数3!= null and 参数3!= ''">
             and 属性名3= #{参数3}
      </if>

测试代码

CarMapper

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

CarMapper.xml

<select id="selectByMultiCondition" resultType="com.powernode.mybatis.pojo.Car">
        select * from t_car where 1 = 1
        <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>

Test

   @Test
    public void testSelectByMultiCondition(){
        SqlSession sqlSession = SqlSessionUntil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByMultiCondition("比亚迪",null,"");
        cars.forEach( car -> System.out.println(car));
        sqlSession.close();
    }

总结

测试结果：

结论:只有在where条件后加上 1 = 1 并且在第一个if标签里最前面加上and才能通过所有测试

2.where标签

语法格式

 select * from 表名
        <where>
            <if test="参数1!= null and 参数1 != ''">
                and 属性名1 like "%"#{参数1}"%"
            </if>
            <if test="参数2!= null and 参数2!= ''">
                and 属性名2 > #{参数2}
            </if>
            <if test="参数3!= null and 参数3!= ''">
                and 属性名3= #{参数3}
            </if>
        </where>

作用

where标签的作用：让where子句更加动态智能。

• 自动生成where关键字
• 所有条件都为空时，where标签保证不会生成where子句。
• 自动去除某些条件前面多余的and或or。

测试代码

CarMapper

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

CarMapper.xml

    <select id="selectByMultiConditionWithWhere" resultType="com.powernode.mybatis.pojo.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>

Test

    @Test
    public void testSelectByMultiConditionWithWhere(){
        SqlSession sqlSession = SqlSessionUntil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByMultiConditionWithWhere("比亚迪",null,"");
        cars.forEach( car -> System.out.println(car));
        sqlSession.close();
    }

总结

测试结果：测试通过

结论:自动去掉if标签里前面多余的and，后面多余的and是不会被去除的，编写xml文件时需要注意将and 尽可能写前面

3.trim标签

语法格式

 select * from 表名
         <trim prefix="where" suffixOverrides="and|or">
            <if test="参数1!= null and 参数1 != ''">
                 属性名1 like "%"#{参数1}"%" and
            </if>
            <if test="参数2!= null and 参数2!= ''">
                 属性名2 > #{参数2} and
            </if>
            <if test="参数3!= null and 参数3!= ''">
                and 属性名3= #{参数3}
            </if>
            ...
         </trim>

trim标签的属性：

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

测试代码

CarMapper

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

CarMapper.xml

 <select id="selectByMultiConditionWithTrim" resultType="com.powernode.mybatis.pojo.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

   @Test
    public void testSelectByMultiConditionWithTrim(){
        SqlSession sqlSession = SqlSessionUntil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> cars = mapper.selectByMultiConditionWithTrim("比亚迪",null,"");
        cars.forEach( car -> System.out.println(car));
        sqlSession.close();
    }

总结

测试结果：测试通过

4.set标签

语法格式

    update 表名
        <set>
            <if test="参数1 != null and 参数1!= ''">
                car_num = #{参数1},
            </if>
            <if test="参数2 != null and 参数2 != ''">
                brand = #{参数2 },
            </if>
            <if test="参数3 != null and 参数3 != ''">
                guide_price = #{guidePrice},
            </if>
            ...
        </set>
        where id = #{id}

作用

• 用来生成set关键字，同时去掉最后多余的“,”

测试代码

CarMapper

int updateBySet(Car car);

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>

Test

    @Test
    public void testUpdateBySet(){
        SqlSession sqlSession = SqlSessionUntil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(209L,null,"丰田霸道",null,null,"");
        mapper.updateBySet(car);
        sqlSession.commit();
        sqlSession.close();
    }

总结

测试结果：测试通过

结论:

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

下一篇 : 动态sql第二篇