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

最新推荐文章于 2021-07-18 17:36:20 发布

逐浪_一生悬命

最新推荐文章于 2021-07-18 17:36:20 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库专区 Java专区

本文链接：https://blog.youkuaiyun.com/szwangdf/article/details/10070111

Java专区同时被 2 个专栏收录

93 篇文章

订阅专栏

数据库专区

39 篇文章

订阅专栏

本文介绍了一个使用IBATIS实现复杂条件查询的例子，展示了如何通过动态SQL语句来构建灵活的查询条件，包括多种字段匹配及时间范围查询等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[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>