查询 IBATIS SQL

 

sql-map-config.xml

<sqlMap resource="com/huawei/support/poapply/orm/poApply.xml" />

 <!-- 查询所有 -->
 <select id="getAllSecondCheckerList" parameterClass="java.util.HashMap"
  resultClass="java.util.HashMap" remapResults="true">
  <![CDATA[
  select bb.* from
  (
  select aa.*, rownum rr from
  (
   select
    s.code,
    s.product_line,
    s.product_family,
    s.product,
          s.second_handler,
          d1.item_name as product_line_name,
         d2.item_name as product_family_name,
          d3.item_name as product_name
        from SUP_PO_APPLY_SECOND_HANDLER s

        inner join sup_dict_item d1 on d1.item_id = s.product_line and d1.nls_lang=#lang#
        inner join  sup_dict_item d2 on d2.item_id = s.product_family and d2.nls_lang=#lang#
        inner join  sup_dict_item d3 on d3.item_id = s.product and d3.nls_lang=#lang#
        where 1=1
  ]]>
  <dynamic>
   <isNotEmpty property="productline" prepend="AND">
    s.product_line
    = #productline#
   </isNotEmpty>
   <isNotEmpty property="productfamily" prepend="AND">
    s.product_family = #productfamily#
   </isNotEmpty>
   <isNotEmpty property="product" prepend="AND">
    s.product =
    #product#
   </isNotEmpty>
   <isNotEmpty property="checker" prepend="AND">
    Upper(s.second_handler) LIKE Upper('%'||#checker#||'%')
   </isNotEmpty>
     <![CDATA[
            order by s.product_line,s.product_family,s.product desc
  ) aa
  ) bb  where bb.rr >= #ROWNUM_LOW# and bb.rr <= #ROWNUM_HIGH#
         ]]>
  </dynamic>
 </select>