数据库表设计中涉及的递归查询解决思路

89 阅读 0 评论 59 点赞

我是靠谱客的博主安静店员，最近开发中收集的这篇文章主要介绍数据库表设计中涉及的递归查询解决思路，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1、数据表的设计

CREATE TABLE work_query_dict
(
   id int primary key NOT NULL COMMENT '编号',
   title varchar(100) NOT NULL COMMENT '标签名',
   parent_id int DEFAULT 0 COMMENT '父级编号',
   create_user varchar(64) COMMENT '创建者',
   create_time timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   update_user varchar(64) COMMENT '更新者',
   update_time timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
   del_flag int DEFAULT 0 NOT NULL COMMENT '删除标记'
)

2、控制层

/**
* 查询工单查询的字典列表
* @param
* @return
*/
@RequestMapping("/findWorkQueryDictList")
@ResponseBody
public RestfulResponse findDictList(@RequestParam Map<String,Object> params, HttpServletRequest request){
int id = Utils.parseInt(request.getParameter("id"),0);
List<Map<String, Object>> dictList= workQueryDictService.findDictList();
return RestfulResponse.success(dictList);
}

3、业务层

 @Override
public List<Map<String, Object>> findDictList() {
List<WorkQueryDict> treeMenu = workQueryDictDao.findWorkQueryDictByPid(0);
List<Map<String, Object>> list = new ArrayList<>();
if (treeMenu.size() > 0) {
for (WorkQueryDict dict : treeMenu) {
Map<String, Object> map = new LinkedHashMap<>();
map.put("id", dict.getId());
map.put("title", dict.getTitle());
map.put("children", getChildren(dict.getId()));
list.add(map);
}
}
return list;
}
/**
* 获取孩子节点
* @param id
* @return
*/
private List<Object> getChildren(int id) {
List<Object> list = new ArrayList<>();
List<WorkQueryDict> treeMenu = workQueryDictDao.findWorkQueryDictByPid(id);
for (WorkQueryDict dict : treeMenu) {
Map<String, Object> map = new LinkedHashMap<>();
map.put("id", dict.getId());
map.put("title", dict.getTitle());
map.put("children", getChildren(dict.getId()));
list.add(map);
}
return list;
}

4、dao层

<resultMap id="workQueryDictMapping" type="csg.ios.zzfw.entity.WorkQueryDict">
<result column="id" property="id"/>
<result column="title" property="title"/>
<result column="parent_id" property="parentId"/>
<result column="create_user" property="createUser"/>
<result column="create_time" property="createTime"/>
<result column="update_user" property="updateUser"/>
<result column="update_time" property="updateTime"/>
<result column="del_flag" property="delFlag"/>
</resultMap>
<select id="findWorkQueryDictByPid" parameterType="csg.ios.zzfw.entity.WorkQueryDict" resultMap="workQueryDictMapping">
select id,title,parent_id,create_user,create_time,update_user,update_time from
work_query_dict
<where>
parent_id = #{id}
</where>
</select>
</mapper>

5、展示的查询数据的结构：

一个树形结构