SSM框架开发web项目系列（四） MyBatis之快速掌握动态SQL
前言
通过前面的MyBatis部分学习，已经可以使用MyBatis独立构建一个数据库程序，基本的增删查改/关联查询等等都可以实现了。简单的单表操作和关联查询在实际开的业务流程中一定会有，但是可能只会占一部分，很多业务需求往往夹杂着一些需要我们在后台去判断的参数，举个例子，我们基本都上过购物网站，想要查看心仪的商品列表，可以通过商品分类筛选，也可以通过商品价格来筛选，还可以同时根据分类和价格来筛选，这里我们可以简单的理解为通过商品分类和商品价格的筛选分别为select语句中where后面的2个子句，类似category=XXX和price > xxx and price <xxx,具体怎么筛选要看用户怎么操作。如果按照之前的路子，我们要分别定义三个select方法和sql语句，这个就涉及到了一个静态动态的问题，用户的操作、输入等等都是不确定的，即动态的，但是我们之前在sql映射文件中写的语句都是针对单个操作单个想法去写死的，即静态的。这样一来，随着需求和判断的的不断叠加，这个代码量会很可怕。另外一个问题，如果大家有使用Java代码拼接过复杂SQL语句经历，应该不会感到很方便，本人使用hibernate的时候也拼接过HQL，共同点就是那些分隔符、空格之类的写起来很麻烦，也容易出错。MyBatis提供了动态SQL这一特性，能同时改良上述两种开发场景。
总览
MyBatis提供的动态SQL元素实际就是通过在我们的sql语句中嵌入标签实现动态，具体标签如下图所示。
熟悉jsp、jstl、el表达式那套的，应该对里面大部分标签名都不陌生，也比较容易理解，具体用法下面分别进行解析。为了结合具体的使用场景，将上面元素细分为四组来演示，分别为【if、where、trim】、【if、set、trim】、【choose、when、otherwise】、【foreach】
背景
private Integer id; //主键 private String name; //姓名 private String gender; //性别 private Integer age; //年龄 private String ifIT; //是否从事IT行业
if、where、trim篇
1.查询语句中的if
以上为我们定义的一个人的属性，数据库中也有一个人的数据表。现在假设需要查询人中的所有男性，同时如果输入参数中年龄不为空，就根据性别和年龄查询。在没有使用动态SQL之前，按照我们的惯有思路，我们需要在Mapper接口中定义两个查询方法，同时分别对应在SQL映射文件中定义两个<select>语句，如下：
<select id="selectPerson1" parameterType="psn" resultMap="personResultMap"> select * from person where GENDER = '男' </select> <select id="selectPerson2" parameterType="psn" resultMap="personResultMap"> select * from person where GENDER = '男' and AGE = #{age} </select>
这样一来，随着类似的需要越来越多，我们的方法和SQL语句量会增加到很多，并且会发现，其实语句中存在很多重复部分。那么有没有办法能同时应对类似的相关需求，同时减少代码量呢？动态SQL就提供了相关的功能实现这些需求，例如上述场景，我们即可只需定义一个方法，对应的SQL语句写成如下：
<select id="selectPerson" parameterType="psn" resultMap="personResultMap"> select * from person where GENDER = '男' <if test="age != null"> and AGE = #{age} </if> </select>
在这项<select>我们将确定的（静态的的部分）select * from person where GENDER = '男'和后面的<if>部分结合起来，通过动态SQL提供的<if>标签给语句预加一层判断，test属性值为布尔类型，true或者false，当为true（即真）时，才会把<if>标签下的内容添加到语句中响应为值，这里的test中即判断输入参数中年龄是否为空，不为空则添加【and AGE = #{age}】到【select * from person where GENDER = '男'】后面，为空则不加，这样就达到了同时满足两种需要，但只定义了一个方法和一条SQL。
2.查询语句中if的where改进
进一步扩展如果想把where后面的部分都动态化，这里以性别为例，查询时如果参数中有不为空的性别值，则根据性别查询，反之则查询所有，有了前面if的学习，我们不难写出如下动态SQL：
<select id="selectPerson1" parameterType="psn" resultMap="personResultMap"> select * from person where <if test="gender != null"> gender = #{gender} </if> </select>
这时候问题来了，当性别不为空时，语句是 select * from person where gender = #{gender} ,这样还能正常查询出我们想要的结果，但是如果性别为空，会发现语句变成了 select * from person where ，这显然是生成一个错误的SQL了，为了解决类似的问题，动态SQL<where>能帮我们解决这个问题，我们可以将上述语句优化成如下：
<select id="selectPerson1" parameterType="psn" resultMap="personResultMap"> select * from person <where> <if test="gender != null"> gender = #{gender} </if> </where> </select>
这样mybatis在这里会根据<where>标签中是否有内容来确定要不要加上where，在这里使用<where>后，如果年龄为空，则前面引发错误的where也不会出现了。
3.针对where的trim同等转换
进一步扩展，如果我们查询有多个参数需要判断，根据性别和年龄参数，有了前面<if>和<where>的了解，我们就可以写出如下SQL：
<select id="selectPerson2" parameterType="psn" resultMap="personResultMap"> select * from person <where> <if test="gender != null"> gender = #{gender} </if> <if test="age != null"> and age = #{age} </if> <where> </select>
乍一看基本没什么毛病了，在这里【性别空、年龄空】、【性别不空、年龄不空】、【性别不空、年龄空】都没问题，但是如果是【性别空、年龄不为空】，按理来说语句变成这样 select * from person where and age = #{age} ，然后如果你动手尝试一下，就会发现，并不会，这也体现了<where>一个强大之处，它不仅会根据元素中内容是否为空决定要不要添加where，还会自动过滤掉内容头部的and或者or，另外空格之类的问题也会智能处理。
MyBatis还提供了一种更灵活的<trim>标签，在这里可以替代<where>,如上面的定义可以修改成如下，同样可以实现效果：
<select id="selectPerson2" parameterType="psn" resultMap="personResultMap"> select * from person <trim prefix="where" prefixOverrides="and |or " > <if test="gender != null"> and gender = #{gender} </if> <if test="age != null"> and age = #{age} </if> </trim> </select>
<trim>标签共有四个属性，分别为【prefix】、【prefixOverrides】、【suffix】、【suffixOverrides】，prefix代表会给<trim>标签中内容加上的前缀，当然前提是内容不为空，prefixOverrides代表前缀过滤，过滤的是内容的前缀，suffix和suffixOverrides则分别对应后缀。例如上面的语句中如果性别和年龄都不为空，<trim>会在添加where前缀的同时，把第一个<if>中的and去掉，这样来实现<where>同样的功能。
if、set、trim篇
1.更新语句中的if
前面都是查询，我们换个更新试试，这里更新我们只想更新部分字段，而且要根据参数是否为空来确定是否更新，这里以姓名和性别为例。
<update id="updatePerson"> update person set <if test="name != null"> NAME = #{name}, </if> <if test="gender != null"> GENDER = #{gender} </if> </update>
2.更新语句中if的set改进
这里如果【姓别为空】或者【性别和性别都为空】，类似之前的where问题同样也来了，所以MyBatis同样也提供了<set>标签来解决这一问题，所以上面的定义可以优化成如下所示
<update id="updatePerson1"> update person <set>
<if test="name != null"> NAME = #{name}, </if> <if test="gender != null"> GENDER = #{gender}， </if> </set>
</update>
在这里，<set>会根据标签中内容的有无来确定要不要加上set，同时能自动过来内容后缀逗号，但是有一点要注意，不同于<where>,当<where>中内容为空时，我们可以查出所有人的信息，但是这里更新语句中，<set>内容为空时，语句变成 update person ，还是会出错，同时更新操作也不会生效了。
3.针对set的trim同等转换
之前介绍到的<trim>灵活之处，在这里通过修改属性值，也能用来替代<set>，上面的定义使用<trim>改写如下所示：
<update id="updatePerson2"> update person <trim prefix="set" suffixOverrides=","> <if test="name != null"> NAME = #{name}, </if> <if test="gender != null"> GENDER = #{gender} </if> </trim> </update>
这里设置前缀为set，后缀过滤为逗号“,”，这样一来，在if判断之后会引发报错的逗号将不会存在，同样可以实现相关功能。
choose、when、otherwise篇
前面我们了解到的，<select>查询语句中where后面，<if>通过判断各个参数是否为空来确定是否添加其子句内容到where后面，是一个累加的关系，但是如果我们的需求，不是累加，而是多选一，例如姓名（name）、性别（gender）、是否从事It行业（ifIT），具体来讲就是，如果优先判断姓名是否有值，有的话就根据姓名查，没有的话其次再判断性别是否有值，有的话就根据性别查，也没有的话就根据是否从事IT行业来查。这样一来，按照前面了解到的<if><where>似乎有些头大，不仅有多重判断，还涉及到一个优先级先后问题，一下子似乎很难快速想到一个简单方便的路子。MyBatis同样提供了一套<choose>、<when>、<otherwise>来帮我们解决类似上面的问题。
如果觉得不太好记忆，可以联想Java中条件判断的switch，switch对应这里的<choose>，case对应<when>,一旦某个case条件满足了会break跳出，同时如果都不满足，最后还有个default可以对应这里的<otherwise>,所以最终<when>和<otherwise>中有且只有一个会满足，也就只有一项内容会被添加进去。按照上面的需求，我们可以写出如下动态SQL：
<select id="selectPersonExt" parameterType="psn" resultMap="personResultMap"> select * from person <where> <choose> <when test="name!=null"> NAME = #{name} </when> <when test="gender!=null"> GENDER = #{gender} </when> <otherwise> IF_IT = #{ifIT} </otherwise> </choose> </where> </select>
即可方便的实现该场景。
foreach篇
for循环大家应该都不陌生，这里的foreach同样主要用来迭代集合，那么SQL中哪些地方会用到集合呢，用过in的应该比较熟悉，例如下面select语句：
select * from person where age in(10,20,30,40)
上面语句可以查询出年龄为10或20或30或40的人，这些年龄数据是一个集合，但是通过参数传入的集合是动态的，我们不可能预知数值和像这样写死，MyBatis提供的<foreach>即可实现该功能。该集合作为参数传入，以上场景方法定义和SQL语句可以写成如下所示：
List<Person> selectForeachAge(List<Integer> ageList);
<select id="selectForeachAge" resultMap="personResultMap"> select * from person where age in <foreach collection="list" item="age" index="i" open="(" close=")" separator=","> #{age} </foreach> </select>
这里我们可以看到，<foreach>共有6个属性。
item：表示集合迭代时元素的别名，这里对应#{age}中的age
index：集合迭代时索引，用于表示集合此时迭代到的位置
open、close、separator：这三个分别代表起始、结束、分隔符，在这里，我们用过in语句查询时应该都知道，使用到集合的查询语句结构关键部分可以描述如下所示： SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...) 。可以看到在IN关键字的后面，集合内容两边分别是左括号、右括号，中间集合的各个元素之间用逗号隔开，正好与这里的三个属性分别对应。
collection：这个属性是必要的，在这里我们传入的是单个参数即一个List，所以属性值就是list。
完整示例
上面就是动态SQL的各个元素的基本内容，熟悉之后会让我们编写SQL时更加方便和灵活，下面给出完整代码示例供参考。
maven工程结构如下图
MyBatis配置文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 这里可以定义类的别名，在mapper.xml文件中应用会方便很多 --> <typeAliases> <typeAlias alias="psn" type="com.mmm.pojo.Person" /> </typeAliases> <!-- 环境配置 --> <environments default="envir"> <environment id="envir"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://192.168.0.100:3306/ssm?characterEncoding=utf-8"/> <property name="username" value="root"/> <property name="password" value="abc123"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/mmm/mapper/personMapper.xml"/> </mappers> </configuration>
实体类（Person）
package com.mmm.pojo; public class Person { private Integer id; //主键 private String name; //姓名 private String gender; //性别 private Integer age; //年龄 private String ifIT; //是否从事IT行业 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getIfIT() { return ifIT; } public void setIfIT(String ifIT) { this.ifIT = ifIT; } @Override public String toString() { return "Person [id=" + id + ", name=" + name + ", gender=" + gender + ", age=" + age + ", ifIT=" + ifIT + "]"; } }
Mapper接口（PersonMapper）
package com.mmm.mapper; import java.util.List; import com.mmm.pojo.Person; public interface PersonMapper { //查找所有Person对象，返回集合类型,用于在测试类中查看动态SQL结果 List<Person> selectAll(); //用于测试查询语句中if、where、trim List<Person> selectPerson(Person p); List<Person> selectPerson1(Person p); List<Person> selectPerson2(Person p); //用于测试更新语句中if、set、trim void updatePerson(Person p); void updatePerson1(Person p); void updatePerson2(Person p); //用于测试choose、where、otherwise List<Person> selectPersonExt(Person p); //用于测试foreach List<Person> selectForeachAge(List<Integer> ageList); }
SQL映射文件（personMapper.xml）
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mmm.mapper.PersonMapper"> <resultMap type="psn" id="personResultMap"> <id column="ID" property="id" /> <result column="NAME" property="name" /> <result column="GENDER" property="gender" /> <result column="AGE" property="age" /> <result column="IF_IT" property="ifIT" /> </resultMap> <select id="selectAll" resultMap="personResultMap"> select * from person </select> <!-- 针对查询语句中if --> <select id="selectPerson" parameterType="psn" resultMap="personResultMap"> select * from person where GENDER = '男' <if test="age != null"> and AGE = #{age} </if> </select> <!-- 针对where --> <select id="selectPerson1" parameterType="psn" resultMap="personResultMap"> select * from person <where> <if test="gender != null"> gender = #{gender} </if> </where> </select> <!-- 针对where的 trim转换 --> <select id="selectPerson2" parameterType="psn" resultMap="personResultMap"> select * from person <trim prefix="where" prefixOverrides="and |or " > <if test="gender != null"> and gender = #{gender} </if> <if test="age != null"> and age = #{age} </if> </trim> <trim prefix="" prefixOverrides="" suffix="" suffixOverrides=""></trim> </select> <!-- 针对更新语句中if --> <update id="updatePerson"> update person set <if test="name != null"> NAME = #{name}, </if> <if test="gender != null"> GENDER = #{gender} </if> where ID = #{id} </update> <!-- 针对set --> <update id="updatePerson1"> update person <set> <if test="name != null"> NAME = #{name}, </if> <if test="gender != null"> GENDER = #{gender} </if> </set> </update> <!-- 针对set的trim转换 --> <update id="updatePerson2"> update person <trim prefix="set" suffixOverrides=","> <if test="name != null"> NAME = #{name}, </if> <if test="gender != null"> GENDER = #{gender} </if> </trim> </update> <!-- choose when otherwise --> <select id="selectPersonExt" parameterType="psn" resultMap="personResultMap"> select * from person <where> <choose> <when test="name!=null"> NAME = #{name} </when> <when test="gender!=null"> GENDER = #{gender} </when> <otherwise> IF_IT = #{ifIT} </otherwise> </choose> </where> </select> <!-- foreach --> <select id="selectForeachAge" resultMap="personResultMap"> select * from person where age in <foreach collection="list" item="age" index="i" open="(" close=")" separator=","> #{age} </foreach> </select> </mapper>
最后测试
package com.mmm.test; import java.io.IOException; import java.io.Reader; import java.util.ArrayList; import java.util.List; 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 org.junit.Test; import com.mmm.mapper.PersonMapper; import com.mmm.pojo.Person; public class TestDB { static PersonMapper mapper; static { //直接实例SqlSessionFactoryBuilder对象 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //MyBatis配置文件路径 String path = "mybatis-config.xml"; //通过路径获取输入流 Reader reader = null; try { reader = Resources.getResourceAsReader(path); } catch (IOException e) { e.printStackTrace(); } //通过reader构建sessionFactory SqlSessionFactory sessionFactory = builder.build(reader); //获取SqlSession对象 SqlSession sqlSession = sessionFactory.openSession(); //获取Mapper实例 mapper = sqlSession.getMapper(PersonMapper.class); } @Test public void testSelect() throws Exception { Person p = new Person(); //p.setAge(11); //p.setGender("男"); List<Person> list = mapper.selectPerson2(p); for(Person psn:list) { System.out.println(psn); } } @Test public void testUpdate() throws Exception { Person p = new Person(); p.setId(10001); //p.setName("小改2"); //p.setGender("男"); mapper.updatePerson2(p); List<Person> list = mapper.selectAll(); for(Person psn:list) { System.out.println(psn); } } @Test public void testSelectExt() throws Exception { Person p1 = new Person(); //p1.setName("小红"); //p.setGender("男"); p1.setIfIT("是"); List<Person> list = mapper.selectPersonExt(p1); for(Person psn:list) { System.out.println(psn); } } @Test public void testSelectForeachAge() throws Exception { List<Integer> ageList = new ArrayList<Integer>(); ageList.add(21); ageList.add(25); ageList.add(36); List<Person> list = mapper.selectForeachAge(ageList); for(Person psn:list) { System.out.println(psn); } } }
小结
以上即为MyBatis动态SQL的内容，在测试类中可以各种尝试改变各种输入值，来查看效果，文中虽然每个元素都涉及到了，但是有些地方还存在不足，并未过多深入扩展，例如最后的foreach，我们的参数不一定是单个，而且也不一定是集合，这些情况我们都该怎么处理，按自己的需要再去深入学习和了解，往往很快会有深刻印象。一个问题与方法的先后问题，当遇到问题后，顺着问题去找方法之后，往往很好记住。反之，没有问题和应用场景，单纯的学习方法和理论效果应该会逊色一些。最后首要还是把这些基础的东西搞懂，再去慢慢延伸。
