Mybaits配置主表和子表关联查询

最新推荐文章于 2024-06-05 17:55:05 发布

iteye_7916

最新推荐文章于 2024-06-05 17:55:05 发布

阅读量340

点赞数

分类专栏： java mybatis

本文链接：https://blog.csdn.net/iteye_7916/article/details/82600715

版权

java mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

    <resultMap id="BaseResultMap" type="com.transcore.common.dal.model.PaymentDO">
        <id column="ID" property="id"/>
        <result column="BUSINESS_CHANNEL" property="businessChannel"/>
        <result column="CURRENCY_CODE" property="currencyCode"/>
        <result column="PAYMENT_MODE" property="paymentMode"/>
        <result column="TRANS_ID" property="transId"/>
        <result column="OPERATOR_IP" property="operatorIp"/>
        <result column="OPERATOR_ID" property="operatorId"/>
        <result column="AMOUNT" property="amount"/>
        <result column="STATUS" property="status"/>
        <result column="CREATE_TIME" property="createTime"/>
        <result column="MODIFY_TIME" property="modifyTime"/>
    </resultMap>

    <resultMap id="detailPaymentMap" type="com.transcore.common.dal.model.PaymentDO"
               extends="BaseResultMap">
        <collection property="paymentDetailList" column="PAYMENT_ID"
                    ofType="com.transcore.common.dal.model.PaymentDetailDO">
            <id column="PD_ID" property="id"/>
            <result column="PAYMENT_ID" property="paymentId"/>
            <result column="PAYMENT_TYPE" property="paymentType"/>
            <result column="CREDENTIAL_ID" property="credentialId"/>
            <result column="CREDENTIAL_NAME" property="credentialName"/>
            <result column="CURRENCY_CODE" property="currencyCode"/>
            <result column="PD_AMOUNT" property="amount"/>
            <result column="PD_CREATE_TIME" property="createTime"/>
            <result column="PD_MODIFY_TIME" property="modifyTime"/>
            <result column="EXTERNAL_FIELDS" property="externalFields"/>
            <result column="SUB_PAY_TYPE" property="subPayType"/>
	        <result column="FOREIGN_AMOUNT" property="foreignAmt"/>
	        <result column="FOREIGN_CURRENCY" property="foreignCcy"/>
	        <result column="EXCHANGE_RATE" property="exchangeRate"/>
        </collection>
    </resultMap>

    <sql id="Base_Column_List">
        ID,  BUSINESS_CHANNEL, CURRENCY_CODE, PAYMENT_MODE, TRANS_ID,
        OPERATOR_IP, OPERATOR_ID, AMOUNT, STATUS, CREATE_TIME, MODIFY_TIME
    </sql>

  <select id="queryPaymentDetailByPage"
            resultMap="detailPaymentMap" parameterType="map">
        select
        p.ID,  p.BUSINESS_CHANNEL, p.CURRENCY_CODE, p.PAYMENT_MODE, p.TRANS_ID,
        p.OPERATOR_IP, p.OPERATOR_ID, p.AMOUNT, p.STATUS, p.CREATE_TIME, p.MODIFY_TIME,

        pd.ID AS PD_ID, pd.PAYMENT_ID, pd.PAYMENT_TYPE, pd.CREDENTIAL_ID, pd.CREDENTIAL_NAME,
        pd.CURRENCY_CODE, pd.AMOUNT AS PD_AMOUNT, pd.CREATE_TIME AS PD_CREATE_TIME
        , pd.MODIFY_TIME AS PD_MODIFY_TIME,pd.EXTERNAL_FIELDS AS EXTERNAL_FIELDS,
        pd.SUB_PAY_TYPE as SUB_PAY_TYPE,
        pd.FOREIGN_AMOUNT as FOREIGN_AMOUNT,
        pd.FOREIGN_CURRENCY as FOREIGN_CURRENCY,
        pd.EXCHANGE_RATE as EXCHANGE_RATE
        from T_TRANSCORE_PAYMENT p INNER JOIN T_TRANSCORE_PAYMENT_DETAIL pd
          ON p.ID = pd.PAYMENT_ID
        where p.TRANS_ID = #{id}
        <if test="status != null">AND p.STATUS = #{status}</if>
        <if test="payType != null">AND pd.PAYMENT_TYPE = #{payType}</if>
        <if test="paymentId != null">AND p.ID = #{paymentId}</if>
        order by p.CREATE_TIME desc
    </select>