二、动态面板
- 数据库设计
- 函数设计：该函数用于获取某个节点的所有子节点
CREATE FUNCTION fn_getAddress_ChildList_test(rootId INT) RETURNS varchar(1000) CHARSET utf8 #rootId为你要查询的节点
BEGIN
#声明两个临时变量
DECLARE temp VARCHAR(1000);
DECLARE tempChd VARCHAR(1000);
SET temp = '0';
SET tempChd=CAST(rootId AS CHAR);#把rootId强制转换为字符
WHILE tempChd is not null DO
SET temp = CONCAT(temp,',',tempChd);#循环把所有节点连接成字符串。
SELECT GROUP_CONCAT(menu_id) INTO tempChd FROM menu where FIND_IN_SET(parent_id,tempChd)>0;
END WHILE;
RETURN temp;
END
- index.jsp
<a href="<%=path%>/servlet/getAccordion">跳转到动态的折叠面板</a>
-
/servlet/getAccordion 这个servlet
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("UTF-8"); response.setCharacterEncoding("UTF-8"); Connection conn=null; String sql=""; try { QueryRunner queryRunner=new QueryRunner(); conn=DBUtil.getConn(); sql="select * from menu where grade=1"; List<Menu> menuList=queryRunner.query(conn, sql, new BeanListHandler<>(Menu.class)); request.setAttribute("menuList", menuList); } catch (Exception e) { e.printStackTrace(); } request.getRequestDispatcher("/accrodion.jsp").forward(request, response); }
-
accrodion.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <!DOCTYPE html > <html> <% String path = request.getContextPath(); %> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Insert title here</title> <link rel="stylesheet" type="text/css" href="<%=path%>/script/easyUI-1.4/themes/bootstrap/easyui.css"> <link rel="stylesheet" type="text/css" href="<%=path%>/script/easyUI-1.4/themes/icon.css"> <script type="text/javascript" src="<%=path%>/script/easyUI-1.4/jquery-1.8.3.min.js"></script> <script type="text/javascript" src="<%=path%>/script/easyUI-1.4/jquery.easyui.min.js"></script> <script type="text/javascript" src="<%=path%>/script/easyUI-1.4/locale/easyui-lang-zh_CN.js"></script> </head> <body class="easyui-layout"> <h2>2.动态的折叠面板</h2> <div id="aa" class="easyui-accordion" style=" 300px; height: 200px;" data-options="region:'west'"> <c:forEach var="menuBean" items="${requestScope.menuList}"> <div title="${menuBean.menu_name}"> <ul class="easyui-tree" data-options="url:'<%=path%>/servlet/getData?menu_id=${menuBean.menu_id}'"></ul> </div> </c:forEach> </div> </body> </html>
-
servlet/getData 这个servlet用于根据menuid来获取这个菜单的所有子菜单的数据
package servlet; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import com.google.gson.Gson; import bean.Menu; import bean.NodeBean; import util.DBUtil; /** * Servlet implementation class getDateServlet */ @WebServlet("/servlet/getData") public class GetDataServlet extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse * response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse * response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("UTF-8"); response.setCharacterEncoding("UTF-8"); response.setContentType("text/html"); PrintWriter out = response.getWriter(); String menu_id = request.getParameter("menu_id");
//该函数fn_getAddress_ChildList_test是数据库函数，文章开头已经定义，该函数用户获取menu_id的所有子节点，查询结果包括这个menu_id，所有grade>1 String sql = "select * from menu where grade>1 and FIND_IN_SET(menu_Id,fn_getAddress_ChildList_test("+menu_id+"))"; try { Connection conn = DBUtil.getConn(); QueryRunner queryRunner = new QueryRunner(); List<Map<String, Object>> treeList = new ArrayList<Map<String, Object>>(); List<Menu> menuList = queryRunner.query(conn, sql, new BeanListHandler<>(Menu.class)); Map<String, Map<String, Object>> id_nodeMap = new HashMap<String, Map<String, Object>>(); Map<String, Object> nodeMap = null; for (Menu menu : menuList) { nodeMap = new HashMap<String, Object>(); nodeMap.put("id", menu.getMenu_id()); nodeMap.put("text", menu.getMenu_name()); id_nodeMap.put(String.valueOf(menu.getMenu_id()), nodeMap); if (menu.getParent_id() == Integer.valueOf(menu_id)) { treeList.add(nodeMap); } else { Map<String, Object> parenMap = id_nodeMap.get(String.valueOf(menu.getParent_id())); if (parenMap != null) { List<Map<String, Object>> children = null; if (parenMap.get("children") == null) { // 说明该父节点当前还没有一个子节点 children = new ArrayList<Map<String, Object>>(); } else { children = (List<Map<String, Object>>) parenMap.get("children"); } children.add(nodeMap); parenMap.put("children", children); } } } Gson gson = new Gson(); String json_res = gson.toJson(treeList); out.print(json_res); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { } }
结果：
