mybatis mysql 树形结构_Mybatis查询树形结构数据

数据表的设计

创建数据表

对于树形结构的数据库设计通常是基于继承关系设计的，也就是通过父ID关联来实现的。

一张树形结构的数据表基本的三个字段：id(自己)、name(名称)和parentid(父类编号)CREATE TABLE `tree` (

`id` int(20) NOT NULL COMMENT 'id',

`name` varchar(20) NOT NULL COMMENT '名称',

`parentid` int(20) NOT NULL COMMENT '父id',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据INSERT INTO `tree` VALUES ('100', '一级1', '0');

INSERT INTO `tree` VALUES ('101', '一级2', '0');

INSERT INTO `tree` VALUES ('201', '二级1-1', '100');

INSERT INTO `tree` VALUES ('202', '二级1-2', '100');

INSERT INTO `tree` VALUES ('203', '二级1-3', '100');

INSERT INTO `tree` VALUES ('211', '二级2-1', '101');

INSERT INTO `tree` VALUES ('212', '二级2-2', '101');

INSERT INTO `tree` VALUES ('213', '二级2-3', '101');

INSERT INTO `tree` VALUES ('301', '三级1-1-1', '201');

INSERT INTO `tree` VALUES ('302', '三级1-1-2', '201');

INSERT INTO `tree` VALUES ('311', '三级1-2-1', '202');

INSERT INTO `tree` VALUES ('321', '三级2-1-1', '211');

INSERT INTO `tree` VALUES ('322', '三级2-1-2', '211');

INSERT INTO `tree` VALUES ('331', '三级2-2-1', '212');

实体类@NoArgsConstructor

@ToString

public class implements Serializable{

private Integer id;

private String name;//名称

private Integer parentid;//父id

private List children=new ArrayList<>();//孩子

private static final long serialVersionUID = 1L;

}

实现方式

无论是通过Mybatis的递归查询还是通过代码的递归查询方式其思路都是一致的。先查询出所有一级节点

再跟进一级节点递归查询出其所有子节点

基于MyBatis的递归查询

基于注解

持久层接口：@Mapper

public interface TreeMapper{

@Select("select * from tree where parentid=#{parentId}") //根据父id查询出所有孩子

@Results({

@Result(id=true,property="id",column="id"),

@Result(property="name",column="name"),

@Result(property="parentid",column="parentid"),

@Result(property="children",column="id",javaType=java.util.List.class,

many=@Many(select="cn.ghjcloud.tree.dao.TreeMapper.selectChildrenByPId"))

//column为父节点Id

})

List selectChildrenByPId(Integer parentId);

@Select("select * from tree where parentid=0") //查询出所有一级目录

@Results({

@Result(id=true,property="id",column="id"),

@Result(property="name",column="name"),

@Result(property="parentid",column="parentid"),

@Result(property="children",column="id",javaType=java.util.List.class,

many=@Many(select="cn.ghjcloud.tree.dao.TreeMapper.selectChildrenByPId"))

})

List selectTreeList();

}

基于XML

TreeMapper.xml<?xml version="1.0" encoding="UTF-8"?>

select="selectChildrenByPId">

select * from tree where parentid=#{parentId}

select * from tree where parentid=0

接口：@Mapper

public interface TreeMapper{

List selectChildrenByPId(Integer parentId);

List selectTreeList();

}

注解对应的XML的元素注解xml@ResultsresultMap

@ResultResult

@Manycollection

column:实体类中对应的属性

property：mysql表中的字段

基于代码的递归查询

根据某个父节点递归获取子节点/**

* 根据某个父节点递归获取子节点

* @param parentTree 父节点

* @param list 全部节点

* @return

*/

public Tree getChildrenByPId(Tree parentTree,List list){

list.forEach((tree)->{

if(tree.getParentid().equals(parentTree.getId())){

tree=getChildrenByPId(tree,list);

parentTree.getChildren().add(tree);

}

});

return parentTree;

}

先获取所有一级节点，再调用上面的getChildrenByPId( )方法递归查询出所有子节点/**

* 递归获取树形结构的数据

* @param list 全部节点

* @return

*/

private List getTreeList(List list){

List treeList=new ArrayList<>();

list.forEach((tree -> {

//获取所有一级节点

if(tree.getParentid()==0){

treeList.add(tree);

}

}));

// 递归获取子节点

treeList.forEach((parentTree)->{

parentTree=this.getChildrenByPId(parentTree,list);

});

return treeList;

}

测试

基于MyBatis的递归查询

service层@Service

public class TreeService{

@Autowired

private TreeMapper treeMapper;

public List selectTreeList(){

List trees=treeMapper.selectTreeList();

return trees;

}

}

Controller层@RestController

@RequestMapping("/tree")

public class TreeController{

@Autowired

private TreeService treeService;

@GetMapping("/list")

public ResponseEntity> selectTreeList(){

return ResponseEntity.ok(treeService.selectTreeList());

}

}

基于代码的递归查询

Mapper接口@Repository

public interface TreeMapper extends Mapper<>{}

Service层@Service

public class TreeService{

@Autowired

private TreeMapper treeMapper;

public List findTreeList(){

//获取所有节点

List list=treeMapper.selectAll();

List treeList=this.getTreeList(list);

return treeList;

}

/**

* 递归获取树形结构的数据

* @param list 全部节点

* @return

*/

private List getTreeList(List list){

List treeList=new ArrayList<>();

list.forEach((tree -> {

//获取所有一级节点

if(tree.getParentid()==0){

treeList.add(tree);

}

}));

// 递归获取子节点

treeList.forEach((parentTree)->{

parentTree=this.getChildrenByPId(parentTree,list);

});

return treeList;

}

/**

* 根据某个父节点递归获取子节点

* @param parentTree 父节点

* @param list 全部节点

* @return

*/

public Tree getChildrenByPId(Tree parentTree,List list){

list.forEach((tree)->{

if(tree.getParentid().equals(parentTree.getId())){

tree=getChildrenByPId(tree,list);

parentTree.getChildren().add(tree);

}

});

return parentTree;

}

}

Controller层@RestController

@RequestMapping("/tree")

public class TreeController{

@Autowired

private TreeService treeService;

@GetMapping("/list")

public ResponseEntity> selectTreeList(){

return ResponseEntity.ok(treeService.findTreeList());

}

}

调试