Mapping a tree with MyBatis?

Question

I'm trying to muddle my way thru setting up a tree structure and mapping it with MyBatis. My table is defined as;

CREATE TABLE `Hierarchy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) NULL DEFAULT NULL,
  `name` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I tried to do an association in MyBatis with;

@Select(SELECT_ALL)
    @Results(value = {
            @Result(property = "id", column = "id"),
            @Result(property = "children", column = "parentId",
                    jdbcType = JdbcType.NUMERIC,
                    many = @Many(select = "selectById")),
            @Result(property = "name", column = "name")
    })
    List<Hierarchy> selectAll();

My class has;

private Integer id;
private Integer parentId;
private String name;
private List<Hierarchy> children;

I pretty quickly realized this wouldn't work, since it's going to wind up associating backwards, and I get children back multiple times in the result set. So what's the answer? Do I have to iterate after I do the select and populate my children that way?

I tried a few approaches to that, but they all seem horribly inefficient, and I'm finding it hard to handle forward-references of parent id's without iterating the list twice.

So, has anyone pulled this off before? What's the trick?

Answer 1

I did this a few years ago with iBatis, and was never satisfied with the result. My tree was read-only, which simplified the code. I also needed to navigate the tree up and down, so each record had a parent class pointer. The algorithm (using your class names etc) was:

1. Read the whole database table into a List. This just used "select * from hierarchy" and standard result mapping.
2. Iterate through the list, forming an index (Map) on the primary keys, and noting the root element, which should be the only record with a null parentId.
3. Iterate through the list a second time, looking up the parent record of each item from the Map, and calling this.setParent(Hierarchy parent) and parent.addChild(Hierarchy child)

This all works fine but is far from elegant. As you noted, it needs two passes through the list. If you find a better method, please share it.