递归查询

1.返回所有子类id

@RequestMapping(value="/test", method = RequestMethod.GET)
public CommonResultVo test(HttpServletRequest req, Model model) throws ServiceException{
	CommonResultVo result = new CommonResultVo();
	List<String> list = new ArrayList<String>();
	list = getChildList("10BM", list);
	System.out.println(list.size());
	result.setData(list);
	return result;
	
}
private List<String> getChildList(String agentCom,List<String> list) throws ServiceException {
	List<String> agentComList= lacomService.findChildAgentList(agentCom);
	if(CommonUtil.hasValue(agentComList)) {
		for (String child : agentComList) {
			list.add(child);
			getChildList(child,list);
		}
	}
	return list;
}

2.返回分类层级关系(树结构)

@RequestMapping(value = "/getCategoryTree", method = RequestMethod.POST)
@ResponseBody
public List<TreeNode> getCategoryTree(HttpServletRequest req, HttpServletResponse rep, Model model) throws ManagerException {
    Map<String, Object> params = this.builderParams(req, model);
    String conditon = " and level_code= 0 or level_code=1";
    params.put("queryCondition", conditon);
    List<Category> categoryList = categoryManager.findByPage(null, null, null, params);
    List<TreeNode> rootTreeNode = new ArrayList<TreeNode>();
    for (Category category : categoryList) {
        TreeNode treeNode = new TreeNode();
        treeNode.setId(category.getCategoryNo());
        treeNode.setText(category.getCategoryName());
        treeNode.setParentNo(category.getParentNo());
        treeNode.setChecked(false);
        treeNode.setCategoryId(category.getId());
        treeNode.setLevelCode(category.getLevelCode() + "");
        treeNode.setKeyWords(category.getKeywords());
        treeNode.setDirectType(category.getDirectType() + "");
        rootTreeNode.add(treeNode);
    }
    List<TreeNode> menuList = new ArrayList<TreeNode>();//主菜单
    // 先找到所有的一级菜单
    for (int i = 0; i < rootTreeNode.size(); i++) {
        // 一级菜单没有ParentNo
        //if (StringUtils.isBlank(rootTreeNode.get(i).getParentNo())) {
    	if (rootTreeNode.get(i).getLevelCode().equals("0")) { // 一级类别的判断需要使用levelCode 字段
            menuList.add(rootTreeNode.get(i));
        }
    }
    // 为一级菜单设置子菜单，getChild是递归调用的
    for (TreeNode treeNode : menuList/*主菜单*/) {
        treeNode.setChildren(getChild(treeNode.getId(), rootTreeNode/*所有的实体类*/));
    }
    Map<String, Object> jsonMap = new HashMap<String, Object>();
    jsonMap.put("menu", menuList);
    return menuList;
}

private List<TreeNode> getChild(String id/*当前主菜单编码*/, List<TreeNode> rootMenu/*所有的实体类*/) {
    // 子菜单
    List<TreeNode> childList = new ArrayList<TreeNode>();
    for (TreeNode treeNode : rootMenu) {
        // 遍历所有节点，将父菜单id与传过来的id比较
        if (StringUtils.isNotBlank(treeNode.getParentNo())) {
            if (treeNode.getParentNo().equals(id)) {
                childList.add(treeNode);
            }
        }
    }
    // 把子菜单的子菜单再循环一遍
    for (TreeNode treeNode : childList) {// 没有url子菜单还有子菜单
        // 递归
        treeNode.setChildren(getChild(treeNode.getId(), rootMenu));
    }
    // 递归退出条件
    if (childList.size() == 0) {
        return null;
    }
    return childList;
}

3.递归查询sql(oracle)

select * from lacom l start with l.agentcom = ‘10BM’ CONNECT by PRIOR l.agentcom = l.upagentcom

(mysql)

CREATE FUNCTION getChildList(rootId INT)
RETURNS varchar(1000)

BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);

SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);

WHILE sTempChd is not null DO
    SET sTemp = concat(sTemp,',',sTempChd);
    SELECT group_concat(id) INTO sTempChd FROM  treeNodes where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
RETURN sTemp; 

END

看我下面的截图标记的序号分别要注意的要点，

1：你创建的表的主键id，

2：你创建的表名，

3：你创建的表的表示上级的字段，

4：这里可以改可以不改，因为按照mysql这样的情况，如果你的数据库可能将来有多张表会用到递归查询的话，这里最好换个名字，比如getchildListTablename，tablename可以换成你2里面的表名，当然小编这里只是建议。