MyBatis原理理解章三(动态地构造SQL语句)

原创
2018/05/03 19:02
阅读数 273

       MyBatis的动态SQL是基于OGNL表达式的,OGNL是Object-Graph Navigation Language的缩写,它是一种功能强大的表达式语言,通过它简单一致的表达式语法,可以存取对象的任意属性,调用对象的方法,遍历整个对象的结构图,实现字段类型转化等功能,它使用相同的表达式去存取对象的属性。帮助我们方便的在SQL语句中实现某些逻辑。

MyBatis中用于实现动态SQL的元素主要有:

  • if
  • choose(when,otherwise)
  • trim
  • where
  • set
  • foreach

示例

(1) if

模糊查询

<select id="select1"  resultType="BaseresultMap">  
  SELECT * FROM User WHERE Age = ‘18’   
  <if test="name != null">  
    AND name like #{name}  
  </if>  
</select>  

(2)choose,when,otherwize

当Job参数有传入时,就找出对应工作的人,否则就找出Job为none的人,而不是所有人

<select id="select2"  resultType="BaseresultMap">  
  SELECT * FROM User WHERE Age = ‘18’   
  
  <choose>  
    <when test="Job != null">  
      AND Job =#{Job}  
    </when>  
    <otherwise>  
      AND Job="none"  
  
    </otherwise>  
  </choose>  
</select>   


(3)foreach

[html] view plain copy
  <select id="select5" resultType="BaseresultBase">  
        select * from User where id in  
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">  
            #{item}  
        </foreach>  
    </select>  
  
public List<User> select5(List<Integer> ids);   


(4) where set trim

where,set

为什么要用where,因为单纯的写where可能会导致 where And ... 和 where .....情况的发生,Set也是一样的

当然 trim 标签是万能的

[html] view plain copy
<select id="select3"  resultType="BaseresultMap">  
  SELECT * FROM User  
<where>  
  
  <if test="Age != null">  
    Age = #{Age}  
  </if>  
  <if test="Job != null">  
    AND Job like #{Job}  
  </if>  
  
<where>  
</select>  
  
<update id="update1">  
  update User  
    <set>  
      <if test="username != null">username=#{username},</if>  
      <if test="password != null">password=#{password},</if>  
      <if test="Age != null">Age =#{Age}</if>  
    </set>  
  where id=#{id}  
</update>  
[html] view plain copy
  
[html] view plain copy
  
[html] view plain copy
  

(3)foreach

<select id="select5" resultType="BaseresultBase">  
        select * from User where id in  
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">  
            #{item}  
        </foreach>  
    </select>  
  
public List<User> select5(List<Integer> ids); 

(4) where set trim

<select id="select3"  resultType="BaseresultMap">  
  SELECT * FROM User  
<where>  
  
  <if test="Age != null">  
    Age = #{Age}  
  </if>  
  <if test="Job != null">  
    AND Job like #{Job}  
  </if>  
  
<where>  
</select>  
  
<update id="update1">  
  update User  
    <set>  
      <if test="username != null">username=#{username},</if>  
      <if test="password != null">password=#{password},</if>  
      <if test="Age != null">Age =#{Age}</if>  
    </set>  
  where id=#{id}  
</update>  

(5)foreach

<select id="dynamicForeachTest" parameterType="java.util.List" resultType="Blog">
 select * from t_blog where id in
 <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
     #{item}       
 </foreach>    
 </select>

 

展开阅读全文
打赏
0
1 收藏
分享
加载中
更多评论
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部