Tuesday, November 8, 2011

JSP/JSTL如何做動態樹狀結構目錄

可以開無限層的資料夾目錄:(超酷)


資料庫的規格:
   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: );
table的開法,只要想一個概念,就會很清楚了,公司的人員的表格,會有兩個欄位是:員工姓名、員工的上一層主管;主管本身也是員工,也會有上一層主管
沒有上一層主管的員工,就是老闆,所以,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>
一般應該是不用給到完整路徑,那因為是用portal ,都會有些其怪的狀況,
這邊最酷的地方是 <jsp:include page=”自已這支jsp” />造成遞迴,這樣就會有無限層的資料夾目錄了

JSP recursion參考資料:http://blog.boyandi.net/2007/11/21/jsp-recursion/