How to implement recursive mapping using mybatis?

I have a java class

class Node {
  String id;
  String name;
  List<Node> children;
}

And the tables in MySQL:

NODES:
| ID | NAME |
| 1  | name1 |
| 2  | name2 |
| 3  | name3 |
| 4  | name4 |

and another table for the relations

RELATIONS:
| PARENT_ID | CHILD_ID |
|    1      |     2    |
|    2      |     3    |
|    2      |     4    |

Normally we can use left join and collection to wire them together like below

<resultMap id="node" type="org.hello.Node>
  <id property="id" column="ID"/>
  <result property="name" column="NAME"/>
  <collection property="children" ofType="org.hello.Node" resultMap="node"/>
</resultMap>

<select id="select" resultMap="node">
 SELECT ID, NAME FROM NODES N
 LEFT JOIN RELATIONS R ON N.ID = R.PARENT_ID
 LEFT JOIN NODES N1 ON R.CHILD_ID = N1.ID
</select>

If N1 is just another table with another model, it should work. The question is How can we write mybatis xml configuration to recursively map above structure which refers back to itself?

Solution

You just need to assign proper column aliases and use columnPrefix.

<resultMap type="org.hello.Node" id="node">
  <id property="id" column="ID" />
  <result property="name" column="NAME" />
  <collection property="children" resultMap="node"
    columnPrefix="C_"/>
</resultMap>

<select id="select" resultMap="node">
  SELECT
    N1.ID, N1.NAME,
    N2.ID C_ID, N2.NAME C_NAME,
  FROM NODES N1
  LEFT JOIN RELATIONS R1 ON R1.PARENT_ID = N1.ID
  LEFT JOIN NODES N2 ON N2.ID = R1.CHILD_ID
</select>

I should also mention that the above result map works with any levels of nodes because columnPrefix is applied recursively.

<select id="select" resultMap="node">
  SELECT
    N1.ID, N1.NAME,
    N2.ID C_ID, N2.NAME C_NAME,
    N3.ID C_C_ID, N3.NAME C_C_NAME
  FROM NODES N1
  LEFT JOIN RELATIONS R1 ON R1.PARENT_ID = N1.ID
  LEFT JOIN NODES N2 ON N2.ID = R1.CHILD_ID
  LEFT JOIN RELATIONS R2 ON R2.PARENT_ID = N2.ID
  LEFT JOIN NODES N3 ON N3.ID = R2.CHILD_ID
</select>