Search code examples
sqlmybatismybatis-generator

MyBatis not retrieving certain fields

So I have a pretty complex java object that I'm mapping to my DB using MyBatis. I want to be able to retrieve the entire object, with all children, grandchildren, etc simultaneously, so I made a result map for it. It's something like

  <resultMap id="BaseResultMap" type="Parent">
    <id column="PARENT_PK" jdbcType="VARCHAR" property="pk" />
    <association property="address" javaType="address">
        <id column="address_PK" jdbcType="VARCHAR" property="pk" />
    </association>
    <collection property="Children" javaType="java.util.ArrayList" ofType="Child">
        <id column="CHILD_PK" property="Pk" jdbcType="VARCHAR" />
        <result column="SCHEMA.CHILDREN.DESCRIPTION" property="description" jdbcType="VARCHAR" />
        <collection property="Children" javaType="java.util.ArrayList" ofType="Grandchildren">
            <id column="Grand_child_pk" jdbcType="VARCHAR" property="pk" />
            <result column="SCHEMA.GRAND_CHILDREN.DESCRIPTION" property="description" jdbcType="VARCHAR" />
            <collection property="children" javaType="java.util.ArrayList" ofType="GreatGrandChildren">
                <result column="NAME" property="name" jdbcType="VARCHAR"/> 
            </collection>
        </collection>
    </collection

and my SQL for it is something like

SELECT PARENT_PK, ADDRESS_PK, CHILD_PK, SCHEMA.CHILDREN.DESCRIPTION, GRAND_CHILD_PK, SCHEMA.GRAND_CHILDREN.DESCRIPTION, NAME FROM ...

Most of the fields are populating correctly, and I'm getting the right number of children at each level. However, neither of the description fields are populating- I'm getting nulls. Everything else works fine. If I use Mybatis to retrieve just the child or grandchild without trying to load the relationships, then the description fields work fine. It's only when I attempt to get everything at once.

If I run the query manually, it retrieves the description columns no problem, so I don't think it's a problem with my sql code.

Any ideas as to what is going on?

Solution

  • I think the problem arises from the same column names in different tables: DESCRIPTION. MyBatis cannot be aware of which different tables these two columns come from. Try giving column aliases to these columns in your select query:

    SELECT PARENT_PK, ADDRESS_PK, CHILD_PK, SCHEMA.CHILDREN.DESCRIPTION C_DESC, 
GRAND_CHILD_PK, SCHEMA.GRAND_CHILDREN.DESCRIPTION GC_DESC, NAME FROM ...

    Then map with the aliases:

    <resultMap id="BaseResultMap" type="Parent">
    <id column="PARENT_PK" jdbcType="VARCHAR" property="pk" />
    <association property="address" javaType="address">
        <id column="address_PK" jdbcType="VARCHAR" property="pk" />
    </association>
    <collection property="Children" javaType="java.util.ArrayList" ofType="Child">
        <id column="CHILD_PK" property="Pk" jdbcType="VARCHAR" />
        <result column="C_DESC" property="description" jdbcType="VARCHAR" />
        <collection property="Children" javaType="java.util.ArrayList" ofType="Grandchildren">
            <id column="Grand_child_pk" jdbcType="VARCHAR" property="pk" />
            <result column="GC_DESC" property="description" jdbcType="VARCHAR" />
            <collection property="children" javaType="java.util.ArrayList" ofType="GreatGrandChildren">
                <result column="NAME" property="name" jdbcType="VARCHAR"/> 
            </collection>
        </collection>
    </collection>
</resultmap>