oracle及postgresql递归查询

相信大家经常会遇到这样的需求，通过一位职员的id/name获取其下属（包括非直属）所有员工列表，用java实现这个功能相信也得花上一会功夫，但是如果是依赖数据库来实现这个功能那就so easy了。先来看看Postgresql如何实现这样的功能

WITH RECURSIVE r AS (SELECT * FROM t_account WHERE name = #{accountName}            union ALL           SELECT t_account.* FROM t_account, r WHERE t_account.parent = r.name         )    SELECT * FROM r ORDER BY name

这样是不是在代码量上减轻了很多啊，具体见postgresql官方文档PostgreSQL: Documentation: 8.4: WITH Queries (Common Table Expressions)
接着来看看oracle如何做递归查询：
从ROOT往末端遍历：

select * from t_account t start with t.parent is null connect by prior t.name=t.parent

从末端到ROOT端遍历：select * from t_account t start with t.name=’**’ connect by t.parent=t.name
具体用法细节请参考oracle文档
下面再参照java的实现：

public class JsonTreeGenerate<T extends AbstractTreeNode> { private Logger logger = Logger.getLogger(JsonTreeGenerate.class); private Lock lock = new ReentrantLock(); private Set<T> set = new HashSet<T>();  public Set<T> getAllChild(Set<T> sets,T node){  lock.lock();  try {   if(set.size()>0){    set.clear();   }   recursionFn(sets,node);  } catch (Exception e) {   logger.error("", e);  }finally{   lock.unlock();  }  return set; } public void recursionFn(Set<T> sets , T node){  set.add(node);  if(hasChild(sets,node)){         List<T> hashSet = getChildList(sets , node);         Iterator<T> it = hashSet.iterator();         while(it.hasNext()){              T n = (T)it.next();          if(null==node.getChildren()){           node.setChildren(new ArrayList<AbstractTreeNode>());          }          node.getChildren().add(n);                recursionFn(sets,n);                }          //recursionFn(accountSet,node);        }     }   public List<T> getChildList(Set<T> list, T t){  List<T> nodeList=new ArrayList<T>();  Iterator<T> it = list.iterator();            while(it.hasNext()){             T accounts = it.next();                if(accounts.getParent()==t.getId()){                 nodeList.add(accounts);             //t.getChildren().add(accounts);            }            }            return nodeList;   } public boolean hasChild(Set<T> list,T node){  List<T> l =getChildList(list,node);  if(null!=l&&l.size()>0){   return true;  }  return false; }}

这个一比较就知道前者处理该问题的简洁性了吧

?