树型结构递归查询

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

@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;
}
}