Servlet实战-菜单数据查询

Servlet实战-菜单数据查询

1.菜单表的设计

菜单表的创建表数据

drop table if exists t_menu;

/*==============================================================*/
/* Table: t_menu                                                */
/*==============================================================*/
create table t_menu
(
   menuID               varchar(50) not null comment '菜单id',
   isDelete             int comment '是否删除：1：删除 0：没删除',
   ipaddress            varchar(50) comment '操作者IP地址',
   addTime              datetime comment '添加时间',
   updateTime           datetime comment '修改时间',
   deleteTime           datetime comment '删除时间',
   menuCode             varchar(50) comment '菜单编码',
   m_name               varchar(50) comment '菜单名称',
   m_href               varchar(100) comment '链接地址',
   level                int comment '层级',
   innerCode            varchar(100) comment '内部编码',
   orders               int comment '排序',
   parentID             varchar(50) comment '父级id',
   primary key (menuID)
);

alter table t_menu comment '菜单信息表';

测试插入语句

INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('p1', 0, NULL, NULL, NULL, NULL, 'p1', '系统管理', NULL, 1, '00001', 1, '0');
INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('pp1', 0, NULL, NULL, NULL, NULL, 'pp1', '平台管理', '/planform', 2, '0000100001', 1, 'p1');
INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('pp2', 0, NULL, NULL, NULL, NULL, 'pp2', '账号管理', '/user', 2, '0000100002', 2, 'p1');
INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('pp3', 0, NULL, NULL, NULL, NULL, 'pp3', '角色管理', '/role', 2, '0000100003', 3, 'p1');
INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('pp4', 0, NULL, NULL, NULL, NULL, 'pp4', '菜单管理', '/menu', 2, '0000100004', 4, 'p1');
INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('pp5', 0, NULL, NULL, NULL, NULL, 'pp5', '字典管理', '/param', 2, '0000100005', 5, 'p1');
INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('p2', 0, NULL, NULL, NULL, NULL, 'p2', '学生管理', NULL, 1, '00002', 2, '0');
INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('p2p1', 0, NULL, NULL, NULL, NULL, 'p2p1', '班级管理', '/classMg', 2, '0000200001', 1, 'p2');
INSERT INTO `t_menu`(`menuID`, `isDelete`, `ipaddress`, `addTime`, `updateTime`, `deleteTime`, `menuCode`, `m_name`, `m_href`, `level`, `innerCode`, `orders`, `parentID`) VALUES ('p2p2', 0, NULL, NULL, NULL, NULL, 'p2p2', '学员管理', '/studentMg', 2, '0000200002', 2, 'p2');

2.编写查询菜单数据的Servlet

设计一个基类dto，存放每个dto类都有的公共字段

package com.huawei.javaservletdemo.sys.dto;

import java.io.Serializable;
import java.util.Date;

/**
 * DTO基类
 * 创建日期：2021/12/28
 * 作者：高靖博
 * 公司：华为（昆明）数字经济学院
 */
public class DtoBase implements Serializable {

    private int isDelete;
    private String ipaddress;
    private Date addTime;
    private Date updateTime;
    private Date deleteTime;

    public int getIsDelete() {
        return isDelete;
    }

    public void setIsDelete(int isDelete) {
        this.isDelete = isDelete;
    }

    public String getIpaddress() {
        return ipaddress;
    }

    public void setIpaddress(String ipaddress) {
        this.ipaddress = ipaddress;
    }

    public Date getAddTime() {
        return addTime;
    }

    public void setAddTime(Date addTime) {
        this.addTime = addTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Date getDeleteTime() {
        return deleteTime;
    }

    public void setDeleteTime(Date deleteTime) {
        this.deleteTime = deleteTime;
    }

}

（1）设计menu表对应的dto实体，继承基类dto

package com.huawei.javaservletdemo.sys.dto;

import com.alibaba.fastjson.JSON;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * 创建日期：2021/12/28
 * 作者：高靖博
 * 公司：华为（昆明）数字经济学院
 */
public class MenuDto extends DtoBase implements Serializable {

    private String menuID;
    private String menuCode;
    private String m_name;
    private String m_href;
    private int level;
    private String innerCode;
    private int orders;
    private String parentID;
    private List<MenuDto> children;

    public List<MenuDto> getChildren() {
        return children;
    }

    public void setChildren(List<MenuDto> children) {
        this.children = children;
    }

    public String getMenuID() {
        return menuID;
    }

    public void setMenuID(String menuID) {
        this.menuID = menuID;
    }



    public String getMenuCode() {
        return menuCode;
    }

    public void setMenuCode(String menuCode) {
        this.menuCode = menuCode;
    }

    public String getM_name() {
        return m_name;
    }

    public void setM_name(String m_name) {
        this.m_name = m_name;
    }

    public String getM_href() {
        return m_href;
    }

    public void setM_href(String m_href) {
        this.m_href = m_href;
    }

    public int getLevel() {
        return level;
    }

    public void setLevel(int level) {
        this.level = level;
    }

    public String getInnerCode() {
        return innerCode;
    }

    public void setInnerCode(String innerCode) {
        this.innerCode = innerCode;
    }

    public int getOrders() {
        return orders;
    }

    public void setOrders(int orders) {
        this.orders = orders;
    }

    public String getParentID() {
        return parentID;
    }

    public void setParentID(String parentID) {
        this.parentID = parentID;
    }

    @Override
    public String toString() {
        return JSON.toJSONString(this);
    }
}

(2) 编写Servlet内容

package com.huawei.javaservletdemo.sys.controller;

import com.huawei.javaservletdemo.sys.dto.MenuDto;
import com.huawei.javaservletdemo.sys.dto.Result;
import com.huawei.javaservletdemo.sys.service.MenuService;
import com.huawei.javaservletdemo.sys.service.impl.MenuServiceImpl;

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 java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

/**
 * 创建日期：2021/12/28
 * 作者：高靖博
 * 公司：华为（昆明）数字经济学院
 */
@WebServlet("/getMenuList")
public class GetMenuServlet extends HttpServlet {

    //创建Menu业务层接口实现对象
    MenuService menuService = new MenuServiceImpl();

    /**
     * 查询菜单数据
     * @param req
     * @param resp
     * @throws ServletException
     * @throws IOException
     */
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        req.setCharacterEncoding("UTF-8");
        resp.setCharacterEncoding("UTF-8");

        //返回前端的是Result类的json字符串
        Result res = new Result();

        //调用Service层接口-查询菜单数据
        try{
            List<MenuDto> listDto = menuService.getMenuList();//调用业务层，返回树形数据
            res.setData(listDto);
            res.setCode("0000");
            res.setMsg("查询成功！");
        }catch (Exception e){
            System.err.println("查询菜单数据异常！");
            res.setCode("9999");
            res.setMsg("查询菜单数据异常！");
        }


        //写出到客户端
        resp.setContentType("application/json;charset=UTF-8");
        PrintWriter writer = resp.getWriter();
        writer.print(res.toString());
        writer.flush();
        writer.close();

    }
}

（3）编写业务层接口

package com.huawei.javaservletdemo.sys.service;

import com.huawei.javaservletdemo.sys.dto.MenuDto;

import java.sql.SQLException;
import java.util.List;

/**
 * 菜单管理的接口
 * 创建日期：2021/12/28
 * 作者：高靖博
 * 公司：华为（昆明）数字经济学院
 */
public interface MenuService {

    /**
     * 查询菜单数据
     * @return
     */
    List<MenuDto> getMenuList() throws SQLException;
}

（4）编写业务层接口实现类

查询菜单数据的SQL

select * from t_menu
order by level asc,orders asc;

Service实现层查询内容

/**
     * 查询菜单数据
     * @return
     */
    @Override
    public List<MenuDto> getMenuList() throws SQLException {

        //1.从mysql查询数据，JDBC查询
        Connection con = MyC3P0Util.getCon();

        String sql = "select * from t_menu order by level asc,orders asc";

        PreparedStatement ps = con.prepareStatement(sql);

        ResultSet resultSet = ps.executeQuery();

        //遍历结果集将查询出来的数据保存到Dto中
        List<MenuDto> menuList = new ArrayList<>();
        while(resultSet.next()){
            MenuDto dto = new MenuDto();

            dto.setMenuID(resultSet.getString("menuID"));
            dto.setIsDelete(resultSet.getInt("isDelete"));
            dto.setMenuCode(resultSet.getString("menuCode"));
            dto.setM_name(resultSet.getString("m_name"));
            dto.setM_href(resultSet.getString("m_href"));
            dto.setLevel(resultSet.getInt("level"));
            dto.setInnerCode(resultSet.getString("innerCode"));
            dto.setOrders(resultSet.getInt("orders"));
            dto.setParentID(resultSet.getString("parentID"));
            menuList.add(dto);
        }

        //把List内容转成树形结构类型
        List<MenuDto> cc = getChildrenDataByMenuID("0", menuList);
		//连接要归还给C3P0连接池
         resultSet.close();
        ps.close();
        con.close();
        return cc;
    }

将二维表数据转换成树形数据

树形数据如：

[{
	"children": [{
		"children": [],
		"innerCode": "0000200001",
		"isDelete": 0,
		"level": 2,
		"m_href": "/classMg",
		"m_name": "班级管理",
		"menuCode": "p2p1",
		"menuID": "p2p1",
		"orders": 1,
		"parentID": "p2"
	}, {
		"children": [],
		"innerCode": "0000200002",
		"isDelete": 0,
		"level": 2,
		"m_href": "/studentMg",
		"m_name": "学员管理",
		"menuCode": "p2p2",
		"menuID": "p2p2",
		"orders": 2,
		"parentID": "p2"
	}],
	"innerCode": "00002",
	"isDelete": 0,
	"level": 1,
	"m_name": "学生管理",
	"menuCode": "p2",
	"menuID": "p2",
	"orders": 2,
	"parentID": "0"
}]

写法:

//从顶级开始查询，遍历迭代往子集查询
        List<MenuDto> cc = getChildrenDataByMenuID("0", menuList);

算法方法：

/**
     * 查询指定id的子集
     * @param menuID  要查询子集的menuID
     * @param menuList  菜单数据
     * @return  返回查询子集的menuID对应的子集数据
     */
    public List<MenuDto> getChildrenDataByMenuID(String menuID,List<MenuDto> menuList){

        List<MenuDto> list = new ArrayList<>();

        for(int i=0;i<menuList.size();i++){
            MenuDto menuDto = menuList.get(i);
            //查询当前menuID下的子集
            if(menuDto.getParentID().equals(menuID)){
                List<MenuDto> childrens = getChildrenDataByMenuID(menuDto.getMenuID(), menuList);
                menuDto.setChildren(childrens);
                list.add(menuDto);
            }
        }

        return list;

    }

3.前端代码实现

（1）当首页页面加载完成时请求ajax查询菜单数据进行渲染

初始化请求菜单ajax部分代码：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<script type="text/javascript">
$(function(){
	
	layui.use(['element'],function(){
		var element = layui.element;
		
		//调用初始化方法
		init();
		
		//监听菜单点击事件
		element.on('nav(myMenu)', function(elem){
		  console.log(elem); //得到当前点击的DOM对象
		  
		 var urlpath = elem[0].dataset.urlpath;
		 
		 $("#mainFrame").attr("src",urlpath);
		  
		});
		
		
		//初始化操作
		function init(){
			
			initMenu();
			
		}
		
		//初始化菜单

		function initMenu(){
            $.ajax({
                url:"<%=basePath%>/getMenuList",
                type:"GET",
                dataType:"JSON",
                success:function(data){
                    console.dir(data);
                    var menuData = data.data;
                    //1.初始化菜单
                    $("#myMenu").html('');
                    var html = "";
                    for(var i in menuData){
                        html +='<li class="layui-nav-item layui-nav-itemed">'+
                            '<a href="javascript:;">'+menuData[i].m_name+'</a>';
                        if(menuData[i].children.length>0){
                            html +='<dl class="layui-nav-child">';

                            var cArr = menuData[i].children;

                            for(var c in cArr){
                                html +='<dd><a data-urlPath="'+cArr[c].m_href+'">'+cArr[c].m_name+'</a></dd>';
                            }

                            html +='</dl>';
                        }
                        html +='</li>';

                        $("#myMenu").html(html);

                        element.init();

                    }
                },
                error:function(XMLHttpRequest, textStatus, errorThrown){
                    console.dir("请求失败！");
                }
            });

			
		}
		
	});
	
});

//退出登录

function outLogin(){
	layui.use(['element'],function(){
		
		layer.msg('即将退出！正在清除缓存 ... ...', {
		  icon: 1,
		  shade: [0.8, '#393D49'],
		  time: 1000
		}, function(){
		  
		  location.href = "<%=basePath%>/loginout";
		  
		});   
		
	});
}
</script>