Mybatis one-to-many collection mapping always have one default entity

Question

I want to rewrite our services to use mybatis mapping and joins to have our entity full and completed on database/mybatis layer.

<resultMap id="ParentMap" type="org.example.mybatis.Parent">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <id column="Name" jdbcType="VARCHAR" property="name" />
    <id column="SurName" jdbcType="VARCHAR" property="surName" />

    <collection property="childs" column="ChildId"
        javaType="ArrayList" ofType="org.example.mybatis.Child"
        resultMap="org.example.ChildMap" />    
</resultMap>

<resultMap id="ChildMap" type="org.example.mybatis.Parent">

    <id column="id" jdbcType="VARCHAR" property="id" />
    <id column="Name" jdbcType="VARCHAR" property="name" />
    <id column="SurName" jdbcType="VARCHAR" property="surName" />
    <id column="Age" jdbcType="INTEGER" property="age" />
</resultMap>

<sql id="Parent_Column_List">
    p.Id, p.Name, p.SurName,
</sql>  

<sql id="Child_Column_List">
    c.Id, c.ParentId c.Name, c.SurName, c.Age
</sql>  

<select id="getParent" parameterType="java.lang.String" resultMap="ParentMap" >
    select 
    <include refid="Parent_Column_List"/>

    <include refid="Child_Column_List" />
    from Parent p

    left outer join Child c on p.Id = c.ParentId
    where p.id = #{id,jdbcType=VARCHAR}

Problem is next: if parent doesn't has childs, some default entity with null or default fields will be added to list. I understand that this is nature of outer join, but is mybatis not very clever to understand that this is fake?

Is there some workaround about this? I cannot use inner join since parent entity is required to be in result.

Answer 1

You have to put the attribute notNullColumn in your collection. So your resultMap will be:

<resultMap id="ParentMap" type="org.example.mybatis.Parent">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <id column="Name" jdbcType="VARCHAR" property="name" />
    <id column="SurName" jdbcType="VARCHAR" property="surName" />

    <collection property="childs" column="ChildId" notNullColumn="id"
        javaType="ArrayList" ofType="org.example.mybatis.Child"
        resultMap="org.example.ChildMap" />    
</resultMap>

Note that you will also maybe have issues with the two id, so you will maybe have to have a c.id as ChildId in your select