[Java][ibatis]条件SQL写法示例

[ibatis]条件SQL写法示例

 

<resultMap class="java.util.HashMap" id="statistic_result">      <result property="TOTAL" javaType="java.lang.Integer"/>      <result property="SUCCESS" javaType="java.lang.Integer"/>      <result property="TOTAL_MONEY" javaType="java.lang.Double"/>      <result property="TOTAL_SUCCESS_MONEY" javaType="java.lang.Double"/>      <result property="TOTAL_COMMISSION" javaType="java.lang.Double"/>  </resultMap>      <select id="statistic"  parameterClass="java.util.HashMap"" resultMap="statistic_result" >      <![CDATA[      select         count(*)                                     as TOTAL,         nvl(sum(decode(state,'0',1,0)),0)            as SUCCESS,         nvl(sum(money),0)                            as TOTAL_MONEY,         nvl(sum(decode(state,'0',money,0)),0)        as TOTAL_SUCCESS_MONEY,         nvl(sum(decode(state,'0',commission,0)),0)   as TOTAL_COMMISSION      FROM T_PHONE_SALE ]]>      <dynamic prepend="WHERE">          <isNotEmpty prepend=" AND " property="saleNum">              SALE_NUM =  #saleNum#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="bussinessId">              BUSSINESS_ID =  #bussinessId#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="phone">              PHONE =  #phone#            </isNotEmpty>          <isNotEmpty prepend=" AND " property="areaCode">              AREA_CODE =  #areaCode#             </isNotEmpty>          <isNotEmpty prepend=" AND " property="phoneArea">              PHONE_AREA =  #phoneArea#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="typeCode">              TYPE_CODE =  #typeCode#             </isNotEmpty>          <isNotEmpty prepend=" AND " property="money">              MONEY =  #money#            </isNotEmpty>          <isNotEmpty prepend=" AND " property="price">              PRICE =  #price#            </isNotEmpty>          <isNotEmpty prepend=" AND " property="collect">              COLLECT =  #collect#            </isNotEmpty>          <isNotEmpty prepend=" AND " property="commission">              COMMISSION =  #commission#          </isNotEmpty>                     <isNotEmpty prepend=" AND " property="beginTime">            <![CDATA[          CREATE_DATE >  to_date(#beginTime#,'yyyy-mm-dd hh24:mi:ss')  ]]></isNotEmpty>          <isNotEmpty prepend=" AND " property="endTime">          <![CDATA[          CREATE_DATE <  to_date(#endTime#,'yyyy-mm-dd hh24:mi:ss')        ]]></isNotEmpty>            <isNotEmpty prepend=" AND " property="finishDate">              FINISH_DATE = #finishDate#          </isNotEmpty>          <isNotEmpty prepend=" AND " property="employeeId">              EMPLOYEE_ID =  #employeeId#             </isNotEmpty>                         <isNotEmpty prepend=" AND " property="states">               <iterate property="states" open="(" close=")" conjunction="OR">                <isNotEqual compareValue="1" property="states[]">                  STATE=#states[]#                </isNotEqual>                <isEqual compareValue="1" property="states[]">                  STATE not in('0','-1')               </isEqual>          </iterate>               </isNotEmpty>                     <isNotEmpty prepend=" AND " property="oneSide">              ONE_SIDE =  #oneSide#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="oneEmployee">              ONE_EMPLOYEE =  #oneEmployee#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="oneRemark">              ONE_REMARK =  #oneRemark#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="reverse">              REVERSE =  #reverse#            </isNotEmpty>          <isNotEmpty prepend=" AND " property="reverseEmployee">              REVERSE_EMPLOYEE =  #reverseEmployee#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="reverseRemark">              REVERSE_REMARK =  #reverseRemark#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="supplyFailed">              SUPPLY_FAILED =  #supplyFailed#             </isNotEmpty>          <isNotEmpty prepend=" AND " property="beforeAccount">              BEFORE_ACCOUNT =  #beforeAccount#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="afterAccount">              AFTER_ACCOUNT =  #afterAccount#             </isNotEmpty>          <isNotEmpty prepend=" AND " property="beforeCommission">              BEFORE_COMMISSION =  #beforeCommission#             </isNotEmpty>          <isNotEmpty prepend=" AND " property="afterCommission">              AFTER_COMMISSION =  #afterCommission#           </isNotEmpty>          <isNotEmpty prepend=" AND " property="cost">              COST =  #cost#          </isNotEmpty>                         <isNotEmpty prepend=" AND " property="operators">              OPERATORS_CODE in               <iterate property="operators" open="(" close=")" conjunction=",">               #operators[]#               </iterate>               </isNotEmpty>                         <isNotEmpty prepend=" AND " property="clientName">              CLIENT_NAME =  #clientName#             </isNotEmpty>          <isNotEmpty prepend=" AND " property="clientMoney">              CLIENT_MONEY =  #clientMoney#           </isNotEmpty>      </dynamic>  </select>