动态 SQL 是 MyBatis 的强大特性之一。
如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis 显著地提升了这一特性的易用性。
$ {}通过${}可以将parameterType传入的内容拼接在sql中,不能防止sql注入,但是有时方便
例:
SELECT * FROM USER WHERE username LIKE '%${name}%'
再比如order by排序,如果将列名通过参数传入sql,根据传的列名进行排序,应该写为:
ORDER BY ${columnName}
如果使用#{}将无法实现此功能。
Java 类
public class UserVo { private Administration adm; //自定义用户扩展类 private UserCustom userCustom; }
xml文件
<select id="findUserList" parameterType="userVo" resultType="UserCustom"> SELECT * FROM adm where adm.sex= #{userCustom.sex} and adm.username LIKE '%${userCustom.username}%' </select>
mybatis 的动态sql语句是基于OGNL表达式的。可以方便的在 sql 语句中实现某些逻辑.
总体说来mybatis 动态SQL 语句主要有以下几类:
<select id="getUnitListByUserId" resultType="com.xjrsoft.module.customer.stock.inv_storer.vo.InvStorerVo" parameterType="string"> SELECT ins.USER_ID as user_id, ins.OPERATING_UNIT_ID as operating_unit_id, mou.`NAME` as operating_unit_name FROM `inv_storer` ins left join md_operating_unit mou on ins.OPERATING_UNIT_ID = mou.F_Id where ins.F_DeleteMark = 0 and ins.F_EnabledMark = 1 <if test="userId != null and userId != ''"> and ins.USER_ID = #{userId} </if> </select>
choose (when,otherwize) ,相当于java 语言中的 switch ,与 jstl 中的choose 很类似
<select id="dynamicChooseTest" parameterType="Blog" resultType="Blog"> select * from t_blog where 1 = 1 <choose> <when test="title != null"> and title = #{title} </when> <when test="content != null"> and content = #{content} </when> <otherwise> and owner = "owner1" </otherwise> </choose> </select>
详解
小结
所以上述语句的意思非常简单,当title!=null的时候就输出and titlte = #{title},不再往下判断条件,当title为空且content!=null的时候就输出and content = #{content},当所有条件都不满足的时候就输出otherwise中的内容。
trim (对包含的内容加上 prefix,或者 suffix 等,前缀,后缀)
<select id="dynamicTrimTest" parameterType="Blog" resultType="Blog"> select * from t_blog <trim prefix="where" prefixOverrides="and |or"> <if test="title != null"> title = #{title} </if> <if test="content != null"> and content = #{content} </if> <if test="owner != null"> or owner = #{owner} </if> </trim> </select>
详解
正因为trim有这样的功能,所以我们也可以非常简单的利用trim来代替where元素的功能。
小结
trim标记是一个格式化的标记,可以完成set或者是where标记的功能,如下代码:
select * from user <trim prefix="WHERE" prefixoverride="AND |OR"> <if test="name != null and name.length()>0"> AND name=#{name} </if> <if test="gender != null and gender.length()>0"> AND gender=#{gender} </if> </trim>
假如说name和gender的值都不为null的话打印的SQL为:
select * from user where name = 'xx' and gender = 'xx'
在红色标记的地方是不存在第一个and的,上面两个属性的意思如下:
update user <trim prefix="set" suffixoverride="," suffix=" where id = #{id} "> <if test="name != null and name.length()>0"> name=#{name} , </if> <if test="gender != null and gender.length()>0"> gender=#{gender} , </if> </trim>
假如说name和gender的值都不为null的话打印的SQL为:
update user set name='xx' , gender='xx' where id='x'
在红色标记的地方不存在逗号,而且自动加了一个set前缀和where后缀,上面三个属性的意义如下,其中prefix意义如上:
suffixoverride:去掉最后一个逗号(也可以是其他的标记,就像是上面前缀中的and一样)
suffix:后缀
<select id="dynamicWhereTest" parameterType="Blog" resultType="Blog"> select * from t_blog <where> <if test="title != null"> title = #{title} </if> <if test="content != null"> and content = #{content} </if> <if test="owner != null"> and owner = #{owner} </if> </where> </select>
详解:
此外,在where元素中你不需要考虑空格的问题,MyBatis会智能的帮你加上。
小结
像上述例子中,如果title=null, 而content != null,那么输出的整个语句会是:
select * from t_blog where content = #{content}
而不是select * from t_blog where and content = #{content},因为MyBatis会智能的把首个and 或 or 给忽略。
foreach (在实现 mybatis in 语句查询时特别有用):
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item,index,collection,open,separator,close。
foreach元素属性
在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:
1、单参数List的类型
<select id="getStoreroomListByIds" parameterType="string" resultType="com.xjrsoft.module.customer.inv_shift_head.invShiftHead.dto.InvStoreroomDto"> SELECT info.* FROM ( SELECT isr.F_Id AS storeroom_id, isr.OPERATING_UNIT_ID as unit_id, mou.NAME as unit_name FROM `inv_storeroom` isr LEFT JOIN `md_operating_unit` AS mou ON isr.OPERATING_UNIT_ID = mou.F_Id WHERE isr.F_DeleteMark = 0 AND isr.F_EnabledMark = 1 AND mou.F_DeleteMark = 0 AND mou.F_EnabledMark = 1 ) AS info <where> <if test="ids != null and ids != ''"> info.storeroom_id in <foreach collection="ids" item="item" close=")" open="(" separator=","> #{item} </foreach> </if> </where> </select>
上述collection的值为list,对应的Mapper是这样的:
/** * 根据库房id获取经营单元 * * @param ids * @return */ List<InvStoreroomDto> getStoreroomListByIds(@Param("ids") List<String> ids);
2、数组类型的参数
<select id="dynamicForeach2Test" resultType="com.mybatis.entity.User"> select * from t_user where id in <foreach collection="array" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>
对应mapper:
public List<User> dynamicForeach2Test(int[] ids);
3、Map类型的参数
<select id="dynamicForeach3Test" resultType="com.mybatis.entity.User"> select * from t_user where username like '%${username}%' and id in <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>
mapper 应该是这样的接口:
/**mybatis Foreach测试 */ public List<User> dynamicForeach3Test(Map<String, Object> params);