資料庫的規格:
1: CREATE TABLE folder (
2: id INT PRIMARY KEY,
3: folder_name NVARCHAR(128) NOT NULL, --資料夾名稱
4: root_id INT, --根目錄的id,記錄最上層根目錄的id
5: depth INT, --位在資料夾的第幾層,從1開始;根目錄是第一層
6: parent_id INT REFERENCES folder (id) --父目錄的id
7: );
沒有上一層主管的員工,就是老闆,所以,parentId 就給 null ( 即根目錄 )
新增資料夾時,要捉跟它有關連的 parent 的 root_id 跟 depth+1
查詢的SQL:
1: String sql = String.format(
2: " SELECT * " +
3: " FROM %1$s " +
4: " WHERE id = %2$d OR parent_id IS NULL " +
5: " OR parent_id = %2$d " +
6: " OR id IN ( SELECT p.id " +
7: " FROM %1$s t JOIN %1$s p " +
8: " ON t.root_id = p.root_id " +
9: " WHERE t.id = %2$d AND t.depth >= p.depth)" +
10: " ORDER BY depth, parent_id, id, folder_name",
11: FOLDER, id);
POJO (BEAN):
1: public class Folder {
2:
3: //資料夾id
4: protected int id;
5:
6: //資料夾名稱
7: protected String folderName;
8:
9: //父資料夾的id,null代表根目錄
10: protected int parentId;
11:
12: //根目錄的id
13: protected int rootId;
14:
15: //目錄的第幾層,根目錄為第一層
16: protected int depth;
17:
18: //子目錄
19: protected List<Folder> subFolders = new ArrayList<Folder>();
20:
21: //父目錄
22: protected Folder parent;
23:
24:
25: setter ...
26: getter...
27: }
邏輯層的寫法:處理樹狀目錄的順序
1: private List<Folder> linkNodes(List<Folder> orderedSource) {
2: // 最上層的目錄當作結果傳回
3: List<Folder> roots = new ArrayList<Folder>();
4:
5: // 每個人都有機會當父母,不過要排隊
6: Queue<Folder> parents = new LinkedList<Folder>();
7:
8: for (Folder f : orderedSource) {
9: if (f.getParentId() > 0) {
10: // 爸爸勒?媽媽勒?
11: // 父母排最前面的是不是他的爸媽
12: while (parents.element().getId() != f.getParentId()) {
13: // 不是的話,叫他閃邊,因為原始資料有排續,確定已經沒用了
14: parents.remove();
15: }
16:
17: // 找到了!
18: // 我的父母是
19: f.setParent(parents.element());
20:
21: // 我也是她們的小孩
22: // 記得要在Folder裡把 subFolders 初始化 = new List<Folder>();
23: // 不然會有NPE (NullPointException)
24: parents.element().getSubFolders().add(f);
25: }
26: else {
27: roots.add(f);
28: }
29:
30: // 可以當準父母了
31: parents.add(f);
32: }
33: return roots;
34: }
畫面的寫法:
1: <%@ page language="java" contentType="text/html;charset=UTF-8"%>
2: <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
3: <%@ taglib prefix="portlet" uri="http://java.sun.com/portlet" %>
4:
5: <ul style="padding:0">
6:
7: <c:forEach var="tree" items="${treeList}">
8:
9: <li style="list-style:none">
10: <a href="<portlet:actionURL>
11: <portlet:param name="id" value="${tree.id}"/>
12: </portlet:actionURL>">
13: <c:out value="${tree.folderName}" />
14: </a>
15: </li>
16:
17: <c:set var="treeList"
18: value="${tree.subFolders}"
19: scope="request"/>
20: <jsp:include page="/完整路徑/FolderTree.jsp" />
21: </c:forEach>
22: </ul>
這邊最酷的地方是 <jsp:include page=”自已這支jsp” />造成遞迴,這樣就會有無限層的資料夾目錄了
JSP recursion參考資料:http://blog.boyandi.net/2007/11/21/jsp-recursion/