MyBatis 2nd nested list returns null

Question

Everything was working with MyBatis and the objects I have outlined, until I introduced a LIST within the first child List object. So now, my structure is:

See Object Relationship

I've tried a number of approaches through the XML mapper, with variations on resultMap configurations. This is what I have currently:

<resultMap id="saleTransaction" type="com.company.sale.domain.Sale" autoMapping="true">
    <result property="transactionNumber" column="TRANSACTION_ID"/>
    <result property="salesTrip.tripDate" column="TRIP_DATE"/>
    <result property="salesTrip.tripNumber" column="TRIP_NUMBER"/>
    <result property="salesTrip.tripOriginTerminal.iataCode" column="ORIGIN_IATA_CODE"/>
    <result property="salesTrip.tripOriginTerminal.city" column="ORIGIN_CITY_NAME"/>
    <result property="salesTrip.tripDestinationTerminal.iataCode" column="DESTINATION_IATA_CODE"/>
    <result property="salesTrip.tripDestinationTerminal.city" column="DESTINATION_CITY_NAME"/>

    <collection property="salesTransactionPayments" ofType="SalesTransactionPayment">
        <result property="amount" column="AMOUNT"/>
        <result property="creditCard.cardNumber" column="CC_NUMBER"/>
        <result property="creditCard.nameOnCard" column="CCHOLDER_NAME"/>
    </collection>

    <collection property="salesTransactionItems" column="TRANSACTION_ID" ofType="SalesTransactionItem" select="getSaleItems">
        <result property="item" column="ITEM"/>
        <result property="price" column="PRICE"/>
        <result property="qty" column="QTY"/>
        <association property="salesTransactionTaxRates" column="ID" resultMap="taxResult" />
    </collection>

</resultMap>

<resultMap id="taxResult" type="com.guestlogix.sale.domain.SalesTransactionTaxRate" autoMapping="true">
    <result property="code" column="code"/>
    <result property="rate" column="rate"/>
    <result property="isFixed" column="isFixed"/>
</resultMap>

The Object SalesTransactionTaxRate is a child to SalesTransactionItem, which in turn is a child of the parent Sale object. SalesTransactionItem is a LIST and then SalesTransactionTaxRate is a LIST as well. There can be many taxes for an ITEM and there can be many ITEMS for a SALE.

I've tried mapping SalesTransactionTaxRate as a collection within SalesTransactionItem collection, again to no avail. When I use that approach, it does not even register that SalesTransactionTaxRate is contained within SalesTransactionItem at all in the IDE. Otherwise, there is autocomplete for the other objects and properties.

All the documentation shows that a collection within a collection is possible, it just does not seem to be working for me for some reason.

Any help or advice is hugely appreciated.

score 1 · Answer 1 · answered Mar 16 '17 at 22:15

I guess you are referring to the  from the documentation. Indeed mapping a collection inside another is possible.

What you have missed is <id> element of the result map. I will admit the documentation stating only id – an ID result; flagging results as ID will help improve overall performance is not enough and does not explains how it is important. This id specifies the column/property used to "group by" on to build nested lists, otherwise the mapping matches on container object equals method with likely unexpected results.

It seems that was attempted by specifying the column attribute inside the <collection>.

Eventually using <id> may be not required, but I would strongly recommend it anyway to make things clearer.

I have just noticed the real issue: collection salesTransactionItems uses a nested select, that means the nested mapping is actually not used because the nested select is a distinct statement in a distinct scope, using its own result map. This result map does not exist, but partial mapping is done simply by default by auto-mapping the simple typed columns/properties, others (collection in this case) are just ignored and then null. This result map must be defined and used by getSaleItems select statement.

Following mapping should be closer to the truth:

    <resultMap id="saleTransaction" type="com.company.sale.domain.Sale" autoMapping="true">
        <id property="transactionNumber" column="TRANSACTION_ID"/>
        <result property="salesTrip.tripDate" column="TRIP_DATE"/>
        <result property="salesTrip.tripNumber" column="TRIP_NUMBER"/>
        <result property="salesTrip.tripOriginTerminal.iataCode" column="ORIGIN_IATA_CODE"/>
        <result property="salesTrip.tripOriginTerminal.city" column="ORIGIN_CITY_NAME"/>
        <result property="salesTrip.tripDestinationTerminal.iataCode" column="DESTINATION_IATA_CODE"/>
        <result property="salesTrip.tripDestinationTerminal.city" column="DESTINATION_CITY_NAME"/>

        <collection property="salesTransactionPayments" ofType="SalesTransactionPayment">
            <result property="amount" column="AMOUNT"/>
            <result property="creditCard.cardNumber" column="CC_NUMBER"/>
            <result property="creditCard.nameOnCard" column="CCHOLDER_NAME"/>
        </collection>

        <collection property="salesTransactionItems" select="getSaleItems" />

    </resultMap>

    <resultMap id="saleItem" type="com.guestlogix.sale.domain.SalesTransactionItem"
        <id property="item" column="ITEM"/>
        <result property="price" column="PRICE"/>
        <result property="qty" column="QTY"/>
        <collection property="salesTransactionTaxRates" resultMap="taxResult" />
    </resultMap>

    <resultMap id="taxResult" type="com.guestlogix.sale.domain.SalesTransactionTaxRate" autoMapping="true">
        <result property="code" column="code"/>
        <result property="rate" column="rate"/>
        <result property="isFixed" column="isFixed"/>
    </resultMap>

It's definitely the standalone SQL Select statement. It's independent of the main query, thus not able to resolve as is. An inclusive SQL query returning the items via JOINS would be ideal. — SiriusBits, Mar 17 '17 at 22:39

MyBatis 2nd nested list returns null

1 Answers1