Mybatis+Mysql 实现树形结构查询

客官°小女子只卖身不卖艺 2022-05-31 04:08 532阅读 0赞

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();  
    \}

//附加一个递归的获取树节点

public List<Node> getTree(){
    //        int i = 0;
            List<Node> list = nodeService.getNodeTree();
    //        首次循环获取母节点
            for(Node node: list){
                System.out.println("start"+node.getName());
                getList(node.getNext());
            }
    
            return list;
        }
        public void getList(List<Node> list){
    
            Node nd = new Node();
            if(list != null){
                if(list.size()>0){
                    for(Node node : list){
    //                    i++;
                        System.out.println(node.getName());
                        nd.setId(node.getId());
                        nd.setName(node.getName());
                        //递归获取下一个子节点
                        getList(node.getNext());
                    }
                } else{
    //                i = 0;
    //                System.out.println("枝干结束"+i);
                }
            } else{
    //            i = 0;
            }
        }

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