CODEWITHSUNDEEP
javasqlmappingmybatis
ReiSchneider
ReiSchneider

Reputation: 195

myBatis Many To Many Relationship

In relation to my previous question regarding Mappings in MyBatis, I am now encountering a problem when doing a Many-To-Many relationship

I have the following objects

I cannot post the actual object definition and XML due to privacy issues

class ObjectA {
     List<Items> items;
     List<Products> products;

     //getters setters
}

class Items {
   int id;
   String description;
   List<Tags> tags;
}

class Tags {
    int id;
    int item_id;
    String tag;
}

The SQL involves joining the Items table with Tags table ON Tags.ITEM_ID = Items.ID

Basically, it returns rows and maps them to this MyBatis

<resultMap id="getObjectA" type="ObjectA">
    <collection property="items" javaType="ArrayList" ofType="Items" resultMap="getItems"/>
     
     ...

</resultMap>

<resultMap="getItems" type="Items">
    <id property="id" column="ITEMS.id"/>
    <result property="description" column="ITEMS.description"/>
    <collection property="tags" javaType="ArrayList" ofType="Tags" resultMap="getTags"/>
</resultMap>

<resultMap id="getTags" type="Tags">
   <id property="id" column="TAGS.id"/>
   <result property="item_id" column="TAGS.item_id"/>
   <result property="tag" column="TAGS.tag"/>
</resultMap>

Technically, the set up works and returns an ObjectA containing a List of Items each containing a List of Tags. When there exists only one Tag per Items, the mapping is fine but when an Item contains multiple Tags, it creates multiple Items of the same ID each containing a List containing only one Tag from the result of the query.

Upvotes: 1

Views: 1734

Answers (1)

Roman-Stop RU aggression in UA
Roman-Stop RU aggression in UA

Reputation: 15861

The fact that duplicates for parent objects are created means that mybatis cannot identify objects correctly, that is that <id property="id" column="ITEMS.id"/> does not work as expected. Basically id element is needed so that mybatis knows that the parent record that is duplicated in multiple rows in a result set refers to the same object.

One working option is to make sure columns from different tables have unique names and use that unique names in result map.

For your example mapper should look like this:

<resultMap type="ObjectA" id="objectaMap">
    <id column="id" property="id"/>
    <collection property="items" javaType="ArrayList" ofType="Items" columnPrefix="ITEM_">
        <id column="id" property="id"/>
        <result property="description" column="description"/>
        <collection property="tags" javaType="ArrayList" ofType="Tags" columnPrefix="TAG_">
            <id column="id" property="id"/>
            <result property="tag" column="tag"/>
        </collection>
    </collection>
</resultMap>

<select id="getObjects" resultMap="objectaMap">
    select o.*, i.id as ITEM_ID, i.description as ITEM_DESCRIPTION, t.id as ITEM_TAG_ID, t.tag as ITEM_TAG_TAG
    from objecta o
        join items i on (i.object_id = o.id) 
        join tags t on (t.item_id = i.id)
</select>

Note how all columns from items table have ITEM_ prefix and the same for tags table.

Upvotes: 3

Related Questions