javaxmlspringmybatis

Mybatis xml resultMap with collection and association issue


i m actually testing mybatis. I like really but, i want to go deeper and i have a problem, with resultMap.

Actually i just want to get from database a computer object, which is composed of multiple screens and one tower (other object of my code)

This is my resultMap for computer :

<resultMap type="entity.Computer" id="computer">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <association property="tower" column="towerid" resultMap="towerResult" columnPrefix="t_"/>
        <collection ofType="entity.Screen" property="screen" javaType="ArrayList" resultMap="screenResult" columnPrefix="s_"/>
    </resultMap>

this the request :

<select id="getcomputerById" resultMap="computer">
        Select c.id, c.name, c.towerid, s.id as s_id, s.size as s_size, s.type as s_type, s.computer_id as s_computer_id, t.id as t_id, t.ram as t_ram, t.stockage as t_stockage from computer c inner join tower t on t.id = c.towerid left join screen s ON s.computer_id = c.id where c.id=#{computerId}
    </select>

With this code everything works fine. BUTTTTTTTT ! What i wanted to do is :

<resultMap type="entity.Computer" id="computer">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <association property="tower" column="towerid" select="getTowerbycomputerid"/>
        <collection ofType="entity.Screen" property="screen" javaType="ArrayList" resultMap="screenResult" columnPrefix="s_"/>
    </resultMap>

The only thing different is : <association property="tower" column="towerid" select="getTowerbycomputerid"/>

Of course i change my request to :

<select id="getcomputerById" resultMap="computer">
        Select c.id, c.name, c.towerid, s.id as s_id, s.size as s_size, s.type as s_type, s.computer_id as s_computer_id from computer c inner join tower t on t.id = c.towerid left join screen s ON s.computer_id = c.id where c.id=#{computerId}
    </select>

There is the xml match the getTowerbycomputerid :

<select id="getTowerbycomputerid" resultMap="towerResult">
        Select t.id, t.ram, t.stockage from tower t inner join computer c on c.towerid=t.id where c.id=#{computerId}
    </select>

And the resultMap :

<resultMap id="towerResult" type="entity.Tower">
        <id property="id" column="id"/>
        <result property="ram" column="ram"/>
        <result property="stockage" column="stockage"/>
    </resultMap>

I don't understand why the second resultmap don't work. If i have one-one tower and one-one Screen I can have a resultmap, with two association and in them a select="getmethod" And it work perfectly But when i change my code to have one-one tower and one-many Screen, i can't let select="getmethod" for the last association. It return null for the one-one, but the one-many work (with the right select statement).

Any idea ? Maybe it's not possible to do it?

THx :)


Solution

  • I answer my own question, @ave's comment put me on the right way:

    It should be possible. The nested select getTowerbycomputerid seems to expect computer id, whereas you specify column="towerid" in the association. Shouldn't it be "id"? If that's not the reason, please consider providing a complete example like these.

    It's not exactly this but it helped me to find a solution. There is my request:

    @Select("Select c.id, c.name, c.towerid, s.id as s_id, s.size as s_size, s.type as s_type, s.computer_id as s_computer_id from computer c left join screen s ON s.computer_id = c.id where c.id=#{computerId}")
    @ResultMap("ComputerMapper.computer")
    public Computer getcomputerById(@Param("computerId") Integer computerId);
    

    This is my resultMap:

    <resultMap type="entity.Computer" id="computer">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <association property="tower" column="towerid" javaType="entity.Tower" select="getTowerbycomputerid"/>
            <collection ofType="entity.Screen" property="screen" javaType="ArrayList" resultMap="screenResult" columnPrefix="s_"/>
        </resultMap>
    

    And now my resultMap and the request to get the tower:

    <resultMap id="towerResult" type="entity.Tower">
            <id property="id" column="id"/>
            <result property="ram" column="ram"/>
            <result property="stockage" column="stockage"/>
        </resultMap>
        <select id="getTowerbycomputerid" resultMap="towerResult">
            Select t.id, t.ram, t.stockage from tower t where t.id = #{towerid}
        </select>
    

    And now everything works fine. Before I got this to select the tower:

    <select id="getTowerbycomputerid" resultMap="towerResult">
                Select t.id, t.ram, t.stockage from tower t inner join computer c on c.towerid = t.id where c.id = #{computerId}
            </select>
    

    This is the end. Thx @ave :)