ssm动态sql语句

一.前言

通过前面的MyBatis部分学习，已经可以使用MyBatis独立构建一个数据库程序，基本的增删查改/关联查询等等都可以实现了。简单的单表操作和关联查询在实际开的业务流程中一定会有，但是可能只会占一部分，很多业务需求往往夹杂着一些需要我们在后台去判断的参数，举个例子，我们基本都上过购物网站，想要查看心仪的商品列表，可以通过商品分类筛选，也可以通过商品价格来筛选，还可以同时根据分类和价格来筛选，这里我们可以简单的理解为通过商品分类和商品价格的筛选分别为select语句中where后面的2个子句，类似category=XXX和price > xxx and price <xxx,具体怎么筛选要看用户怎么操作。如果按照之前的路子，我们要分别定义三个select方法和sql语句，这个就涉及到了一个静态动态的问题，用户的操作、输入等等都是不确定的，即动态的，但是我们之前在sql映射文件中写的语句都是针对单个操作单个想法去写死的，即静态的。这样一来，随着需求和判断的的不断叠加，这个代码量会很可怕。另外一个问题，如果大家有使用Java代码拼接过复杂SQL语句经历，应该不会感到很方便，本人使用hibernate的时候也拼接过HQL，共同点就是那些分隔符、空格之类的写起来很麻烦，也容易出错。MyBatis提供了动态SQL这一特性，能同时改良上述两种开发场景。

总览

熟悉jsp、jstl、el表达式那套的，应该对里面大部分标签名都不陌生，也比较容易理解，具体用法下面分别进行解析。为了结合具体的使用场景，将上面元素细分为四组来演示，分别为【if、where、trim】、【if、set、trim】、【choose、when、otherwise】、【foreach】

if、where、trim篇

1.查询语句中的if
以上为我们定义的一个人的属性，数据库中也有一个人的数据表。现在假设需要查询人中的所有男性，同时如果输入参数中年龄不为空，就根据性别和年龄查询。在没有使用动态SQL之前，按照我们的惯有思路，我们需要在Mapper接口中定义两个查询方法，同时分别对应在SQL映射文件中定义两个语句，如下：

<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 * from person where GENDER = '男’和后面的部分结合起来，通过动态SQL提供的标签给语句预加一层判断，test属性值为布尔类型，true或者false，当为true（即真）时，才会把标签下的内容添加到语句中响应为值，这里的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能帮我们解决这个问题，我们可以将上述语句优化成如下：

<select id="selectPerson1" parameterType="psn" resultMap="personResultMap">
    select * from person
    <where>
        <if test="gender != null">
            gender = #{gender}
        </if>
    </where>
</select>

这样mybatis在这里会根据标签中是否有内容来确定要不要加上where，在这里使用后，如果年龄为空，则前面引发错误的where也不会出现了

3.针对where的trim同等转换

进一步扩展，如果我们查询有多个参数需要判断，根据性别和年龄参数，有了前面和的了解，我们就可以写出如下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，还会自动过滤掉内容头部的and或者or，另外空格之类的问题也会智能处理。

MyBatis还提供了一种更灵活的标签，在这里可以替代,如上面的定义可以修改成如下，同样可以实现效果：

 <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>           

标签共有四个属性，分别为【prefix】、【prefixOverrides】、【suffix】、【suffixOverrides】，prefix代表会给标签中内容加上的前缀，当然前提是内容不为空，prefixOverrides代表前缀过滤，过滤的是内容的前缀，suffix和suffixOverrides则分别对应后缀。例如上面的语句中如果性别和年龄都不为空，会在添加where前缀的同时，把第一个中的and去掉，这样来实现同样的功能。

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同样也提供了标签来解决这一问题，所以上面的定义可以优化成如下所示

  　　<update id="updatePerson1">
    update person
    <set>
　　　　　<if test="name != null">
            NAME = #{name},
        </if>
        <if test="gender != null">
            GENDER = #{gender}，
        </if>
    </set>
</update>

在这里，会根据标签中内容的有无来确定要不要加上set，同时能自动过来内容后缀逗号，但是有一点要注意，不同于,当中内容为空时，我们可以查出所有人的信息，但是这里更新语句中，内容为空时，语句变成 update person ，还是会出错，同时更新操作也不会生效了。

3.针对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篇

前面我们了解到的，查询语句中where后面，通过判断各个参数是否为空来确定是否添加其子句内容到where后面，是一个累加的关系，但是如果我们的需求，不是累加，而是多选一，例如姓名（name）、性别（gender）、是否从事It行业（ifIT），具体来讲就是，如果优先判断姓名是否有值，有的话就根据姓名查，没有的话其次再判断性别是否有值，有的话就根据性别查，也没有的话就根据是否从事IT行业来查。这样一来，按照前面了解到的似乎有些头大，不仅有多重判断，还涉及到一个优先级先后问题，一下子似乎很难快速想到一个简单方便的路子。MyBatis同样提供了一套、、来帮我们解决类似上面的问题。

如果觉得不太好记忆，可以联想Java中条件判断的switch，switch对应这里的，case对应,一旦某个case条件满足了会break跳出，同时如果都不满足，最后还有个default可以对应这里的,所以最终和中有且只有一个会满足，也就只有一项内容会被添加进去。按照上面的需求，我们可以写出如下动态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提供的即可实现该功能。该集合作为参数传入，以上场景方法定义和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>

这里我们可以看到，共有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。