mybatis动态sql——（六）
0 什么是动态sql
mybatis核心 对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接、组装。
通过mybatis提供的各种标签方法实现动态拼接sql。
-------------------------------if,where--------------------------------------
1.问题描述
用户信息综合查询列表和用户信息查询列表总数这两个statement的定义使用动态sql。
对查询条件进行判断，如果输入参数不为空才进行查询条件拼接。
2. mapper.xml
3. 测试代码
if也可以多个条件进行组合
fstarttime,fendtime 是传递的map参数的键名 (有时候==后面的常量不用加引号)
<if test="fstarttime != null or fendtime != null and isManage=='1'"> totalMinus desc </if> <if test="fstarttime == null and fendtime == null"> sort DESC </if>
引号问题
//外面是双引号，里面就是单引号 <if test="contactPhone != null and contactPhone!= '' "> contact_phone = #{contactPhone} </if> //外面是单引号，里面就是双引号 <if test='contactPhone != null and contactPhone != "" '> contact_phone = #{contactPhone} </if> //判断字符串等于某个值是需要使用外面单引号，里面双引号 <if test='contactPhone == "abc" '> contact_phone = #{contactPhone} </if>
-----------------------------------------sql片段-------------------------------------------------
1.需求
将上边实现的动态sql判断代码块抽取出来，组成一个sql片段。其它的statement中就可以引用sql片段。
方便程序员进行开发。
2. 定义sql片段
3. 引用sql片段
------------------------------------------foreach---------------------------------
向sql传递数组或List，mybatis使用foreach解析，如下：
1. 通过pojo传递list
1.1 需求:
传入多个id查询用户信息，用下边两个sql实现：
两种方法：
SELECT * FROM USER WHERE id=1 OR id=10 OR id=16
SELECT * FROM USER WHERE id IN(1,10,16)
1.2 方法：
在pojo中定义list属性ids存储多个用户id，并添加getter/setter方法
1.3 mapper.xml
修改上面的sql片段
<!-- 定义sql片段 id：sql片段的唯 一标识 经验：是基于单表来定义sql片段，这样话这个sql片段可重用性才高 在sql片段中不要包括 where --> <sql id="query_user_where"> <if test="userCustom!=null"> <if test="userCustom.sex!=null and userCustom.sex!=''"> and user.sex = #{userCustom.sex} </if> <if test="userCustom.username!=null and userCustom.username!=''"> and user.username LIKE '%${userCustom.username}%' </if> <if test="ids!=null"> <!-- 使用 foreach遍历传入ids collection：指定输入 对象中集合属性 item：每个遍历生成对象中 open：开始遍历时拼接的串 close：结束遍历时拼接的串 separator：遍历的两个对象中需要拼接的串 --> <!-- 使用实现下边的sql拼接： AND (id=1 OR id=10 OR id=16) --> <foreach collection="ids" item="user_id" open="AND (" close=")" separator="or"> <!-- 每个遍历需要拼接的串 --> id=#{user_id} </foreach> <!-- 实现 “ and id IN(1,10,16)”拼接 --> <!-- <foreach collection="ids" item="user_id" open="and id IN(" close=")" separator=","> 每个遍历需要拼接的串 #{user_id} </foreach> --> </if> </if> </sql>
引用sql片段：
<!-- 用户信息综合查询 #{userCustom.sex}:取出pojo包装对象中性别值 ${userCustom.username}：取出pojo包装对象中用户名称 --> <select id="findUserList" parameterType="cn.itcast.mybatis.po.UserQueryVo" resultType="cn.itcast.mybatis.po.UserCustom"> SELECT * FROM USER <!-- where可以自动去掉条件中的第一个and --> <where> <!-- 引用sql片段 的id，如果refid指定的id不在本mapper文件中，需要前边加namespace --> <include refid="query_user_where"></include> <!-- 在这里还要引用其它的sql片段 --> </where> </select>
1.4 另外一个sql的实现：
2. 传递单个List
传递List类型在编写mapper.xml没有区别，唯一不同的是只有一个List参数时它的参数名为list。
2.1 Mapper.xml
<select id="selectUserByList" parameterType="java.util.List" resultType="user"> select * from user <where> <!-- 传递List，List中是pojo --> <if test="list!=null"> <foreach collection="list" item="item" open="and id in("separator=","close=")"> #{item.id} </foreach> </if> </where> </select>
2.2 Mapper接口
public List<User> selectUserByList(List userlist) throws Exception;
2.3 测试
Public void testselectUserByList()throws Exception{ //获取session SqlSession session = sqlSessionFactory.openSession(); //获限mapper接口实例 UserMapper userMapper = session.getMapper(UserMapper.class); //构造查询条件List List<User> userlist = new ArrayList<User>(); User user = new User(); user.setId(1); userlist.add(user); user = new User(); user.setId(2); userlist.add(user); //传递userlist列表查询用户列表 List<User>list = userMapper.selectUserByList(userlist); //关闭session session.close(); }
3. 传递单个数组（数组中是pojo）：
3.1 Mapper.xml
<!-- 传递数组综合查询用户信息 --> <select id="selectUserByArray" parameterType="Object[]" resultType="user"> select * from user <where> <!-- 传递数组 --> <if test="array!=null"> <foreach collection="array" index="index" item="item" open="and id in("separator=","close=")"> #{item.id} </foreach> </if> </where> </select>
sql只接收一个数组参数，这时sql解析参数的名称mybatis固定为array，如果数组是通过一个pojo传递到sql则参数的名称为pojo中的属性名。
index：为数组的下标。
item：为数组每个元素的名称，名称随意定义
open：循环开始
close：循环结束
separator：中间分隔输出
3.2 Mapper接口：
public List<User> selectUserByArray(Object[] userlist) throws Exception;
3.3 测试：
Public void testselectUserByArray()throws Exception{ //获取session SqlSession session = sqlSessionFactory.openSession(); //获限mapper接口实例 UserMapper userMapper = session.getMapper(UserMapper.class); //构造查询条件List Object[] userlist = new Object[2]; User user = new User(); user.setId(1); userlist[0]=user; user = new User(); user.setId(2); userlist[1]=user; //传递user对象查询用户列表 List<User>list = userMapper.selectUserByArray(userlist); //关闭session session.close(); }
4. 传递单个数组（数组中是字符串类型）：
4.1 Mapper.xml
<!-- 传递数组综合查询用户信息 --> <select id="selectUserByArray" parameterType="Object[]" resultType="user"> select * from user <where> <!-- 传递数组 --> <if test="array!=null"> <foreach collection="array"index="index"item="item"open="and id in("separator=","close=")"> #{item} </foreach> </if> </where> </select>
如果数组中是简单类型则写为#{item}，不用再通过ognl获取对象属性值了。
4.2 Mapper接口：
public List<User> selectUserByArray(Object[] userlist) throws Exception;
4.3 测试：
Public void testselectUserByArray()throws Exception{ //获取session SqlSession session = sqlSessionFactory.openSession(); //获限mapper接口实例 UserMapper userMapper = session.getMapper(UserMapper.class); //构造查询条件List Object[] userlist = new Object[2]; userlist[0]=”1”; userlist[1]=”2”; //传递user对象查询用户列表 List<User>list = userMapper.selectUserByArray(userlist); //关闭session session.close(); }
附一个比较复查的动态SQL:根据不同的查询条件进行查询与排序
<select id="getDepartmentInfoByCondition" resultType="map" parameterType="map"> SELECT depart.departmentid, depart.updepartmentId, depart.departmentname, depart.departmenttype, depart.departprojectnames, (SELECT departmentName FROM department WHERE departmentId = depart.upDepartmentId) AS upDepartName, depart.employeeName, depart.sort, depart.phone, (SELECT COUNT(employeeId) FROM employee_in WHERE departmentId = depart.departmentId) AS perNum, IFNULL((SELECT SUM(empInMinusNum) FROM emplyin_breakrules, employee_in WHERE emplyin_breakrules.empInEmployeeId = employee_in.employeeId AND employee_in.departmentId = depart.departmentId <if test="fstarttime != null"> and empInBreakTime >= #{fstarttime} </if> <if test="fendtime != null"> and empInBreakTime <= #{fendtime} </if> ),0) AS totalMinus FROM department depart <where> <include refid="query_department_where"></include> </where> ORDER BY <if test="fstarttime != null or fendtime != null"> totalMinus desc </if> <if test="fstarttime == null and fendtime == null"> sort DESC </if> <include refid="query_department_limit"></include> </select>
<!--查询部门的条件 --> <sql id="query_department_where"> <if test="updepartmentid!=null"> and updepartmentid=#{updepartmentid} </if> <if test="departmentId != null"> AND departmentId like '${departmentId}%' </if> <if test="departmentname!=null"> and departmentname like '%${departmentname}%' </if> <if test="1 == 1"> and departmentid !='01' </if> </sql> <sql id="query_department_limit"> <if test="index!=null"> LIMIT #{index},#{currentCount} </if> </sql>
总结:
动态 SQL
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。
动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
if
动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。比如:
<select id="findActiveBlogWithTitleLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> </select>
这条语句提供了一个可选的文本查找类型的功能。如果没有传入“title”，那么所有处于“ACTIVE”状态的BLOG都会返回；反之若传入了“title”，那么就会把模糊查找“title”内容的BLOG结果返回（就这个例子而言，细心的读者会发现其中的参数值是可以包含一些掩码或通配符的）。
如果想可选地通过“title”和“author”两个条件搜索该怎么办呢？首先，改变语句的名称让它更具实际意义；然后只要加入另一个条件即可。
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </select>
choose, when, otherwise
有些时候，我们不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
还是上面的例子，但是这次变为提供了“title”就按“title”查找，提供了“author”就按“author”查找，若两者都没有提供，就返回所有符合条件的BLOG（实际情况可能是由管理员按一定策略选出BLOG列表，而不是返回大量无意义的随机结果）。
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <choose> <when test="title != null"> AND title like #{title} </when> <when test="author != null and author.name != null"> AND author_name like #{author.name} </when> <otherwise> AND featured = 1 </otherwise> </choose> </select>
trim, where, set
前面几个例子已经合宜地解决了一个臭名昭著的动态 SQL 问题。现在考虑回到“if”示例，这次我们将“ACTIVE = 1”也设置成动态的条件，看看会发生什么。
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE <if test="state != null"> state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </select>
如果这些条件没有一个能匹配上将会怎样？最终这条 SQL 会变成这样：
SELECT * FROM BLOG WHERE
这会导致查询失败。如果仅仅第二个条件匹配又会怎样？这条 SQL 最终会是这样:
SELECT * FROM BLOG WHERE AND title like ‘someTitle’
这个查询也会失败。这个问题不能简单的用条件句式来解决，如果你也曾经被迫这样写过，那么你很可能从此以后都不想再这样去写了。
MyBatis 有一个简单的处理，这在90%的情况下都会有用。而在不能使用的地方，你可以自定义处理方式来令其正常工作。一处简单的修改就能得到想要的效果：
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG <where> <if test="state != null"> state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </where> </select>
where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。
如果 where 元素没有按正常套路出牌，我们还是可以通过自定义 trim 元素来定制我们想要的功能。比如，和 where 元素等价的自定义 trim 元素为：
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除，并且插入 prefix 属性中指定的内容。
类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列，而舍去其他的。比如：
<update id="updateAuthorIfNecessary"> update Author <set> <if test="username != null">username=#{username},</if> <if test="password != null">password=#{password},</if> <if test="email != null">email=#{email},</if> <if test="bio != null">bio=#{bio}</if> </set> where id=#{id} </update>
这里，set 元素会动态前置 SET 关键字，同时也会消除无关的逗号，因为用了条件语句之后很可能就会在生成的赋值语句的后面留下这些逗号。
若你对等价的自定义 trim 元素的样子感兴趣，那这就应该是它的真面目：
<trim prefix="SET" suffixOverrides=","> ... </trim>
例如:
<!-- 拼装查询积分组装条件 -->
<trim prefix="having" prefixOverrides="AND">
<if test="mixMinus!=null">
and
<include refid="selectMinus"></include>
>#{mixMinus}
</if>
<if test="maxMinus!=null">
and
<include refid="selectMinus"></include>
<#{maxMinus}
</if>
<!-- 如果选择进入黑名单扣分大于12 -->
<if test="isBlack!=null">
and
<include refid="selectMinus"></include>
>12
</if>
</trim>
注意这里我们忽略的是后缀中的值，而又一次附加了前缀中的值。
foreach
动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：
<select id="selectPostIn" resultType="domain.blog.Post"> SELECT * FROM POST P WHERE ID in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </select>
foreach 元素的功能是非常强大的，它允许你指定一个集合，声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。
注意 你可以将任何可迭代对象（如列表、集合等）和任何的字典或者数组对象传递给foreach作为集合参数。当使用可迭代对象或者数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。当使用字典（或者Map.Entry对象的集合）时，index是键，item是值。
到此我们已经完成了涉及 XML 配置文件和 XML 映射文件的讨论。下一部分将详细探讨 Java API，这样才能从已创建的映射中获取最大利益。
bind (可以防止SQL注入)
bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。比如：
<select id="selectBlogsLike" resultType="Blog"> <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" /> SELECT * FROM BLOG WHERE title LIKE #{pattern} </select>
Multi-db vendor support
一个配置了“_databaseId”变量的 databaseIdProvider 对于动态代码来说是可用的，这样就可以根据不同的数据库厂商构建特定的语句。比如下面的例子：
<insert id="insert"> <selectKey keyProperty="id" resultType="int" order="BEFORE"> <if test="_databaseId == 'oracle'"> select seq_users.nextval from dual </if> <if test="_databaseId == 'db2'"> select nextval for seq_users from sysibm.sysdummy1" </if> </selectKey> insert into users values (#{id}, #{name}) </insert>
动态 SQL 中可插拔的脚本语言
MyBatis 从 3.2 开始支持可插拔的脚本语言，因此你可以在插入一种语言的驱动（language driver）之后来写基于这种语言的动态 SQL 查询。
可以通过实现下面接口的方式来插入一种语言：
public interface LanguageDriver { ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql); SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType); SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType); }
一旦有了自定义的语言驱动，你就可以在 mybatis-config.xml 文件中将它设置为默认语言：
<typeAliases> <typeAlias type="org.sample.MyLanguageDriver" alias="myLanguage"/> </typeAliases> <settings> <setting name="defaultScriptingLanguage" value="myLanguage"/> </settings>
除了设置默认语言，你也可以针对特殊的语句指定特定语言，这可以通过如下的 lang 属性来完成：
<select id="selectBlog" lang="myLanguage"> SELECT * FROM BLOG </select>
或者在你正在使用的映射中加上注解 @Lang 来完成：
public interface Mapper { @Lang(MyLanguageDriver.class) @Select("SELECT * FROM BLOG") List<Blog> selectBlog(); }
总结:动态sql(9个)
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
-
bind
