动态SQL
基于OGNL(对象图导航语言)表达式
完成多条件查询等逻辑实现
用于实现动态SQL的元素主要有
if
<?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="dao.EmpDaoMapper">
<select id="selectByNoMgr" resultType="bean.Emp">
select * from emp where 1=1
<!-- 如果test值为真,则将if标签里的字符串拼接到后面 -->
<if test="empno != null">
and empno = #{empno}
</if>
<if test="mgr != null">
and mgr = #{mgr}
</if>
</select>
</mapper>
trim
<select id="selectByNoMgr" resultType="bean.Emp">
select * from emp
<!--prefixOverrides:去除与它值相同的前缀,在这里就是去掉 and 前缀-->
<!--prefix:添加前缀-->
<trim prefix="where" prefixOverrides="and">
<if test="empno != null">
and empno = #{empno}
</if>
<if test="mgr != null">
and mgr = #{mgr}
</if>
</trim>
</select>
<update id="update">
update emp set
<!--suffixOverrides:去掉与它值相同的后缀,在这里就是去掉最后一个 ,-->
<!--suffix:添加后缀-->
<trim suffixOverrides=",">
<if test="ename != null and ename != '' ">
ename = #{ename},
</if>
<if test="job != null and job != '' ">
job = #{job},
</if>
</trim>
where empno = #{empno}
</update>
where
<select id="selectByNoMgr" resultType="bean.Emp">
select * from emp
<!--where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。-->
<!--而且,若语句的开头为“AND”或“OR”,where 元素也会将它们去除-->
<where>
<if test="empno != null">
empno = #{empno}
</if>
<if test="mgr != null">
mgr = #{mgr}
</if>
</where>
</select>
set
<update id="update">
update emp
<!--set 元素会动态前置 SET 关键字,同时也会删掉无关的逗号,-->
<!--因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。-->
<set>
<if test="ename != null and ename != '' ">
ename = #{ename},
</if>
<if test="job != null and job != '' ">
job = #{job},
</if>
</set>
where empno = #{empno}
</update>
choose(when、otherwise)
<select id="selectByNoMgr" resultType="bean.Emp">
select * from emp where 1=1
<!--与java switch相似-->
<choose>
<when test="empno != null">
and empno = #{empno}
</when>
<when test="mgr != null">
and mgr = #{mgr}
</when>
<otherwise>
and empno = 1
</otherwise>
</choose>
</select>
foreach
<select id="selectIn" resultType="bean.Emp">
select * from emp where
<!--collection:传入参数类型-->
<!--item:迭代变量-->
<!--open:前缀-->
<!--close:后缀-->
<!--separator:分隔符-->
<foreach collection="list" item="no" open="empno in (" close=")" separator=",">
#{no}
</foreach>
<!--若传入参数集合为 1,2,3,则遍历后的sql语句为:-->
<!--select * from emp where empno in (1,2,3)-->
</selet>