树型结构递归查询

最新推荐文章于 2024-03-18 20:57:04 发布

原创最新推荐文章于 2024-03-18 20:57:04 发布 · 196 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#OS #F#

oracle 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种使用SQL进行数据库层级查询的方法，包括如何查询当前节点的所有子节点、查找根节点及根据根节点获取所有层级节点等操作。还展示了多表查询的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询当前节点的所有子字节:

@SuppressWarnings("unchecked")
public List<String> findAllChildColumnIdById(long id) throws Exception {

return em
.createNativeQuery(
"select to_char(i.id) FROM columns i start with i.id=:id connect by PRIOR i.id=i.f_parentId order by i.id asc")
.setParameter("id", id).getResultList();
}

当前节点找根节点
public String findRootIdByColumnId(long id) throws Exception {
return (String)em
.createNativeQuery(
"select to_char(i.id) FROM columns i where i.f_parentId=0 start with i.id=:id connect by PRIOR i.f_parentId=i.id ")
.setParameter("id", id).getSingleResult();
}

根据根节点找所有节点:

@SuppressWarnings("unchecked")
public List<InfoColumn> findAllChildColumnIdByRootId(long id) throws Exception {
List objects =em
.createNativeQuery(
"select i.id,i.f_name,i.f_parentId FROM columns i start with i.id=:id connect by PRIOR i.id=i.f_parentId order by i.id asc")
.setParameter("id", id).getResultList();
if(objects!=null&&objects.size()>0){

List<InfoColumn> infocs = new ArrayList<InfoColumn>();
for (Object o : objects) {
InfoColumn i = new InfoColumn();
Object[] os = (Object[]) o;
i.setId(Long.parseLong(os[0].toString()));
i.setName(os[1] != null ? os[1].toString() : "");
i.setParentId(Long.parseLong(os[2].toString()));
infocs.add(i);
}
return infocs;
}else{

return null;
}
}

多表查询:

@SuppressWarnings("unchecked")
public List<Info> findInfoByColumnIds(List<String> ids, int pageindex,
int max) throws Exception {

String QL = "select distinct o.id,o.title,o.path,o.updateDate FROM Info o,InfoColumn i where o in elements(i.infos) ";

StringBuffer temp = new StringBuffer();
if (ids != null && ids.size() > 0) {
for (String id : ids) {
temp.append(",");
temp.append(id);
}
}

List objects= em.createQuery(QL + " and i.id in("
+ temp.substring(1).toString()
+ ") and (o.state=2 or o.state=3) order by o.updateDate desc").setFirstResult(
pageindex).setMaxResults(max).getResultList();
if(objects!=null&&objects.size()>0){

List<Info> infos = new ArrayList<Info>();
for (Object o : objects) {
Info i = new Info();
Object[] os = (Object[]) o;
i.setId(Long.parseLong(os[0].toString()));
i.setTitle(os[1] != null ? os[1].toString() : "");
i.setPath(os[2] != null ? os[2].toString() : "");
infos.add(i);

}

return infos;
}else{

return null;
}
}