mybatis之动态SQL

    对于一些复杂的查询,我们可能会指定多个查询条件,但是这些条件可能存在也可能不存在,例如在58同城上面找房子,我们可能会指定面积、楼层和所在位置来查找房源,也可能会指定面积、价格、户型和所在位置来查找房源,此时就需要根据用户指定的条件动态生成SQL语句。如果不使用持久层框架我们可能需要自己拼装SQL语句,还好MyBatis提供了动态SQL的功能来解决这个问题。MyBatis中用于实现动态SQL的元素主要有:

- if

- choose (when, otherwise)

- trim (where, set)

- foreach

由于每次建立工程比较复杂,可以参考第一节:mybatis入门来搭建一个简单的工程,然后来测试本节内容。

1、动态sql参数传递方式


当我们关注动态sql的时候,其实要查询的参数不止一个,这个时候,我们通常有如下几个选择:

  1. 通过Param注解,在方法中使用多个参数
  2. 使用POJO或者TO
  3. 使用map

下面测试的时候,将分别介绍,首先构造一个场景,我们按照age和address进行查询。因此mapper接口方法为:

public interface PersonMapper
{
    List<Person> getPersonByParam(@Param("age") Integer age, @Param("address") String address);
    List<Person> getPersonByPOJO(Person person);
    List<Person> getPersonByMap(Map<String,Object> map);
}

编写好mapper接口之后,再来编写mapper映射文件。上面三个方法虽然参数形式不同,但是下面select里面除了id不同,其它都是相同的。

<mapper namespace="com.yefengyu.mybatis.mapper.PersonMapper">
    <select id="getPersonByParam" resultType="com.yefengyu.mybatis.entity.Person">
        select id, first_name firstName, last_name lastName, age, email, address  from person where age > #{age} and address = #{address}
    </select>
    <select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person">
        select id, first_name firstName, last_name lastName, age, email, address  from person where age > #{age} and address = #{address}
    </select>
    <select id="getPersonByMap" resultType="com.yefengyu.mybatis.entity.Person">
        select id, first_name firstName, last_name lastName, age, email, address  from person where age > #{age} and address = #{address}
    </select>
</mapper>

下面进行测试:

package com.yefengyu.mybatis;

import com.yefengyu.mybatis.mapper.PersonMapper;
import com.yefengyu.mybatis.entity.Person;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class Main
{
    public static void main(String[] args)
        throws IOException
    {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);

        //注解形式参数
         List<Person> personByParam = mapper.getPersonByParam(20, "beijing");
        System.out.println(personByParam.size());

        //POJO形式参数
         Person person = new Person();
        person.setAge(20);
        person.setAddress("beijing");
        List<Person> personByPOJO = mapper.getPersonByPOJO(person);
        System.out.println(personByPOJO.size());

        //map形式参数
         Map<String, Object> map = new HashMap<>();
        map.put("age",20);
        map.put("address","beijing");
        List<Person> personByMap = mapper.getPersonByMap(map);
        System.out.println(personByMap.size());

        sqlSession.close();
    }
}

    上面的代码中三种参数传递中都使用age和address两个参数作为过滤条件,因此查询都没有问题。但是现在我们将上面代码修改一下,不传address的值,也就是不使用address作为过滤条件,只使用age作为过滤条件。

1、mapper.getPersonByParam(20, null);

2、注释此句://person.setAddress("beijing");

3、注释此句://map.put("address","beijing");

//注解形式参数
List<Person> personByParam = mapper.getPersonByParam(20, null);
System.out.println(personByParam.size());

//POJO形式参数
Person person = new Person();
person.setAge(20);
//person.setAddress("beijing");
List<Person> personByPOJO = mapper.getPersonByPOJO(person);
System.out.println(personByPOJO.size());

//map形式参数
Map<String, Object> map = new HashMap<>();
map.put("age",20);
//map.put("address","beijing");
List<Person> personByMap = mapper.getPersonByMap(map);
System.out.println(personByMap.size());

    我们期望如果不传address值的时候,将不再使用address作为过滤条件,但是查询结果却是0条数据,这是因为sql中 address = null 也作为查询条件,自然查询不到期望的结果。那么怎么实现我们的需求呢?

2、if


我们可以使用if标签来解决上面的问题。

    <select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person">
        select id, first_name firstName, last_name lastName, age, email, address from person
        where
        <if test="age!=null">
            age > #{age}
        </if>
        <if test="address!=null and address!=''">
            and address = #{address}
        </if>
    </select>

上面使用if标签,如果age不为null,就把age > #{age} 拼接到where之后,同理,如果address的条件满足,也会拼接在后面。

有4种情况(✔表示传递此属性,❌表示不传递此属性):

序号 age address sql
1 select id, first_name firstName, last_name lastName, age, email, address from person where age > ? and address = ?
2 select id, first_name firstName, last_name lastName, age, email, address from person where age > ?
3 select id, first_name firstName, last_name lastName, age, email, address from person where  and address = ?
4 select id, first_name firstName, last_name lastName, age, email, address from person where

可以看出后两种有明显的问题,简单的解决之法是在where后面添加 1=1,第一个if条件前面加and,如下:

    <select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person">
        select id, first_name firstName, last_name lastName, age, email, address from person
        where 1=1
        <if test="age!=null">
            and age > #{age}
        </if>
        <if test="address!=null and address!=''">
            and address = #{address}
        </if>
    </select>
这个时候4种情况对应的sql为,可以正常查询数据。
序号 age address sql
1 select id, first_name firstName, last_name lastName, age, email, address from person where 1=1 and age > ? and address = ?
2 select id, first_name firstName, last_name lastName, age, email, address from person where 1=1 and  age > ?
3 select id, first_name firstName, last_name lastName, age, email, address from person where 1=1 and address = ?
4 select id, first_name firstName, last_name lastName, age, email, address from person where  1=1

借助1=1可以解决sql拼接出现的一系列问题,但是mybatis自身也提供了更强大的标签来解决这些问题。

3、where


使用where标签可以解决上面sql拼接出现的一系列问题,在使用的时候,不能同时在sql中书写where关键字。使用的时候,只需要将if判断包含在where标签里面即可。

<select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person">
    select id, first_name firstName, last_name lastName, age, email, address from person
    <where>
        <if test="age!=null">
            age > #{age}
        </if>
        <if test="address!=null and address!=''">
            and address = #{address}
        </if>
    </where>
</select>

where 标签只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。(解决第四条)

而且,若语句的开头为“AND”或“OR”,where 标签也会将它们去除。(解决第三条)

序号 age address sql
1 select id, first_name firstName, last_name lastName, age, email, address from person where  and age > ? and address = ?
2 select id, first_name firstName, last_name lastName, age, email, address from person where  age > ?
3 select id, first_name firstName, last_name lastName, age, email, address from person where address = ?
4 select id, first_name firstName, last_name lastName, age, email, address from person

4、set


该标签主要用在更新操作里面,如果传递了某个属性,就更新对应的字段。此处假如要根据id更新age或者address,传入哪个属性就更新哪个属性,那么使用动态sql就是如下的写法。

<update id="updatePerson">
    update person
    <set>
        <if test="age!=null">
            age = #{age},
        </if>
        <if test="address!=null and address!=''">
            address = #{address}
        </if>
    </set>
    where id = #{id}
</update>

此处讲解一下为什么需要使用set标签:

如果去掉set标签,在第一个if标签前面加一个 set 关键字,如下,同样会出现上面那种sql拼接问题:

  • 只传入age,where前面将多一个逗号。
  • 如果两个都不传,where 前面将有一个set关键字。
  • 如果都传则正常拼接sql
  • 只传address也正常拼接sql
<update id="updatePerson">
    update person set
    <if test="age!=null">
        age = #{age},
    </if>
    <if test="address!=null and address!=''">
        address = #{address}
    </if>
    where id = #{id}
</update>

这里,set 标签会动态前置 SET 关键字,同时也会删掉无关的逗号,因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。因为用的是“if”标签,若最后一个“if”没有匹配上而前面的匹配上,SQL 语句的最后就会有一个逗号遗留。

5、trim


trim标签功能比较强大,它的使用方式是:

<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
    ......    
</trim>

trim标签有四个属性,可以随机组合使用,分别表示:

  • prefix:在包围的语句前面加上前缀
  • prefixOverrides:在包围的语句最前面移除所有指定在 prefixOverrides 属性中的内容
  • suffix: 在包围的语句后面加上后缀
  • suffixOverrides:在包围的语句最后面移除所有指定在 suffixOverrides 属性中的内容

where标签等价于:注意此例中的空格也是必要的(AND |OR )。

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

set标签等价于:

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

trim和where、set标签是同一类标签。

6、choose, when, otherwise


    有时我们不想应用到所有的条件语句,而只想从中择其一项。针对这种情况,MyBatis 提供了 choose 标签,它有点像 Java 中的 switch 语句。现在我们想如果提供了age,就按照age条件过滤,不再关注其它条件。如果没有提供age,如果提供了address,就按照address条件过滤,其它即使有十个条件都满足也不过滤。

<select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person">
    select id, first_name firstName, last_name lastName, age, email, address from person where
    <choose>
        <when test="age!=null">
            age = #{age}
        </when>
        <when test="address!=null and address!=''">
            address = #{address}
        </when>
        <otherwise>
            1=1
        </otherwise>
    </choose>
</select>

注意:

1、where关键字是需要手写的。

2、choose包裹着不同的查询条件,when类似if功能,判断条件是否满足。

3、每个过滤条件前面不用加and,因为只有一条过滤条件会被执行。

4、最后可以加个otherwise,所有条件不满足时会执行此处过滤条件。

posted @ 2019-06-12 00:20  代码梦工厂  阅读(570)  评论(0编辑  收藏  举报