Mybatis+Mysql 实现树形结构查询

Mybatis <collection 实现树形结构>该实现使用的是SpringBoot 其实用Spring也是差不多的只要知道Mapper怎么写就可以


直接贴代码：
1.MySql创建数据库表
CREATE TABLE `node` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(32) NOT NULL,
 `parent_id` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COMMENT='节点表'
简单的节点父子关系设计，下面插入几条数据：


INSERT INTO node (name, parent_id) VALUES ('一级节点A', 0);
INSERT INTO node (name, parent_id) VALUES ('一级节点B', 0);
INSERT INTO node (name, parent_id) VALUES ('一级节点C', 0);
INSERT INTO node (name, parent_id) VALUES ('二级节点AA', 1);
INSERT INTO node (name, parent_id) VALUES ('二级节点aa', 1);
INSERT INTO node (name, parent_id) VALUES ('二级节点BB', 2);
INSERT INTO node (name, parent_id) VALUES ('三级级节点AAA', 4);
INSERT INTO node (name, parent_id) VALUES ('三级级节点aaa', 4);
INSERT INTO node (name, parent_id) VALUES ('三级级节点BBB', 6);

2.Bean：
/**
* 节点实体类
*
* Created by bysocket on 24/01/2018.
*/
public class Node {


    /**
     * 编号
     */
    private Long id;


    /**
     * 名称
     */
    private String name;


    /**
     * 下一个节点
     */
    private List<Node> next;


    public Long getId() {
        return id;
    }


    public void setId(Long id) {
        this.id = id;
    }


    public String getName() {
        return name;
    }


    public void setName(String name) {
        this.name = name;
    }


    public List<Node> getNext() {
        return next;
    }


    public void setNext(List<Node> next) {
        this.next = next;
    }
}




3.service:
/**
* 节点业务逻辑接口类
*
* Created by bysocket on 24/01/2018.
*/
public interface NodeService {


    /**
     * 获取节点树
     */
    List<Node> getNodeTree();
}


4.service实现类：
/**
* 节点业务逻辑实现类
*
* Created by bysocket on 24/01/2018.
*/
@Service
public class CityServiceImpl implements NodeService {


    @Autowired
    private NodeDao nodeDao;


    @Override
    public List<Node> getNodeTree() {
        return nodeDao.getNodeTree();
    }
}


5.Dao层：
/**
* 节点 DAO 接口类
*
* Created by bysocket on 24/01/2018.
*/
public interface NodeDao {


    /**
     * 获取节点树
     */
    List<Node> getNodeTree();
}


6.Mapper：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.mybatis.dao.NodeDao">
 <resultMap id="BaseTreeResultMap" type="org.mybatis.domain.Node">
<result column="id" property="id"/>
<result column="name" property="name"/>
<collection column="id" property="next" javaType="java.util.ArrayList"
ofType="org.mybatis.domain.Node" select="getNextNodeTree"/>
 </resultMap>


 <resultMap id="NextTreeResultMap" type="org.mybatis.domain.Node">
<result column="id" property="id"/>
<result column="name" property="name"/>
<collection column="id" property="next" javaType="java.util.ArrayList"
ofType="org.mybatis.domain.Node" select="getNextNodeTree"/>
 </resultMap>


 <sql id="Base_Column_List">
id, name
</sql>


 <select id="getNextNodeTree" resultMap="NextTreeResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM node
WHERE parent_id = #{id}
 </select>


 <select id="getNodeTree" resultMap="BaseTreeResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM node
WHERE parent_id = 0
 </select>


</mapper>


7.controller：

/**
* 节点业务控制成类
*
* Created by bysocket on 24/01/2018.
*/
@RestController
public class NodeRestController {


    @Autowired
    private NodeService nodeService;


    @GetMapping(value = "/node/tree")
    public List<Node> getNodeTree() {
        return nodeService.getNodeTree();
    }


}




试起来吧！
文本摘自：https://www.bysocket.com/?p=2008
工程代码：https://github.com/JeffLi1993/myabtis-learning-example