欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-04-01 23:43:59
...
    <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>