3.2　使用动态SQL完成多条件查询

3.2.1　使用元素if+where实现多条件查询

1.if元素

在MyBatis框架中，<if>元素是最常用的判断语句，它类似于Java中的if语句，主要用于实现某些简单的条件选择。

在实际应用中经常会通过多个条件来精确地查询某个数据。如要查找某个用户的信息，不仅可以通过姓名和职业来查找用户，也可以不填写职业直接通过姓名来查找用户，或这两个信息都不填写而查询出所有用户，此时姓名和职业就是非必须条件。类似于这种情况，在MyBatis框架中就可以通过if来实现。下面就通过一个具体的案例来演示这种情况，其具体实现步骤如下。

回顾第2章的演示示例：根据用户角色（角色id精确匹配）和用户名称（模糊匹配）完成对用户表的查询操作，在该示例中，采用的是封装User对象入参进行查询。因此，在查询条件不是很多且较为固定的情况下，最好的解决方案是采用多参数直接入参的方式，这样代码比较清晰可读性也强。

修改UserMapper.java的getUserList()方法，见示例1。

【示例1】　UserMapper.java

在上述代码中，参数使用了@Param注解，并将参数roleId重命名为userRole，所以UserMapper.xml的代码无须改造，见示例2。

【示例2】　UserMapper.xml

完成以上修改之后，运行测试类进行相应的方法测试。先测试两个条件均给出的情况，测试方法（testGetUserList()）部分见示例3。

【示例3】　UserMapperTest.java

使用JUnit4执行上述方法后，控制台的输出结果如下：

然后模拟用户没有输入所有条件的情况，如传入用户角色roleId参数为空，即只按用户名称进行模糊查询，测试方法（testGetUserList2()）见示例4。

【示例4】　UserMapperTest.java

使用JUnit4执行上述方法后，控制台的输出结果如下：

通过该运行结果发现，查询出来的用户列表为空，这个查询结果是否正确呢?

根据控制台的输出日志信息，把日志中SQL语句含有“?”的位置分别替换成相应的参数："张"、null，修改后相应的SQL语句如下：

并在MySQL数据库中执行该SQL语句，查询结果确实为空。但是根据正确的逻辑思考，当用户没有输入用户角色的情况下，只根据用户名称进行模糊查询，结果应该是所有userName中含有“张”的全部用户信息，SQL语句如下：

在MySQL数据库中执行的结果如图3.1所示。

根据业务需求，这才是正确的查询结果，而示例代码的运行结果并不正确。由于在进行多条件查询的时候，用户并不一定会完整地输入所有的查询条件，因此对于类似情况，之前示例代码的SQL语句存在漏洞，那么应该如何修改呢？

思考：对于上述示例，若查询条件中userName为null，roleId有值（如roleId=3），则查询结果同样为空，但是若查询条件中userName为“”（空字符串），roleld有值（如roleId=3），则查询结果不为空且正确。大家思考一下，这是什么原因导致的呢？

解决方案是使用动态SQL的if元素来实现多条件查询。修改UserMapper.xml，见示例5。

【示例5】　UserMapper.xml

在上述示例中改造了SQL语句，利用if元素实现简单的条件判断，if元素的test属性表示进入if元素内需要满足的条件。整个SQL语义非常简单，若提供userRole参数（满足条件userRole !=null），那么SQL的where条件就要满足：u.userRole=#{userRole}。同样，若提供了userName参数（满足条件userName!=null且userName !=&apos; &apos;），那么SQL的where条件就要满足：u.userName like CONCAT（&apos;%&apos; ，# {userName}，&apos;%&apos;），最终返回满足这些where条件的数据列表。这是一个非常有用的功能，相比于使用JDBC要达到同样的选择效果，则需要通过if、else等语句进行SQL的拼接，而MyBatis的动态SQL就要简单许多。

最后运行测试方法，观察控制台输出的SQL语句及查询结果如下：

从控制台输出可以看出，输出的SQL语句是根据if元素的条件判断重新对where子句进行拼接的，日志中的查询结果也是正确的。当然还可以测试其他多种情况，在此不再逐一进行演示。

2.where元素

根据用户名称（模糊查询）和角色id查询用户列表，用户列表不需要显示角色名称。修改示例代码，只需将UserMapper.xml中getUserList的select类型改为resultType，并修改SQL，见示例6。

【示例6】　UserMapper.xml

运行测试方法的代码片段如下：

运行结果如图3.2所示。

通过运行结果发现后台报错。具体的错误信息为SQL语句错误，即where子句后面多了一个“and”，那为何之前的示例代码没有出现这样的问题呢？这是因为之前的示例中在该SQL语句的where子句里含有一个固定条件：u.userRolei.id，且紧跟在where后面。所以当参数传入不完整时，不会因为多余的“and”导致发生SQL错误。

同样对于上述示例，若不输入任何条件，即测试方法中两个参数均传入空值，正常情况下控制台应该输出所有用户列表的信息，测试方法见示例7。

【示例7】　UserMapperTest.java

运行结果如图3.3所示。

与之前的运行结果一样，后台SQL语句又报错了，不同的是SQL语句没有where子句，却多了一个“where”，造成SQL异常错误的原因也和之前分析的一样。

综上分析，若要解决此类问题就需要智能地处理and和where这两个元素，动态SQL中的where元素即可满足需求。

where元素主要用来简化SQL语句中的where条件判断，并能智能地处理元素and和or，不必担心多余关键字导致的语法错误。下面修改UserMapper.xml，见示例8。

【示例8】　UserMapper.xml

通过上述代码，where元素标签会自动识别其标签内是否有返回值，若有就插入一个where。此外，若该标签返回的内容是以and或者or开头的，则会自动剔除。下面根据以上两种出错情况分别进行运行测试。

第1种情况：参数userName传入空字符串（或者null），参数roleId给定值，测试方法见示例9。

【示例9】　UserMapperTest.java

使用JUnit4执行上述方法后，控制台的输出结果如下：

可看出，在控制台输出的日志中，SQL语句根据传递的参数进行了正确拼接，where子句里自动剔除了and。

第2种情况：两个参数传入的值均为空，测试方法见示例10。

【示例10】　UserMapperTest.java

使用JUnit4执行上述方法后，控制台的输出结果如下：

可看出，在控制台输出的日志中，SQL语句同样根据传递的参数进行了正确拼接，由于此种情况没有参数，故智能地去掉了where。

3.2.2　技能训练1

上机练习1　使用动态SQL—if元素改造采购订单表的查询操作

需求说明

（1）修改订单表的查询功能，使用动态SQL来完善此功能。

（2）查询条件：

①商品名称（模糊查询）。

②供应商（供应商id）。

③是否付款。

（3）查询结果列表：订单id、订单编码、商品名称、供应商id、供应商名称、订单金额、是否付款、创建时间。

（1）在BillMapper.xml中修改SQL语句，使用动态SQL的if元素。

（2）修改测试方法，并进行多种情况的测试。

上机练习2　使用动态SQL—if+where改造供应商表的查询操作

需求说明

（1）改造供应商表的查询功能，使用动态SQL完善此功能。

（2）查询条件：

①供应商编码（模糊查询）。

②供应商名称（模糊查询）。

（3）查询结果列表：供应商id、供应商编码、供应商名称、联系人、联系电话、传真、创建时间。

（1）在ProviderMapper.xml中修改SQL语句，使用动态SQL的if元素和where元素。

（2）修改测试方法，并进行多种情况的测试。

3.2.3　使用元素if+trim实现多条件查询

在MyBatis框架中除了使用元素if+where实现多条件查询，还有一个更为灵活的trim元素可以替代之前的做法。

trim元素也会自动识别其标签内是否有返回值，若有返回值，则会在已包含的内容前加上某些前缀，或在其后加上某些后缀，与之对应的属性是prefix和suffix；还可以把包含内容的首部某些内容覆盖（忽略），或者把尾部的某些内容覆盖，与之对应的属性是prefixOverrides和suffixOverrides；正因为trim元素有这样强大的功能，可以利用它替代where元素，并实现与where元素相同的效果。接下来就修改UserMapper.xml，以实现多条件用户表的查询操作，见示例11。

【示例11】　UserMapper.xml

通过上述代码来了解一下trim的属性。

（1）prefix：前缀。通过自动识别是否有返回值后，在trim元素包含的内容中加前缀。如此处的where元素。

（2）suffix：后缀。在trim元素包含的内容中加后缀。

（3）prefixOverrides：对于trim元素包含内容的首部进行指定内容（如“and | or”）的忽略。

（4）suffixOverrides：对于trim元素包含内容的首尾部进行指定内容的忽略。

最后运行测试方法，根据传入的不同参数，分别进行智能的SQL语句拼接处理，效果等同于where元素，此处不再赘述。

3.2.4　<choose>元素、<when>元素、<otherwise>元素

在使用<if>元素时，只要test属性中的表达式为true，就会执行元素中的条件语句，但是在实际应用中，有时只需要从多个选项中选择一个用于执行。

如下面的场景：

①当用户名称不为空时，则只根据用户名称进行用户筛选；

②当用户名称为空，而用户权限不为空时，则只根据用户权限进行用户筛选；

③当用户名称和用户权限都为空时，则要求查询出所有电话不为空的用户信息。

此种情况下使用<if>元素进行处理是非常不合适的。如果使用的是Java语言，则更适合使用switch...case...default语句来处理。那么在MyBatis框架中有没有类似的语句呢？答案是肯定的。针对上面的情况，MyBatis框架可以使用元素<choose>、<when>、<otherwise>进行处理。

对于某些查询需求，虽有多个查询条件，但是不需要应用所有的条件，只选择其中一种情况的查询结果即可。同Java的switch语句相似，MyBatis提供choose元素来满足这种需求。

choose元素的作用相当于Java中的switch语句，基本同JSTL中choose元素的作用和用法是一样的，通常都是搭配元素when、otherwise使用。下面就通过一个示例来演示说明其用法。

根据条件（用户名称、用户角色、用户编码、创建时间）查询用户表，具体要求：查询条件提供前3个（用户名称、用户角色、用户编码）中的任意一个即可，若前3个条件都不提供，那么默认提供最后1个条件（创建时间：在指定的年份内）来完成查询操作。

修改UserMapper.java，以增加接口方法，见示例12。

【示例12】　UserMapper.java

在上述代码中，使用@Param注解实现多条件入参，然后修改UserMapper.xml，见示例13。

【示例13】　UserMapper.xml

在上述代码中，使用元素choose（when、otherwise）来实现需求。choose元素一般与元素when、otherwise配套使用。

when元素：当其test属性中的条件满足时，就会输出when元素中的内容。跟Java的switch同样是按照条件的顺序来进行处理的，并当when元素中一旦有条件满足时，就会跳出元素choose，即所有的元素when和otherwise条件中，只有一个条件会输出。

otherwise元素：当when元素中的所有条件都不满足时，就会自动输出otherwise元素中的内容。

比如上述的代码语句表述的就是当“userName !=null and userName !=”时，就输出“and userName like CONCAT (&apos;%&apos;,#{userName},&apos;%&apos;)”拼接到前面SQL语句（select * from tb_user where 1=1）的后面，然后就不再往下判断剩余条件了，SQL语句拼接完成，当第一个when标签的条件不满足时，进入第二个when标签进行条件判断，若满足“userCode!=null and userCode !=”时，就输出标签内的内容，且不再往下判断剩余条件，SQL语句拼接完成。以此类推，若所有的when条件都不满足，则进入otherwise标签，输出该标签内的“and YEAR(creationDate)=YEAR(#{creationDate})”，与需求相呼应。

那么大家可以思考：为何前面的SQL语句（select * from tb_user）要加入where 1=1呢？下面通过运行结果和输出的SQL语句加以分析。

增加测试方法，并进行相应的测试，测试方法见示例14。

【示例14】　UserMapperTest.java

通过上述代码传入的参数情况，运行测试方法，观察控制台输出的SQL语句如下：

通过日志可以看出拼接后的SQL语句，与之前分析的一样。那么在SQL语句（select * from tb_user）后面加入where 1=1的原因是，不需要再去处理多余的“and”。其他情况的测试，在此不再赘述。

3.2.5　技能训练2

上机练习3　改造供应商列表查询（choose）

需求说明

（1）实现按条件查询供应商表，查询条件如下：

①供应商编码（模糊查询）；

②供应商名称（模糊查询）；

③供应商联系人（模糊查询）；

④创建时间在本年内（时间范围）。

（2）查询结果列显示：供应商id、供应商编码、供应商名称、供应商联系人、创建时间。

查询操作中，提供查询条件的前3个（供应商编码、供应商名称、供应商联系人）中的任意一个即可，若前3个条件都为空，那么默认提供最后1个条件（创建时间）来完成查询操作。

使用动态SQL-choose（when、otherwise）实现。