【MyBatis】动态SQL

if标签

CarMapper.java

/**
     * 多条件查询
     * @param brand 品牌
     * @param guidePrice 指导价
     * @param carType 汽车类型
     * @return
     */
    List<Car> selectByMultiCondition(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
1
2
3
4
5
6
7
8

CarMapper.xml

   <select id="selectByMultiCondition" resultType="car">
        select * from t_car where 1=1
--      1.if标签中test属性是必须的
--      2.if标签中test属性的值是false或true
--      3.如果test是true,则if标签中的sql语句就会拼接
--      4.test属性中可以使用的是：
--          当使用了@Param注解，那么test中要出现的是@Param注解指定的参数名，@Param("brand"),只能用brand
--          当没有使用注解，则出现param1 param2 arg0 arg1
--          当使用POJO，则出现POJO的属性名
        <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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

test.java

    @Test
    public void testSelectByMuItiCondition(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);

        //假设三个条件都不是空
        List<Car> cars = mapper.selectByMultiCondition("比亚泰迪",2.0,"新能源");

        //假设三个条件都是空

        //假设后两个条件不为空，第一个条件为空

        //假设第一个条件不为空，第二个条件为空

        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

where标签

作用：让where子句更加动态智能

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

 /**
     * 使用where标签
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */

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

1
2
3
4
5
6
7
8
9
10

xml

    <select id="selectByMultiConditionWithWhere" resultType="car">
        select * from t_car
--         where标签专门负责where子句动态生成的
        <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
15

test

 @Test
    public void testselectByMultiConditionWithWhere(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        //三个条件都是空
        List<Car> cars = mapper.selectByMultiConditionWithWhere("",null,"");
        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }
1
2
3
4
5
6
7
8
9

trim标签

接口

    /**
     * 使用trim标签
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);

1
2
3
4
5
6
7
8
9
10

xml

 <select id="selectByMultiConditionWithTrim" resultType="car">
        select * from t_car
        <!--
            prefix:加前缀
            suffix：加后缀
            prefixOverrides：删除前缀
            suffixOverrides：删除后缀
        -->
        <!--
            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="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
16
17
18
19
20
21
22
23
24

test

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

1
2
3
4
5
6
7
8
9

set标签

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

    /**
     * 使用set标签
     * @param car
     * @return
     */
    int updateBySet(Car car);
1
2
3
4
5
6

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

test

    @Test
    public void testUpdateBySet(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car=new Car(2L,null,"丰田道",null,null,"燃油车");
        mapper.updateBySet(car);
        sqlSession.commit();
        sqlSession.close();
    }
1
2
3
4
5
6
7
8
9
10

choose when otherwise

这三个标签在一起使用
接口

 /**
     * 使用choose when otherwise标签
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */

     List<Car> selectByChoose(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
    
1
2
3
4
5
6
7
8
9
10

xml

 <select id="selectByChoose" 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>
                    car_type=#{carType}
                </otherwise>
            </choose>
        </where>
    </select>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

test

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

foreach标签

批量删除

java

 /**
     * 批量删除，foreach标签
     * @param ids
     * @return
     */
   int deleteByIds(@Param("ids") Long[] ids);
1
2
3
4
5
6

xml

 <!--
    foreach标签的属性：
        collection:指定数组或集合
        item：代表数组或集合中的元素
        separator:循环之间的分隔符
        open foreach循环拼接的所有sql语句的最前面以什么开始
        colse foreach循环拼接的所有sql语句的最后面以什么结束
    -->
<!--    报错：Parameter 'ids' not found. Available parameters are [array, arg0]-->
<!--    可以使用@Param标签-->
<!--    <delete id="deleteByIds">-->
<!--        delete from t_car where id in(-->
<!--            <foreach collection="ids" item="id" separator=",">-->
<!--                #{id}-->
<!--            </foreach>-->
<!--            )-->
<!--    </delete>-->
<!--    另一种写法（没有括号的）-->
    <delete id="deleteByIds">
            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
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

test

  @Test
    public void testDeleteByIds(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long[] ids={7L,8L,9L};
        int count=mapper.deleteByIds(ids);
        System.out.println(count);
        sqlSession.commit();
        sqlSession.close();
    }
1
2
3
4
5
6
7
8
9
10

第二种写法
xml

<delete id="deleteByIds2">
        <foreach collection="ids" item="id" separator="or">
            id=#{id}
        </foreach>
    </delete>
1
2
3
4
5

批量添加

java

 /**
  * 批量插入，一次插入多条Car信息
  * @param cars
  * @return
  */
 int insertBatch(@Param("cars") List<Car> cars);
1
2
3
4
5
6

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

test

   @Test
    public void testinsertBatch(){
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car1=new Car(null,"1200","帕萨特",30.0,"2020-11-15","燃油车");
        Car car2=new Car(null,"1201","帕萨特CDS",50.0,"2021-11-15","燃油车");
        Car car3=new Car(null,"1202","奔驰",40.0,"2024-11-15","新能源");

        List<Car> cars=new ArrayList<>();
        cars.add(car1);
        cars.add(car2);
        cars.add(car3);
        mapper.insertBatch(cars);
        sqlSession.commit();
        sqlSession.close();
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

sql标签 include标签

sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句中