MyBatis学习-（七）动态SQL

本文最后更新于：January 24, 2022 pm

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

目录

MyBatis 中文文档：https://mybatis.org/mybatis-3/zh/index.html

动态SQL：同一个dao的方法，根据不同的条件可以表示不同的SQL语句。类似于重载。

基本代码

Person.java类

1
2
3
4
public class Person {
    private int id;
    private String name;
}

if 标签

可以有多个if，但没有else。

基本使用方法

1
2
3
<if test="boolean类型的判断条件">
    sql代码
</if>

条件是用对象的属性值作为条件。

具体在mapper文件中的使用：

1
2
3
4
5
6
7
8
9
10
11
<select id="queryPersonByID" resultType="com.tothefor.Study1.entity.Person">
    <!--  主SQL语句  -->
    select * from Person where
    <!--  部分SQL语句，当条件满足时，会将部分SQL语句加到主SQL语句的后面  -->
    <if test="条件">
       SQL语句
    </if>
    <if test="条件">
       SQL语句
    </if>
</select>

当满足某个条件时，会将对应的SQL语句加到主语句后面。

示例1（满足一个if）

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
26
<!-- ======= PersonDao.java 接口中的方法 ======= -->
public Person queryPersonByIf(Person person);

<!-- ======= PersonDao.xml mapper文件中 =======  -->
<select id="queryPersonByIf" resultType="com.tothefor.Study1.entity.Person">
    <!--  主SQL语句  -->
    select * from Person where
    <!--  部分SQL语句，当条件满足时，会将部分SQL语句加到主SQL语句的后面  -->
    <if test="id >= 0 and id != '' "> <!--  OGNL语法  -->
        id = #{id} <!--  前一个id是字段id，后一个id是对象的属性  -->
    </if>
    <if test="name!=null and name!='' ">
        name=#{name}
    </if>
</select>

<!-- ======= 测试类 ======= -->
PersonDao dao = sqlSession.getMapper(com.tothefor.Study1.dao.PersonDao.class);
Person person = new Person();
person.setId(1001); <!-- id -->
person.setName(null); <!-- name -->
Person result = dao.queryPersonByIf(person);
System.out.println("---> "+result);

<!-- ======= 最后实际的SQL语句 ======= -->
Preparing: select * from Person where id = ?  <!-- name为null，不满足条件 -->

当满足另外一个条件也是同样的用法。

示例二（满足多个或不满足if）

这种情况下，需要特殊处理。因为可能还存在一个条件也不满足的情况，所以需要特殊处理。就是在主SQL的条件后面加上一个必为假的判断条件，如：id=-1。

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
26
27
28
29
30
31
32
33
34
35
36
<!-- ======= PersonDao.java 接口中的方法 ======= -->
public Person queryPersonByIf(Person person);

<!-- ======= PersonDao.xml mapper文件中 =======  -->
<select id="queryPersonByIf" resultType="com.tothefor.Study1.entity.Person">
    <!--  主SQL语句  -->
    select * from Person where id=-1
    <!--  部分SQL语句，当条件满足时，会将部分SQL语句加到主SQL语句的后面  -->
    <if test="id >= 0 and id != '' "> <!--  OGNL语法  -->
        or id = #{id} <!--  前一个id是字段id，后一个id是对象的属性  -->
    </if>
    <if test="name!=null and name!='' ">
        or name=#{name}
    </if>
</select>

<!-- ======= 测试类中，一个也不满足 ======= -->
Person person = new Person();
person.setId(-1);
person.setName(null);
<!-- ======= 最后实际的SQL语句 ======= -->
Preparing: select * from Person where id = -1

<!-- ======= 测试类中，满足一个 ======= -->
Person person = new Person();
person.setId(1001);
person.setName(null);
<!-- ======= 最后实际的SQL语句 ======= -->
Preparing: select * from Person where id = -1 or id = ?

<!-- ======= 测试类中，满足两个 ======= -->
Person person = new Person();
person.setId(1001);
person.setName("lee");
<!-- ======= 最后实际的SQL语句 ======= -->
Preparing: select * from Person where id = -1 or id = ? or name=?

也可以看出if很容易引起SQL语法的错误（多and或or，少and或or），所以一般不单独使用，通常和where配合使用。

where 标签

在使用时，where里面有一个或多个if标签。当有一个if标签的条件为true时，where标签就会转化为WHERE关键字加到主SQL语句的后面，如果if没有一个成功，则忽略where和里面的if。

并且，where标签会删除多余的or或者and。

基本使用方法

1
2
3
4
<where>
   <if test="条件1">部分SQL语句</if>
   <if test="条件2">部分SQL语句</if>
</where>

示例

PersonDao.xml

1
2
3
4
5
6
7
8
9
10
11
<select id="queryPersonByWhere" resultType="com.tothefor.Study1.entity.Person">
    select * from Person
    <where>
        <if test="name!=null and name!='' ">
            or name =#{name}
        </if>
        <if test="id>0">
            or id &gt; #{id}
        </if>
    </where>
</select>

在测试时，直接使用大于符合也可以。

测试类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Person person = new Person();
person.setId(100);
person.setName(null);
Person result = dao.queryPersonByWhere(person);

//   最后实际的SQL语句 
Preparing: select * from Person WHERE id > ? 
    
    
Person person = new Person();
person.setId(100);
person.setName("lee");
Person result = dao.queryPersonByWhere(person);

//   最后实际的SQL语句 
Preparing: select * from Person WHERE name =? or id > ?

foreach

可以循环数组、list集合，一般使用在in语句中。例如SQL语句：

1
select * from Person where id in (1001,1002,1003)

基本语法：

1
2
3
<foreach collection="集合类型" open="开始的字符" close="结束的字符" item="集合中的成员" separator="集合中成员之间的分隔符">
    #{item的值}
</foreach>
  • collection：表示循环的对象是数组还是集合。是数组，则collection=”array”，是List，则collection=”list”。
  • open：例如上面的SQL语句中的前括号符号（”(“）。
  • close：例如上面的SQL语句中的后括号符号（”)”）。
  • item：相当于for循环中定义的一个变量i进行遍历。在上面SQL语句中是1001、1002、1003。
  • separator：相当于上面SQL语句中的逗号（”，”）。

简单类型的List

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
<!-- ======= PersonDao.java 接口中的方法 ======= -->
public Person queryPersonByForeach(List<Integer> idlist);

<!-- ======= PersonDao.xml mapper文件中 =======  -->
    <select id="queryPersonByForeach" resultType="com.tothefor.Study1.entity.Person">
        select * from Person where id in

        <foreach collection="list" open="(" close=")" item="PersonId" separator=",">
            #{PersonId}
        </foreach>

    </select>


<!-- ====== 测试 =======  -->
    List<Integer> idlist = new ArrayList<>();
    idlist.add(1001);
    idlist.add(1002);
    idlist.add(1003);

    Person result = dao.queryPersonByForeach(idlist);
<!-- ====== 最后实际的SQL语句 =======  -->
Preparing: select * from Person where id in ( ? , ? , ? )


如果List是为空，则会出现SQL语法错误。这时就需要用上if进行判断，如：

1
2
3
4
5
6
<if test="list!=null and list.size>0">
    where id in
  <foreach collection="list" open="(" close=")" item="PersonId" separator=",">
            #{PersonId}
        </foreach>
</if>

对象类型的List

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
<!-- ======= PersonDao.java 接口中的方法 ======= -->
public Person queryPersonByForeach2(List<Person> Plist);

<!-- ======= PersonDao.xml mapper文件中 =======  -->
    <select id="queryPersonByForeach2" resultType="com.tothefor.Study1.entity.Person">
        select * from Person where id in

        <foreach collection="list" open="(" close=")" item="Person" separator=",">
            #{Person.id}
        </foreach>

    </select>

<!-- ====== 测试 =======  -->
    List<Person> idlist = new ArrayList<>();
    idlist.add(new Person(1001,"lee"));
    idlist.add(new Person(1002,"lee2"));
    idlist.add(new Person(1003,"lee3"));

    Person result = dao.queryPersonByForeach2(idlist);
    
<!-- ====== 最后实际的SQL语句 =======  -->
Preparing: select * from Person where id in ( ? , ? , ? )


代码片段（SQL标签）

sql标签用于定义SQL片段，以便其他SQL标签复用。使用该SQL片段时，需要使用<include>子标签。sql标签可以定义SQL语句中的任何部分，<include>子标签可以放在动态SQL的任何位置。

基本用法

1
2
3
4
<sql id="唯一标识符">
  SQL语句、表名、字段、where条件等。
</sql>
<include refid="sql标签的id"></include>

示例

在上面所有的示例中，我们在写SQL语句时，都使用了select * from Person 这部分。现在我们可以定义成一个sql标签。

1
2
3
<sql id="allS">
    select * from Person
</sql>

然后其他地方可以写为：

1
2
3
4
5
6
7
8
9
<select id="queryPersonByForeach" resultType="com.tothefor.Study1.entity.Person">
    <include refid="allS" />
    where id in

    <foreach collection="list" open="(" close=")" item="PersonId" separator=",">
        #{PersonId}
    </foreach>

</select>

sql标签定义的位置没有关系，可以定义在使用前，也可以定义在使用后。至少测试时是成功的。