mybatis递归查询

最近在写一个小程序，需要实现业务无限极分类，就是业务下有无限下拉子菜单，需要后台返回包括子节点的所有的数据

数据库表结构如下：

business表

项目代码：

1、编写business实体类

import java.util.Date;
import java.util.List;

//业务类实体类
public class Business  {
    private Integer id;
    //业务名
    private String businessName;
    //父id
    private Integer parentId;
    //部门ID
    private Integer departmentId;
    //类别（个人或者企业）
    private Integer isPresonal;
    //标题
    private String title;
    //需要携带的资料
    private String data;
    //资料文档地址
    private String fileUrl;
    //逻辑删除，1表示删除，0表示不删除，默认为0
    private Integer isDelete;
    //创建时间
    private Date createTime;
    //更新时间
    private Date updateTime;

    List<Business> businesses;

    public Integer getId() {
        return id;
    }

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

    public Integer getParentId() {
        return parentId;
    }

    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    public Integer getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentId(Integer departmentId) {
        this.departmentId = departmentId;
    }

    public Integer getIsPresonal() {
        return isPresonal;
    }

    public void setIsPresonal(Integer isPresonal) {
        this.isPresonal = isPresonal;
    }

    public String getBusinessName() {
        return businessName;
    }

    public void setBusinessName(String businessName) {
        this.businessName = businessName;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getData() {
        return data;
    }

    public void setData(String data) {
        this.data = data;
    }

    public String getFileUrl() {
        return fileUrl;
    }

    public void setFileUrl(String fileUrl) {
        this.fileUrl = fileUrl;
    }
    public Integer getIsDelete() {
        return isDelete;
    }

    public void setIsDelete(Integer isDelete) {
        this.isDelete = isDelete;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public List<Business> getBusinesses() {
        return businesses;
    }

    public void setBusinesses(List<Business> businesses) {
        this.businesses = businesses;
    }
}

2、businessMapper

@Mapper
@Repository
public interface BusinessMapper {
    List<Business> getBusiness();

    List<Business> getBusinessById(@Param("pid") Integer pid);
}

3、controller

@RestController
public class BusinessController {
    @Autowired
    BusinessMapper businessMapper;
   
   //根据部门id返回属于该部门的所有业务
   @RequestMapping("/getBusinessById")
   public R getBusinessById(Integer id)
   {
       List<Business> list=businessMapper.getBusinessById(0);
       ArrayList<Business> businessesArrayList = new ArrayList<>();
       for (int i=0;i<list.size();i++)
       {
           if(list.get(i).getDepartmentId()==id)
           {
               businessesArrayList.add(list.get(i));
           }
       }
       return R.ok().put("business",businessesArrayList);
   }

4、businessMapper.xml

<?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="com.boseidong.cfzxzspzx.mapper.BusinessMapper">
    <resultMap id="getSelf" type="com.boseidong.cfzxzspzx.entity.Business">
        <id column="id" property="id"/>
        <result column="parent_id" property="parentId"/>
        <result column="department_id" property="departmentId"/>
        <result column="is_presonal" property="isPresonal"/>
        <result column="business_name" property="businessName"/>
        <result column="title" property="title"/>
        <result column="data" property="data"/>
        <result column="is_print" property="isPrint"/>
        <result column="is_delete" property="isDelete"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <collection property="businesses" select="getBusinessById"
                    column="id">
        </collection>
        <!--查到的cid作为下次的pid -->
    </resultMap>
    <select id="getBusinessById" resultMap="getSelf">
        select * from business b1
        where b1.parent_id=#{pid}
    </select>
</mapper>

注意：

1.collection 的column与id的column相同，property为实体类中子集合的名字，select与查询方法名字相同

2.查询时一定要将id和parentId都查出来，否则mybaits无法完成递归，我用*查就更没问题了。实体类中也要有父id那个属性

3、以上实现至针对传入参数为一个值的时候，parentId传进去的值要是最顶级的，这里就是0
————————————————
 

原文链接：https://blog.csdn.net/lianzhang861/article/details/86243532