ibatis有关模糊查询和And与Or联查的例子

   ibatis有时候需要操纵模糊查询，其SQL的语句为 Select t.userName,t.userPwd  from tb_user  t  where t.name like '%liu%'。
当需将改SQL用在ibatis中查询的时候，可以用以下语句：

<select id="findUserByName" resultClass="com.example.User" resultSetType="SCROLL_INSENSITIVE">
  select user_Name,user_Pwd
  from tb_user 
  where 1=1 
  <isNotNull property="userName" prepend="AND">
    user_Name like '%'||#userName#||'%'
  </isNotNull>
 </select>

 

当需要查询查询语句中包含and 与or的表时，可以用以下语句:  
  <select id="findUserNum" resultClass="java.lang.Long" resultSetType="SCROLL_INSENSITIVE">
        select count(*)
        from tb_user u
        where U.STATE = 'ENABLED'
        <dynamic prepend="and" open="(" close=")">
                      <isNotNull prepend="or" property="searchCondition">
                         u.user_name like '%'||#searchCondition#||'%'
                      </isNotNull>
                      <isNotNull prepend="or" property="searchCondition">
                         u.msisdn like '%'||#searchCondition#||'%'
                      </isNotNull>                    
        </dynamic>
   </select>