Mybatis递归查询树结构

Mybatis递归查询树结构样例

1. 实体类

@TableName(value ="t_test")
@Data
public class TTest {

    @TableField("id")
    private String id;

    @TableField("parent_id")
    private String parentId;

    @TableField("name")
    private String name;

    @TableField(exist = false)
    private List<TTest> children;

}

2. mapper接口类

List<TTest> getAllNode();

3. mapper xml类

<!-- 自定义返回值映射，子节点为查询SQL，并指定返回值字段 -->
<resultMap id="nodeList" type="com.asiainfo.sx.data.pojo.alarm.TTest">
    <id column="id" property="id"/>
    <result column="parent_id" property="parentId"/>
    <result column="name" property="name"/>
    <collection property="nodeList" column="{id=id, parentId=parent_id, name= name}" select="getNodeByParentId"/>
</resultMap>

<!-- 获取所有节点，返回值类型为自定义映射resultMap -->
<select id="getAllNode" resultMap="nodeList">
    select * from t_test where parent_id = 0
</select>

<!-- 查询子节点，返回值类型为自定义映射resultMap -->
<select id="getNodeByParentId" resultMap="nodeList">
    select * from t_test where parent_id = #{id}
</select>

4. 测试类

@SpringBootTest
@RunWith(SpringRunner.class)
public class Test {

    @Autowired
    private AlarlmListMapper listMapper

    @org.junit.Test
    public void test(){
        List<TTest> allNode = listMapper.getAllNode();
        System.out.println(JSONUtil.toJsonStr(allNode));
    }
}

输出结果：

[
    {
        "nodeList": [
            {
                "nodeList": [
                    {
                        "nodeList": [],
                        "parentId": "10",
                        "name": "老大-10-101",
                        "id": "101"
                    }
                ],
                "parentId": "1",
                "name": "老大-10",
                "id": "10"
            },
            {
                "nodeList": [],
                "parentId": "1",
                "name": "老大-11",
                "id": "11"
            }
        ],
        "parentId": "0",
        "name": "老大",
        "id": "1"
    },
    {
        "nodeList": [
            {
                "nodeList": [
                    {
                        "nodeList": [],
                        "parentId": "20",
                        "name": "老二-20-201",
                        "id": "201"
                    }
                ],
                "parentId": "2",
                "name": "老二-20",
                "id": "20"
            },
            {
                "nodeList": [],
                "parentId": "2",
                "name": "老二-21",
                "id": "21"
            }
        ],
        "parentId": "0",
        "name": "老二",
        "id": "2"
    }
]