like模糊查詢，和大小寫，去空

like模糊查詢，和大小寫，去空

用 like查詢最好用#這樣在輸入文字參數的時候會自動加上''就不會報錯。（欄位太多來源複雜直接用map裝，其也相當於一個對象，前段EL運算式直接類似對象訪問）

 

  <select id="getWhLogList" parameterType="map" resultType="map">

  select a.*,c.*

  from SCM_EGESYSLOG a left join opm_user b on a.operid=b.id left join TB_CUS_MARK c on b.organid=c.market_key where 1=1

  <if test="organId !=null and organId!=''">

  and c.market_key=#{organId} and c.market_key is not null

  </if>

  <if test="type !=null and type!=''">

  and type=#{type}

  </if>

   <if test="key !=null and key!=''">

  and ( (lower(trim(operDesc))  like '%'||lower(trim(#{key}))||'%') or (lower(trim(operName)) like '%'||lower(trim(#{key})))||'%' )

  </if>

  <if test="begindate !=null and begindate!=''">

  and operDate <![CDATA[>=]]> to_date(#{begindate},'yyyy-mm-dd hh24:mi:ss')

  </if>

  <if test="endDate !=null and endDate!=''">

  and operDate <![CDATA[<=]]> to_date(#{endDate},'yyyy-mm-dd hh24:mi:ss')

  </if>

   <if test="userID !=null and userID!=''">

   and lower(trim(operId))=lower(trim(#{userID})) 

  </if>

  <if test="userName !=null and userName!=''">

   and lower(trim(operName))  like '%'||lower(trim(#{userName}))||'%'

  </if>

  </select>