相信大家经常会遇到这样的需求，通过一位职员的id/name获取其下属（包括非直属）所有员工列表，用java实现这个功能相信也得花上一会功夫，但是如果是依赖数据库来实现这个功能那就so easy了。先来看看Postgresql如何实现这样的功能。
WITH RECURSIVE r AS (SELECT * FROM t_account WHEREname = #{accountName}
unionALL
SELECT t_account.* FROM t_account, r WHERE t_account.parent = r.name
)
SELECT * FROM r ORDERBYname
这样是不是在代码量上减轻了很多啊，具体见postgresql官方文档http://www.postgresql.org/docs/8.4/static/queries-with.html
接着来看看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的实现：
publicclass JsonTreeGenerate {
private Logger logger = Logger.getLogger(JsonTreeGenerate.class);
private Lock lock = new ReentrantLock();
private Set set = new HashSet();
public Set getAllChild(Set 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;
}
publicvoid recursionFn(Set sets , T node){
set.add(node);
if(hasChild(sets,node)){
List hashSet = getChildList(sets , node);
Iterator it = hashSet.iterator();
while(it.hasNext()){
T n = (T)it.next();
if(null==node.getChildren()){
node.setChildren(new ArrayList());
}
node.getChildren().add(n);
recursionFn(sets,n);
}
//recursionFn(accountSet,node);
}
}
public List getChildList(Set list, T t){
List nodeList=new ArrayList();
Iterator it = list.iterator();
while(it.hasNext()){
T accounts = it.next();
if(accounts.getParent()==t.getId()){
nodeList.add(accounts);
//t.getChildren().add(accounts);
}
}
return nodeList;
}
publicboolean hasChild(Set list,T node){
List l =getChildList(list,node);
if(null!=l&&l.size()>0){
returntrue;
}
returnfalse;
}
}
这个一比较就知道前者处理该问题的简洁性了吧。
