java递归构建树形结构SQL查询，可通用

最近做项目要查出所有的产品类型，并且按照一级二姐三级目录的形式以json字符串的形式返回给前端，上网搜了不少，有在数据库中直接查询的，数据库中有树形查询的方法格式，在此不多做介绍，如果数据量过大，可能导致查询效率降低 另一种是在程序内部做处理，话不多说，直接上代码：

mapper

，可以根据自己的需求去查，我因为是需要查询全部产品类型，就都查了

@Mapper public interface TypeTreeMapper extends BaseMapper<TypeTree> { List<TypeTreeVo> showTypeTree(); }

mapper.xml

<!-- 通用查询映射结果 --> <resultMap id="BaseResultMap" type="com.lookherbs.lookherbscloud.provider.lookherbscloudproviderdmc.common.product.TypeTreeVo"> <id column="type_id" property="typeId" /> <result column="type_name" property="typeName" /> <result column="ename" property="ename" /> <result column="lname" property="lname" /> <result column="ftype_id" property="ftypeId" /> <result column="rtype_id" property="rtypeId" /> <result column="type_level" property="typeLevel" /> </resultMap> <!-- 根据父键查询 --> <select id="showTypeTree" resultMap="BaseResultMap"> select * from t_type_tree </select>

主要的处理逻辑是在service层

service

第一级的父id为0就不多说了，二级三级四级的父id分别就是相对应的上一级的id，用递归去获取查询出来的数据，实体类中创建一个List集合，存放相对应的子集目录下的所有内容，对于lambda有点陌生的可以看我的上一篇博客

@Override public List<TypeTreeVo> showTypeTree() { List<TypeTreeVo> nodeList = treeMapper.showTypeTree(); List<TypeTreeVo> all=nodeList.stream().filter(t-> t.getFtypeId()!=null && t.getFtypeId()==0 ).map((t)->{ t.setChildren(getFtype(t,nodeList)); return t; }).collect(Collectors.toList()); return all; } private List<TypeTreeVo> getFtype(TypeTreeVo root,List<TypeTreeVo> all){ List<TypeTreeVo> children=all.stream().filter(t -> { return Objects.equals(t.getFtypeId(),root.getTypeId()); }).map((t)->{ t.setChildren(getFtype(t,all)); return t; }).collect(Collectors.toList()); return children; }

controller没啥操作，直接返回就行

/** * 展示产品类型 * @return */ @RequestMapping("/show") public Wrapper showTypeTree(){ return handleResult(treeService.showTypeTree()); }

实体类的VO

@Data public class TypeTreeVo implements Serializable { private static final long serialVersionUID = 1L; //类型id private Integer typeId; private String typeName; private String ename; private String lname; //父类型id private Integer ftypeId; //根类型id private Integer rtypeId; private Integer typeLevel; List<TypeTreeVo> children; }

看效果

{ "code": 200, "message": "操作成功", "result": [ { "typeId": 1, "typeName": "植物原料", "ename": "Plant Materials", "lname": "zwyl", "ftypeId": 0, "rtypeId": 0, "typeLevel": 1, "children": [ { "typeId": 2, "typeName": "根", "ename": "gen", "lname": "g", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] }, { "typeId": 3, "typeName": "茎", "ename": "jin", "lname": "j", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] }, { "typeId": 4, "typeName": "叶", "ename": "ye", "lname": "y", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] }, { "typeId": 5, "typeName": "花", "ename": "hua", "lname": "h", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] }, { "typeId": 6, "typeName": "果实", "ename": "guoshi", "lname": "gs", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] }, { "typeId": 7, "typeName": "种子", "ename": "zhognzi", "lname": "zz", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] }, { "typeId": 8, "typeName": "树皮", "ename": "shupi", "lname": "sp", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] }, { "typeId": 9, "typeName": "全草", "ename": "quancao", "lname": "qc", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] }, { "typeId": 10, "typeName": "地上部分", "ename": "dishangbufen", "lname": "dsbf", "ftypeId": 1, "rtypeId": 1, "typeLevel": 2, "children": [] } ] }, { "typeId": 11, "typeName": "提取物", "ename": "Extracts", "lname": "tqw", "ftypeId": 0, "rtypeId": 0, "typeLevel": 1, "children": [ { "typeId": 12, "typeName": "标准提取物", "ename": "biaozhuntiquwu", "lname": "bztqw", "ftypeId": 11, "rtypeId": 11, "typeLevel": 2, "children": [] }, { "typeId": 13, "typeName": "比例提取物", "ename": "bilitiquwu", "lname": "bltqw", "ftypeId": 11, "rtypeId": 11, "typeLevel": 2, "children": [] }, { "typeId": 14, "typeName": "有效成分单体", "ename": "youxiaochengfendanti", "lname": "yxcfdt", "ftypeId": 11, "rtypeId": 11, "typeLevel": 2, "children": [] }, { "typeId": 15, "typeName": "医药中间体", "ename": "yiyaozhongjianti", "lname": "yyzjt", "ftypeId": 11, "rtypeId": 11, "typeLevel": 2, "children": [] } ] }, { "typeId": 16, "typeName": "原料药", "ename": "yuanliaoyao", "lname": "yly", "ftypeId": 0, "rtypeId": 0, "typeLevel": 1, "children": [ { "typeId": 30, "typeName": "原料药", "ename": "yuanliaoyao", "lname": "yly", "ftypeId": 16, "rtypeId": 16, "typeLevel": 2, "children": [] } ] }, { "typeId": 17, "typeName": "实验室用品", "ename": "Laboratory Supplies", "lname": "sysyp", "ftypeId": 0, "rtypeId": 0, "typeLevel": 1, "children": [ { "typeId": 18, "typeName": "检测仪器设备", "ename": "yiqishebei", "lname": "sqsb", "ftypeId": 17, "rtypeId": 17, "typeLevel": 2, "children": [] }, { "typeId": 19, "typeName": "试剂耗材", "ename": "shijihaocai", "lname": "sjhc", "ftypeId": 17, "rtypeId": 17, "typeLevel": 2, "children": [] }, { "typeId": 20, "typeName": "维护保养", "ename": "weihubaoyao", "lname": "whby", "ftypeId": 17, "rtypeId": 17, "typeLevel": 2, "children": [] } ] }, { "typeId": 21, "typeName": "生产相关", "ename": "Production Related", "lname": "scsb", "ftypeId": 0, "rtypeId": 0, "typeLevel": 1, "children": [ { "typeId": 22, "typeName": "生产设备", "ename": "shengchanshebei", "lname": "scxg", "ftypeId": 21, "rtypeId": 21, "typeLevel": 2, "children": [] }, { "typeId": 23, "typeName": "配件", "ename": "peijian", "lname": "pj", "ftypeId": 21, "rtypeId": 21, "typeLevel": 2, "children": [] }, { "typeId": 24, "typeName": "生产技术转让", "ename": "shengwujishuzhuangrang", "lname": "scjszr", "ftypeId": 21, "rtypeId": 21, "typeLevel": 2, "children": [] }, { "typeId": 25, "typeName": "整体解决方案", "ename": "zhengtifanganjiejue", "lname": "ztfajj", "ftypeId": 21, "rtypeId": 21, "typeLevel": 2, "children": [] } ] }, { "typeId": 26, "typeName": "循环利用", "ename": "Cyclic Uutilization", "lname": "xhly", "ftypeId": 0, "rtypeId": 0, "typeLevel": 1, "children": [ { "typeId": 27, "typeName": "水提废弃物", "ename": "shuitifeiqiwu", "lname": "stqufw", "ftypeId": 26, "rtypeId": 26, "typeLevel": 2, "children": [] }, { "typeId": 28, "typeName": "醇提废弃物", "ename": "chutifeiqiwu", "lname": "cttqfw", "ftypeId": 26, "rtypeId": 26, "typeLevel": 2, "children": [] }, { "typeId": 29, "typeName": "其它废料", "ename": "qitafeiliao", "lname": "qtgfl", "ftypeId": 26, "rtypeId": 26, "typeLevel": 2, "children": [] } ] } ] }

感言

mappermapper.xmlservicecontroller没啥操作，直接返回就行实体类的VO看效果

自己各处请教和查询资料才鼓捣出来的东西，难登大雅之堂，不足之处请来喷，哈哈