• 动态 SQL是MyBatis强大特性之一,极大的简化我们拼装 SQL的操作。
• 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处 理器相似。
• MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作。
– if – choose (when, otherwise) – trim (where, set) – foreach
sql片段:(实现sql复用)
如果片段不再同一个文件则要加上namespace
<select id="sqlOb" resultType="Person" parameterType="Object[]">
SELECT *FROM person
<include refid="sqlaa"></include>
</select>
<sql id="sqlaa">
<where>
<if test="array != null and array.length > 0">
<foreach collection="array" open="and id IN (" close=")" item="item" separator=",">
#{item.id}
</foreach>
</if>
</where>
</sql>
动态SQL
若student有name属性且不为null,则将其Sql语句拼入
if
where
<select id="sql" parameterType="Person" resultType="Person">
SELECT * FROM person
<where>
<!--student有name属性且不为null-->
<if test="name != null and name != ''">
and name = #{name}
</if>
<if test="age !=null">
and age = #{age}
</if>
</where>
</select>
choose (when, otherwise)
foreach迭代
foreach迭代的类型:数组,集合,属性(grade类:List<>)
• 当迭代列表、集合等可迭代对象或者数组时 – index是当前迭代的次数,item的值是本次迭代获取的元素
• 当使用字典(或者Map.Entry对象的集合)时 – index是键,item是值
<!--对象中的属性-->
<select id="sqllist" resultType="Person" parameterType="Age">
SELECT *FROM person
<where>
<if test="list != null and list.size > 0">
<foreach collection="list" open="and id IN (" close=")" item="id" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
<!--数组 约定标识符只能为array -->
<select id="sqlarray" resultType="Person" parameterType="int[]">
SELECT *FROM person
<where>
<if test="array != null and array.length > 0">
<foreach collection="array" open="and id IN (" close=")" item="id" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
<!--集合 约定标识符只能为list -->
<select id="sqlli" resultType="Person" parameterType="list">
SELECT *FROM person
<where>
<if test="list != null and list.size > 0">
<foreach collection="list" open="and id IN (" close=")" item="id" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
<!--对象数组(parameter中是Object[]) 约定标识符只能为array -->
<select id="sqlOb" resultType="Person" parameterType="Object[]">
SELECT *FROM person
<include refid="sqlaa"></include>
</select>
<sql id="sqlaa">
<where>
<if test="array != null and array.length > 0">
<foreach collection="array" open="and id IN (" close=")" item="item" separator=",">
#{item.id}
</foreach>
</if>
</where>
</sql>
TIP
<!--数组 约定标识符只能为array -->
<!--集合 约定标识符只能为list -->
<!--对象数组(parameter中是Object[]) 约定标识符只能为array -->