mybatis sql语句包含list

MyBatis导出交易明细

最新推荐文章于 2021-11-14 23:14:36 发布

原创最新推荐文章于 2021-11-14 23:14:36 发布 · 592 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个MyBatis的SQL映射文件，用于导出特定条件下的交易明细数据，包括订单信息、客户信息、支付状态等。通过动态SQL实现了灵活的查询条件设置。

     <isNotEmpty prepend="AND" property="orgids">
                JZY.ORGID IN
                <iterate property="orgids" open="(" close=")" conjunction=",">
                    <![CDATA[#orgids[]# ]]>
                </iterate>

</isNotEmpty>

<select id="jzWyTradeDetailExport" parameterClass="java.util.HashMap" resultMap="JzyWyEstOrderMapPage">
       SELECT JZY.PKID,JZY.ORDER_ID
        ,JZY.GRPNAME
        ,JZY.BUDNAME
        ,JZY.RESNO
        ,JZY.CSTNAME
        ,BTO.MERCHANT_NAME MER_NAME
        ,JZY.CREATER_TIME
        ,JZY.STATUS
        ,JZY.ORDER_TYPE
        ,JZY.UPDATE_USER,JZY.UPDATE_TIME,JZY.DELETE_FLAG
        ,BO.PAYABLE_AMT PAYABLE_AMT
        ,BO.REAL_AMT REAL_AMT
        ,BO.FEE_MONEY FEE_MONEY
        ,BTO.BALANCE_TYPE BALANCE_TYPE
        ,JWH.UNITNO
        ,JWH.HOUSEFLOOR
        ,JWH.FLOORNUM
        ,MU.LOGIN_NAME USER_NAME
        FROM JZYWY_EST_ORDER JZY
        INNER JOIN BUSI_ORDER BO
          ON JZY.ORDER_ID = BO.ORDER_ID
        AND BO.PAY_STATUS='01'
        LEFT JOIN BUSI_TRANS_ORDER BTO
          ON BO.ORDER_ID = BTO.ORDER_ID
        LEFT JOIN JZYWY_HOUSE JWH
          ON JWH.PKID = JZY.HOUSEID
       LEFT JOIN MER_USER MU
           ON MU.USER_ID = BO.USER_ID
        INNER JOIN JZYWY_ORG JO
          ON JZY.ORGID = JO.ORGID
        AND JO.DELETE_FLAG='0'
       <dynamic prepend="where">
           <isNotEmpty prepend="AND" property="pkid">
             JZY.PKID = #pkid#
          </isNotEmpty>
          <isNotEmpty prepend="AND" property="orderId">
              INSTR(JZY.ORDER_ID , #orderId#) > 0
          </isNotEmpty>
          <isNotEmpty prepend="AND" property="orderType">
                JZY.ORDER_TYPE = #orderType#
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="status">
                JZY.STATUS = #status#
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="orgid">
             JZY.ORGID = #orgid#
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="grpid">
             JZY.GRPID = #grpid#
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="cstname">
             INSTR(JZY.CSTNAME , #cstname#) > 0
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="resno">
             INSTR(JZY.RESNO , #resno#) > 0
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="beginDateStr">
                  <![CDATA[
                     TRUNC(jzy.CREATER_TIME,'DD') >= TO_DATE(#beginDateStr#,'YYYY/MM/DD')
                  ]]>
             </isNotEmpty>
             <isNotEmpty prepend="AND" property="endDateStr">
                     <![CDATA[
                     TRUNC(jzy.CREATER_TIME,'DD') <= TO_DATE(#endDateStr#,'YYYY/MM/DD')
                  ]]>
             </isNotEmpty>
             <isNotEmpty prepend="AND" property="updateUser">
             BO.USER_ID = #updateUser#
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="orgids">
                JZY.ORGID IN
                <iterate property="orgids" open="(" close=")" conjunction=",">
                    <![CDATA[#orgids[]# ]]>
                </iterate>
            </isNotEmpty>
            <isNotEmpty prepend="AND" property="budid">
             JO.MERCHANT_ID = #budid#
            </isNotEmpty>
       </dynamic>
      ORDER BY JZY.CREATER_TIME DESC
   </select>