ibatis 工作常用标签笔记

SQLMAP  查询常用标签使用

 

<!-- 取数字结果-->

<select id="searchfundbreakawayCount" parameterClass="map" resultClass="int">
  SELECT COUNT(*) FROM tbl_orgbreakaway_counts WHERE  Fund_ID = #fundId#
 </select>

 

<!-- 取LIST查询结果-->

<!-- id:查询ID（全局唯一）  resultClass:非指定，以常用固定类反回  parameterClass：条件参数 -->

<select id="getListFinancingintelligenceEventSearch" resultClass="java.util.HashMap" parameterClass="map">
  

SELECT
    a.Financingintelligence_ID,
    a.CN_Name

FROM
    tbl_financingintelligenceevent a, tbl_enterprise b
WHERE
    a.EnterpriseId = b.Enterprise_ID

    AND a.Financingintelligence_Status=#status#

    AND b.EnterpriseStage_Status=#status#

   

    <!-- 单数组循环 -->
    <isNotNull prepend="AND" property="capitalreList">
       <iterate property="capitalreList" open="(" close=")" conjunction="OR">
           b.Enterprise_city = #capitalreList[]#
       </iterate>
    </isNotNull>

    <!-- 循环 + 比较判断-->

    <isNotNull property="industryList">
       <iterate prepend="AND" property="industryList" open="(" close=")" conjunction="OR">
         <isEqual property="industryList[].industryType" compareValue="1">
            (a.IndustryO = #industryList[].industryO#)
         </isEqual>
         <isEqual property="industryList[].industryType" compareValue="2">
            (a.IndustryO = #industryList[].industryO# AND a.IndustryT = #industryList[].industryT#)
         </isEqual>
         <isEqual property="industryList[].industryType" compareValue="3">
            (a.IndustryO = #industryList[].industryO# AND a.IndustryT = #industryList[].industryT# AND a.IndustryTH = #industryList[].industryTH#)
         </isEqual>
       </iterate>
    </isNotNull>

    

     <!-- 循环 + LIKE -->

     <isNotNull prepend="and" property="industryList">
         <iterate property="industryList" open="(" close=")" conjunction="OR">
            a.Industry_id LIKE '%$industryList[]$%'
         </iterate>
    </isNotNull>

     <!-- 大于 小于 符号的转意使用-->
     <isNotNull property="moneys">
       <iterate prepend="AND" property="moneys" open="(" close=")" conjunction="OR">
         (
          <isEqual property="moneys[].type" compareValue="0">
               a.MinMoneyUs <![CDATA[ < ]]> #moneys[].money#
          </isEqual>
          <isEqual property="moneys[].type" compareValue="1"> 
               a.MinMoneyUs <![CDATA[ >= ]]> #moneys[].money# AND a.MinMoneyUs <![CDATA[ <= ]]> #moneys[].money2#
          </isEqual>
          <isEqual property="moneys[].type" compareValue="2">
               a.MinMoneyUs <![CDATA[ > ]]> #moneys[].money#
          </isEqual>
         )
      </iterate>
    </isNotNull>
   

    <isNotNull prepend="AND" property="authority">
          a.Authority = #authority#
    </isNotNull>

  <isEqual property="orderStr" compareValue="0">
   ORDER BY CONVERT(a.CN_Name USING gbk) 
  </isEqual>
  

  <isEqual property="orderStr" compareValue="1">
   ORDER BY CONVERT(a.CN_Name USING gbk)  DESC
  </isEqual>
  

  <isEqual property="orderStr" compareValue="2">
   ORDER BY a.Create_Time 
  </isEqual>
   

  LIMIT #pager.startRow#,#pager.pageSize# 

</select>