获取树形结构的几种方式

获取树形结构的几种方式

一、存储过程实现
start with … connect by prior … 语句
示例

select connect_by_root(child_col) root, level ,
decode(connect_by_isleaf,0,'No',1,'Yes') is_leaf, sys_connect_by_path(child_col,'/') path  
from tree  
start with parent_col is null connect by prior child_col=parent_col

;
二、mybatis
xml文件实现(实际也是迭代的一种体现)

<resultMap type="com.test.mybatis.model.RoleInfo" id="roleModel">
      <id column="id" property="roleid"/>
    <result column="name" property="rolename"/>
    <collection property="menulist" select="getMenu" column="id">
        <!--当需要传递多个参数时使用{}-->
     </collection>
 </resultMap>
 
 <select id="getRoleInfo" resultMap="roleModel">
    select id,name from role
 </select>
 <select id="getMenu" resultType="com.test.mybatis.model.Menu">
     select m.id,m.name 
              from menu m join roleandmenu ram on m.id=ram.menuId
     where ram.roleId=#{id}
 </select>

java 实体

  import java.util.List;
 
 @Data
  public class RoleInfo {
     
    private Integer roleid;
    private String rolename;
    private List<Menu> menulist;
    }

三、迭代

1.先查出顶层，迭代查询子层

public List<TreeListDto> getInterfaceTree(List<TreeListDto> tldList, String interfaceId) {
	List<TreeListDto> tlds = new ArrayList<TreeListDto>();
	for (TreeListDto tld1 : tldList) {
		if (MyStringUtil.isEquals(interfaceId, tld1.getParent())) {
			String id = tld1.getKey();
			List<TreeListDto> tmpTree = this.getInterfaceTree(tldList, id);
			tld1.setChildren(tmpTree);
			if (tmpTree.size() <= 0) {
				tld1.setIsLeaf(true);
			}
			tlds.add(tld1);
		}

	}
	return tlds;
}