MyBatis - Fetch multiple collections

Question

I am preety new to MyBatis so wanted to if somebody can help me with my issue. The problem surrounds around Television programs.

Objects

• Programs
• Products

Mappings

• One Program shows multiple products
• Programs can be repeated multiple times in future or have been in past

Select Query

<select id="findsProgramGuideDetails" resultMap="programGuideDetails" parameterType="map">
        select 
             sourceCode, showCode, productNumber, programTitle, OTHER_FIELDS 
             from MY_TABLE where SOME_CONDITIONS
</select>

Result map

<resultMap id="programGuideDetails" type="com.qvc.integration.batch.programguide.vo.UdbProgramGuideDataloadDetailsVo">
    <id property="sourceCode" column="sourceCode"
typeHandler="com.test.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />

  <result property="showCode" column="showCode"
  typeHandler="com.test.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />

  <result property="programTitle" column="programTitle"
                typeHandler="com.test.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
... Other Result tags

Collection inside Resultmap

<collection property="plannedShows" ofType="com.test.programguide.dataload.model.vo.PlannedShow" column="programTitle">
    <id property="showTitle" column="programTitle"
                typeHandler="com.test.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
    <result property="id" column="sourceCode"
                typeHandler="com.test.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
    <result property="channelCode" column="channel"
                typeHandler="com.test.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
    <result property="startDate" column="startDate"
                typeHandler="com.test.integration.batch.programguide.typehandler.DateFormatTypeHandler" />
    <result property="endDate" column="endDate"
                typeHandler="com.test.integration.batch.programguide.typehandler.DateFormatTypeHandler" />
</collection>

<collection property="productDetails" ofType="com.qvc.programguide.dataload.model.vo.ProductDetail" column="sourceCode">
    <id property="productId" column="productNumber"
                typeHandler="com.qvc.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
    <result property="productName" column="productName"
                typeHandler="com.qvc.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
    <result property="brandId" column="brandId"
                typeHandler="com.qvc.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
    <result property="brandName" column="brandName"
                typeHandler="com.qvc.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
    <result property="priceCode" column="priceCode"
                typeHandler="com.qvc.integration.batch.programguide.typehandler.StringTrimmingTypeHandler" />
</collection>

Problem

Multiple products are coming properly with every record but the plannedShows i.e. previous and upcoming repeat telecast is fetching itself only.

Any help would be appreciated.

Answer 1

There relation one to many is simple, use the collection tag and define the parameters to join the keys, in this case i also used a request parameter as a subselect column.

<resultMap  type="package.pojo.Recipient" id="receiversResultMap">
    <result column="EMAIL_ADDRESS" property="emailAddress" />
    <result column="END_TS" property="endTs" />
    <result column="NAME" property="name" />        
    <result column="RECIPIENT_ID" property="recipientId" />         
    <result column="START_TS" property="startTs" />         
    <result column="TYPE" property="type" />        
    <result column="UPDATE_TS" property="updateTs" />       
    <result column="USER_ID" property="userId" />       
    <collection property="messages" column="{param1=RECIPIENT_ID, param2=LOCKID}"  javaType="ArrayList" ofType="java.util.List" select="findMessages" />            
</resultMap>

<select id="findReceivers" resultMap="receiversResultMap">

        SELECT 
               EMAIL_ADDRESS,
               END_TS,
               NAME,
               RECIPIENT_ID,
               START_TS,
               TYPE,
               UPDATE_TS,
               USER_ID,
               #{lockId} AS LOCKID

                 FROM T_DOPA0_RECIPIENT     
</select>   


<resultMap  type="package.pojo.Message" id="messageResultMap">
    <result column="EMAIL_USED" property="emailUsed" />
    <result column="LOCK_ID" property="lockId" />
    <result column="MESSAGE_ID" property="messageId" />
    <result column="RECEIPT_TS" property="receiptTs" />         
    <result column="RECIPIENT_ID" property="recipientId" />         
    <result column="SELECTED" property="selected" />        
    <result column="SEND_TS" property="sendTs" />               
    <result column="TYPE" property="type" />        
    <result column="DESCRIPTION" property="description" />
</resultMap>    

<select id="findMessages"   resultMap="messageResultMap">
            SELECT
                   EMAIL_USED,
                   LOCK_ID,
                   MESSAGE_ID,
                   RECEIPT_TS,
                   RECIPIENT_ID,
                   SELECTED,
                   SEND_TS,
                   TYPE,
                   DECODE.DESCRIPTION

                    FROM T_MESSAGE MESSAGE
                    LEFT OUTER JOIN D_MESSAGE_TYPE DECODE ON DECODE.LOCK_MESSAGE_TYPE = MESSAGE.TYPE

                    WHERE   RECIPIENT_ID = '${param1}'

                            AND LOCK_ID = '${param2}'

</select>