Oracle and PostgreSQL recursive query

I believe that you will often encounter this kind of requirement. You can use the id/name of a staff member to obtain a list of all its employees (including non-subordinate employees, it may take a while to implement this function using java, but it would be so easy if you rely on databases to implement this function. Let's take a look at how Postgresql implements such a feature.

1. With recursive r AS (SELECT * FROM t_account WHEREname =#{ accountName}
2. UnionALL
3. SELECT t_account. * FROM t_account, r WHERE t_account.parent = r. name
4. )
5. SELECT * FROM r ORDERBYname

This is not in the amount of code to reduce a lot of ah, see the postgresql official documentation http://www.postgresql.org/docs/8.4/static/queries-with.html
Next let's take a look at how Oracle performs recursive queries:

Traverse from ROOT to end:

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

Traverse from the end to the ROOT end: select * from t_account t start with t. name = '** 'connect by t. parent = t. name

For more information, see the oracle documentation.

See the java Implementation below:

1. Publicclass JsonTreeGenerate <T extends AbstractTreeNode> {
2. Private Logger logger = Logger. getLogger (JsonTreeGenerate. class );
3. Private Lock lock = new ReentrantLock ();
4. Private Set <T> set = new HashSet <T> ();
6. Public Set <T> getAllChild (Set <T> sets, T node ){
7. Lock. lock ();
8. Try {
9. If (set. size ()> 0 ){
10. Set. clear ();
11. }
12. RecursionFn (sets, node );
13. } Catch (Exception e ){
14. Logger. error ("", e );
15. } Finally {
16. Lock. unlock ();
17. }
18. Return set;
19. }
20. Publicvoid recursionFn (Set <T> sets, T node ){
21. Set. add (node );
22. If (hasChild (sets, node )){
23. List <T> hashSet = getChildList (sets, node );
24. Iterator <T> it = hashSet. iterator ();
25. While (it. hasNext ()){
26. T n = (T) it. next ();
27. If (null = node. getChildren ()){
28. Node. setChildren (new ArrayList <AbstractTreeNode> ());
29. }
30. Node. getChildren (). add (n );
31. RecursionFn (sets, n );
32. }
33. // RecursionFn (accountSet, node );
34. }
35. }
38. Public List <T> getChildList (Set <T> list, T t ){
39. List <T> nodeList = new ArrayList <T> ();
40. Iterator <T> it = list. iterator ();
41. While (it. hasNext ()){
42. T accounts = it. next ();
43. If (accounts. getParent () = t. getId ()){
44. NodeList. add (accounts );
45. // T. getChildren (). add (accounts );
46. }
47. }
48. Return nodeList;
49. }
50. Publicboolean hasChild (Set <T> list, T node ){
51. List <T> l = getChildList (list, node );
52. If (null! = L & l. size ()> 0 ){
53. Returntrue;
54. }
55. Returnfalse;
56. }
57. }

This comparison shows the conciseness of the former to solve this problem.