近期工作上遇到了这样一个需求,一张表用来存储树形结构的父节点和叶子节点,大体是这样定义的。
Table定义如下:
id | groupid | nodeid | other |
1 | A001 | A0001 | ~~ |
2 | B001 | B0001 | ~~ |
3 | A0001 | A00001 | ~~ |
4 | C001 | C0001 | ~~ |
5 | C001 | C0002 | ~~ |
需求很简单:这张表表示的是一个数结构,其中groupid代表的是父节点,而nodeid代表的是相应的子节点,我们想要的是整个树结构的全部叶子节点。当然涉及到多棵树,就组成了森林结构,也就是查找指定森林结构的所有叶子节点。
思路也很Easy:遍历每一颗树,利用写好的递归查询函数去查询每棵树的最终叶子结点,然后把它们保存到静态list中。
详细代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
package org.uugu.test; import java.util.ArrayList; import java.util.List; import java.sql.*; public class FindLeaf { /** * @param args */ private static List<String> leaf = new ArrayList<String>(); public static void main(String[] args) { // TODO Auto-generated method stub FindLeaf test = new FindLeaf(); List<String> list = new ArrayList<String>(); list.add("A001"); list.add("B001"); for (int i = 0; i < list.size(); i++) { try { test.search(list.get(i).toString()); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } System.out.println(leaf); /* * 递归函数 * */ public List<String> search(String args) throws Exception { Connection con = createConnection(); List<String> tmplist = new ArrayList<String>(); /* * 构建SQL */ String sql = "select nodeid from setnode where groupid = '" + args+ "'"; /* * 判断是否为叶子节点 * */ String sql2 = "select groupid from setnode where groupid = '" + args+ "'"; //PreparedStatement ps = con.prepareStatement(sql2); ResultSet rs2 = con.prepareStatement(sql2).executeQuery(); List<String> list2 = rs2list(rs2); if (list2.size() == 0) { con.close(); System.out.println("最终叶子节点是:" + args); leaf.add(args); } else { ResultSet rs = con.prepareStatement(sql).executeQuery(); tmplist = rs2list(rs); for (int i = 0; i < tmplist.size(); i++) { search(tmplist.get(i).toString()); //递归循环查询 } } if(con!=null){ con.close(); } return leaf; } public Connection createConnection() { Connection con = null; String url = "jdbc:postgresql://localhost:5432/postgres"; String user = "postgres"; String pass = "123"; try { Class.forName("org.postgresql.Driver"); con = DriverManager.getConnection(url, user, pass); // System.out.println("Connection successfully established!"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return con; } private static List<String> rs2list(ResultSet rs) throws SQLException { //将结果集ResultSet转换为list // TODO Auto-generated method stub List<String> list = new ArrayList<String>(); while (rs.next()) { list.add(rs.getString(1).trim()); } // System.out.println(list); return list; } } |
指定结果为:leaf[‘A00001′,’B0001’]
工作经验就是这样一点一滴总结起来的,怪不得现如今招聘都要工作经验了。