mysql邀请人员多层级查询 mysql多级菜单查询

功能描述

• 实际上级联菜单可能在很多情况下出现,例如:在设置地址的时候后,由用户先设置省份,而后再根据省份生出对应的城市信息.
• 实现无限极力按菜单选择
• 例如有用户自己设置要生成几级菜单

数据库设计

-- 删除原有的数据表
DROP TABLE IF EXISTS menu;
-- 创建新的数据表
CREATE TABLE menu(
  mid     INT   AUTO_INCREMENT,
  title   VARCHAR(200),
  fmid  INT,
  CONSTRAINT pk_mid2 PRIMARY KEY(mid),
  CONSTRAINT fk_fmid FOREIGN KEY(fmid) REFERENCES menu(mid) ON DELETE CASCADE
);
-- 测试数据:一级菜单
INSERT INTO menu(title)VALUES('家居生活');
INSERT INTO menu(title)VALUES('电脑办公');
INSERT INTO menu(title)VALUES('粮油');
INSERT INTO menu(title)VALUES('图书');
INSERT INTO menu(title)VALUES('箱包服饰');


-- 二级菜单
INSERT INTO menu(title,fmid)VALUES('锅',1);
INSERT INTO menu(title,fmid)VALUES('碗',1);
INSERT INTO menu(title,fmid)VALUES('瓢',1);
INSERT INTO menu(title,fmid)VALUES('拖布',1);
INSERT INTO menu(title,fmid)VALUES('游戏周边',2);
INSERT INTO menu(title,fmid)VALUES('整机',2);
INSERT INTO menu(title,fmid)VALUES('数码配件',2);
INSERT INTO menu(title,fmid)VALUES('粮油',3);
INSERT INTO menu(title,fmid)VALUES('零食',3);
INSERT INTO menu(title,fmid)VALUES('啤酒饮料',3);
INSERT INTO menu(title,fmid)VALUES('计算机图书',4);
INSERT INTO menu(title,fmid)VALUES('Java入门到跑路',4);
INSERT INTO menu(title,fmid)VALUES('影视',4);
INSERT INTO menu(title,fmid)VALUES('旅行包',5);
INSERT INTO menu(title,fmid)VALUES('棉衣',5);
INSERT INTO menu(title,fmid)VALUES('睡衣',5);
INSERT INTO menu(title,fmid)VALUES('雨衣',5);

-- 测试数据,三级菜单
INSERT INTO menu(title,fmid) VALUES('不粘锅',6);
INSERT INTO menu(title,fmid) VALUES('平底锅',6);
INSERT INTO menu(title,fmid) VALUES('砂锅',6);
INSERT INTO menu(title,fmid) VALUES('刷锅',6);
INSERT INTO menu(title,fmid) VALUES('不锈钢',7);
INSERT INTO menu(title,fmid) VALUES('银碗',7);
INSERT INTO menu(title,fmid) VALUES('汤勺',8);
INSERT INTO menu(title,fmid) VALUES('木勺',8);
INSERT INTO menu(title,fmid) VALUES('普通拖把',9);
INSERT INTO menu(title,fmid) VALUES('选装拖把',9);
INSERT INTO menu(title,fmid) VALUES('游戏',10);

• 无线级别的菜单的处理,关键体现在这个父的菜单项上,但是虽然数据库是无限的,但是绝不可能在程序中实现无限.
• 在这种情况下要想实现信息的操作,唯一的用法只能够通过过Ajax异步处理操作完成.

后台业务实现

• 定义Menu,java程序类

package mao.shu.vo;

import java.io.Serializable;

public class Menu implements Serializable {
    private String mid;
    private String title;
    //表示父菜单
    private Menu fmenu;

    public String getMid() {
        return mid;
    }

    public void setMid(String mid) {
        this.mid = mid;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Menu getFmenu() {
        return fmenu;
    }

    public void setFmenu(Menu fmenu) {
        this.fmenu = fmenu;
    }
}

• 定义ImemberDAO.java程序类

package mao.shu.dao;

import mao.shu.vo.Menu;

import java.util.List;

public interface IMenuDAO {
    /**
     * 列出所有1级菜单,列出的所有1级菜单中fmenu一定为null
     * @return
     */
    public  List<Menu> findAll();

    /**
     *根据父菜单id,列出此id下的所有子菜单
     * @return
     */
    public List<Menu> findAllSub(String fmid);

}

• 定义MenuDAOImple.java程序类

package mao.shu.dao.imple;

import mao.shu.dao.IMenuDAO;
import mao.shu.vo.Menu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MenuDAOImpl implements IMenuDAO {
    private Connection conn;
    private PreparedStatement pstm;
    public MenuDAOImpl(Connection conn){
        this.conn = conn;
    }

    @Override
    public List<Menu> findAll()throws SQLException {
        List<Menu> menus = new ArrayList<Menu>();
        String sql = "SELECT mid,title FROM menu WHILE fmid IS NOLL";
        this.pstm = this.conn.prepareStatement(sql);
        ResultSet rest = this.pstm.executeQuery();
        while(rest.next()){
            Menu vo = new Menu();
            vo.setMid(rest.getString(1));
            vo.setTitle(rest.getString(2));
            menus.add(vo);
        }
        return menus;
    }

    @Override
    public List<Menu> findAllSub(String fmid) throws SQLException{
        List<Menu> menus = new ArrayList<Menu>();
        String sql = "SELECT mid,title FROM menu WHILE fmid=?";
        this.pstm = this.conn.prepareStatement(sql);
        this.pstm.setString(1,fmid);
        ResultSet rest = this.pstm.executeQuery();
        while(rest.next()){
            Menu vo = new Menu();
            vo.setMid(rest.getString(1));
            vo.setTitle(rest.getString(2));
            menus.add(vo);
        }
        return menus;
    }
}

• 定义DAOFactory.java类,DAO工厂类用于创建DAO接口子类对象

package mao.shu.factory;

import mao.shu.dao.IMemberDAO;
import mao.shu.dao.IMenuDAO;
import mao.shu.dao.imple.MemberDAOImpl;
import mao.shu.dao.imple.MenuDAOImpl;

import java.sql.Connection;

public class DAOFactory {
    public static IMemberDAO newMemberDAO(Connection conn){
        return new MemberDAOImpl(conn);
    }
    public static IMenuDAO getMenuDAO(Connection conn){
        return new MenuDAOImpl(conn);

    }
}

• 在IMenuService接口中定义两个方法,也是列出一级菜单和二级菜单

package mao.shu.service;

import mao.shu.vo.Menu;

import java.util.List;

public interface IMenuService {
    public List<Menu> findAll()throws Exception;
    public List<Menu> findAllSub(String fmid)throws Exception;
}

• 定义MenuServiceImpl.java类

package mao.shu.service.impl;

import mao.shu.dao.IMenuDAO;
import mao.shu.dbc.DatabaseConnection;
import mao.shu.factory.DAOFactory;
import mao.shu.service.IMenuService;
import mao.shu.vo.Menu;

import java.util.List;

public class MenuServiceImpl implements IMenuService {
    private DatabaseConnection dataConn  = new DatabaseConnection();
    @Override
    public List<Menu> findAll() throws Exception {
        try{
            return DAOFactory.getMenuDAO(dataConn.getConnection()).findAll();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            dataConn.getConnection().close();
        }
        return null;
    }

    @Override
    public List<Menu> findAllSub(String fmid) throws Exception {
        try{
            return DAOFactory.getMenuDAO(dataConn.getConnection()).findAllSub(fmid);
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            dataConn.getConnection().close();
        }
        return null;
    }
}

• 定义ServiceFactory.java程序类

package mao.shu.factory;

import mao.shu.service.IMemberService;
import mao.shu.service.IMenuService;
import mao.shu.service.impl.MemberServiceImpl;
import mao.shu.service.impl.MenuServiceImpl;

public class ServiceFactory {
    public static IMemberService getMemberService(){
        return new MemberServiceImpl();
    }
    public static IMenuService getMenuService(){
        return new MenuServiceImpl();
    }
}

控制层实现

• 本次的操作将采用全部的一步的通讯形式.所以对于Servlet程序而言,可以将数据装换为XML格式.
• 使用DOM4j开发包操作XML
• 如果最终Servlet生成数据为XML格式,必须将MIME类型设置为xml
• 再生成xml元素的时候,建议数据都已元素的形式出现
• 创建MenuServlet.java程序类

package mao.shu.servlet;


import mao.shu.factory.ServiceFactory;
import mao.shu.vo.Menu;
import org.dom4j.*;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;

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.lang.reflect.Method;
import java.util.Iterator;
import java.util.List;

@WebServlet("/MenuServlet/*")
public class MenuServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setCharacterEncoding("utf-8");
        String uri = req.getRequestURI();
        String status = uri.substring(uri.lastIndexOf("/")+1);
        if(status != null){
            try {
                Method method = this.getClass().getMethod(status,HttpServletRequest.class,HttpServletResponse.class);
                method.invoke(this,req,resp);
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req, resp);
    }
    public void listSub(HttpServletRequest req, HttpServletResponse resp)  {
        resp.setContentType("text/xml");
        int fmid = Integer.parseInt(req.getParameter("fmid"));
        try {
            List<Menu> all = ServiceFactory.getMenuService().findAllSub(fmid);
            Document document = DocumentHelper.createDocument();
            Element menus = document.addElement("menus");
            Iterator<Menu> iterator = all.iterator();
            while(iterator.hasNext()){
                Menu vo = iterator.next();
                Element menu = menus.addElement("menu");
                Element mid = menu.addElement("mid");
                Element title = menu.addElement("title");
                mid.addText(String.valueOf(vo.getMid()));
                title.addText(vo.getTitle());
            }
            OutputFormat outputFormat = OutputFormat.createPrettyPrint();
            outputFormat.setEncoding("utf-8");
            XMLWriter xmlWriter = new XMLWriter(resp.getWriter());
            xmlWriter.write(document);

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public void listAll(HttpServletRequest req, HttpServletResponse resp)  {
        resp.setContentType("text/xml");
        try {
            List<Menu> all = ServiceFactory.getMenuService().findAll();
            Document document = DocumentHelper.createDocument();
            Element menus = document.addElement("menus");
            Iterator<Menu> iterator = all.iterator();
            while(iterator.hasNext()){
                Menu vo = iterator.next();
                Element menu = menus.addElement("menu");
                Element mid = menu.addElement("mid");
                Element title = menu.addElement("title");
                mid.addText(String.valueOf(vo.getMid()));
                title.addText(vo.getTitle());
            }
            OutputFormat outputFormat = OutputFormat.createPrettyPrint();
            outputFormat.setEncoding("utf-8");
            XMLWriter xmlWriter = new XMLWriter(resp.getWriter());
            xmlWriter.write(document);

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

• 测试访问以下路径
• 查看一级菜单项

http://localhost:8080/AjaxProject/MenuServlet/listAll

• 查看二级菜单项

http://localhost:8080/AjaxProject/MenuServlet/listSub?fmid=2

页面实现级联菜单

• 此处的代码将采用极端程序实现级联列表
• 建立menu_demo.html,
• 先实现一级菜单实现

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>级联菜单</title>
</head>
<script type="text/javascript">
    //保存XMLHttpRequest对象的变量
    var xmlHttpRequest;
    window.onload=function(){
        document.getElementById("menua").addEventListener("click",function(){
            createMenua();
        },false)

    }

    //创建XMLHttpRequest对象的方法
    function createXMLHttpRequest(){

        if(window.XMLHttpRequest){
            xmlHttpRequest = new XMLHttpRequest();
         //判断是否为IE6的浏览器
        }else if(ActiveXObject){
            xmlHttpRequest = new ActiveXObject("Microsoft.XMLHttp");
        }
    }
    //提交请求方法
    function createMenua(){
        createXMLHttpRequest();
        //使用XMLHttpRequest对象设置请求类型
        //设置请求参数接收位置
        xmlHttpRequest.open("post","MenuServlet/listAll");
        //发送请求
        xmlHttpRequest.send(null);
        //针对服务器请求,做出回应处理
        xmlHttpRequest.onreadystatechange = function(){
           if(xmlHttpRequest.readyState == 4 && xmlHttpRequest.status == 200){
               //得到页面返回的DOM文档
               var docxml = xmlHttpRequest.responseXML;
               //得到文档树中所有的mid和title
               var mids = docxml.getElementsByTagName("mid");
               var titles = docxml.getElementsByTagName("title");
               addOption("menua",mids,titles);
           }
        }
    }
    function addOption(selectId,values,params){
        var selectEle = document.getElementById(selectId);
        if(selectEle.getElementsByTagName("option").item(1)==null){
            for(var x = 0; x < values.length;x++){
                var newOption = document.createElement("option");
                newOption.setAttribute("id",values[x].firstChild.nodeValue);
                newOption.appendChild(document.createTextNode(params[x].firstChild.nodeValue));
                selectEle.appendChild(newOption);
            }
        }


    }

</script>
<body>
    <div>
        <table>
            <tr>
                <td>
                    <select id="menua"><option>==========请选择分类==========</option></select>
                </td>
                <td>
                    <select id="menub"><option>==========请选择分类==========</option></select>
                </td>
                <td>
                    <select id="menuc"><option>==========请选择分类==========</option></select>
                </td>
            </tr>
        </table>
    </div>
</body>
</html>

• 定义二级菜单生成过程

• 如果当一级菜单的内容发生改变之后,并发现当前的一级菜单不是0的情况表示需要进行二级菜单的读取.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>级联菜单</title>
</head>
<script type="text/javascript">
    //保存XMLHttpRequest对象的变量
    var xmlHttpRequest;
    window.onload=function(){
        var selectEle = document.getElementById("menua");
        if(selectEle.getElementsByTagName("option").item(1)==null) {
            createMenua();
        }
        selectEle.addEventListener("change",function(){
            alert(this.value);
            if(this.value == 0){
                document.getElementById("menub").length = 1;
                document.getElementById("menuc").length = 1;
            }else{
                loadSubMenu("menub",this.value);
            }
        },false)
    }
    function loadSubMenu(eleName,fmid){
        createXMLHttpRequest();
        //使用XMLHttpRequest对象设置请求类型
        //设置请求参数接收位置
        xmlHttpRequest.open("post","MenuServlet/listSub?fmid="+fmid);
        //发送请求
        xmlHttpRequest.send(null);
        //针对服务器请求,做出回应处理
        xmlHttpRequest.onreadystatechange = function(){
            if(xmlHttpRequest.readyState == 4 && xmlHttpRequest.status == 200){
                //得到页面返回的DOM文档
                var docxml = xmlHttpRequest.responseXML;
                //得到文档树中所有的mid和title
                var mids = docxml.getElementsByTagName("mid");
                var titles = docxml.getElementsByTagName("title");
                addOption(eleName,mids,titles);
            }
        }
    }
    //创建XMLHttpRequest对象的方法
    function createXMLHttpRequest(){

        if(window.XMLHttpRequest){
            xmlHttpRequest = new XMLHttpRequest();
         //判断是否为IE6的浏览器
        }else if(ActiveXObject){
            xmlHttpRequest = new ActiveXObject("Microsoft.XMLHttp");
        }
    }
    //提交请求方法
    function createMenua(){
        createXMLHttpRequest();
        //使用XMLHttpRequest对象设置请求类型
        //设置请求参数接收位置
        xmlHttpRequest.open("post","MenuServlet/listAll");
        //发送请求
        xmlHttpRequest.send(null);
        //针对服务器请求,做出回应处理
        xmlHttpRequest.onreadystatechange = function(){
           if(xmlHttpRequest.readyState == 4 && xmlHttpRequest.status == 200){
               //得到页面返回的DOM文档
               var docxml = xmlHttpRequest.responseXML;
               //得到文档树中所有的mid和title
               var mids = docxml.getElementsByTagName("mid");
               var titles = docxml.getElementsByTagName("title");
               addOption("menua",mids,titles);
           }
        }
    }
    function addOption(selectId,values,params){
        var selectEle = document.getElementById(selectId);
            //清除原有的option
            selectEle.length = 1;
            for(var x = 0; x < values.length;x++){
                var newOption = document.createElement("option");
                newOption.setAttribute("value",values[x].firstChild.nodeValue);
                newOption.appendChild(document.createTextNode(params[x].firstChild.nodeValue));
                selectEle.appendChild(newOption);
            }



    }

</script>
<body>
    <div>
        <table>
            <tr>
                <td>
                    <select id="menua"><option value="0">==========请选择分类==========</option></select>
                </td>
                <td>
                    <select id="menub"><option value="0">==========请选择分类==========</option></select>
                </td>
                <td>
                    <select id="menuc"><option value="0">==========请选择分类==========</option></select>
                </td>
            </tr>
        </table>
    </div>
</body>
</html>

• 三级菜单和二级菜单没有任何区别
• 在menub中绑定change事件

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>级联菜单</title>
</head>
<script type="text/javascript">
    //保存XMLHttpRequest对象的变量
    var xmlHttpRequest;
    window.onload=function(){
        var selectEle = document.getElementById("menua");
        if(selectEle.getElementsByTagName("option").item(1)==null) {
            createMenua();
        }
        selectEle.addEventListener("change",function(){
            if(this.value == 0){
                document.getElementById("menub").length = 1;
                document.getElementById("menuc").length = 1;
            }else{
                loadSubMenu("menub",this.value);
            }
        },false)
        document.getElementById("menub").addEventListener("change",function(){
            if(this.value == 0){
                document.getElementById("menuc").length = 1;
            }else{
                loadSubMenu("menuc",this.value);
            }
        },false)
    }
    function loadSubMenu(eleName,fmid){
        createXMLHttpRequest();
        //使用XMLHttpRequest对象设置请求类型
        //设置请求参数接收位置
        xmlHttpRequest.open("post","MenuServlet/listSub?fmid="+fmid);
        //发送请求
        xmlHttpRequest.send(null);
        //针对服务器请求,做出回应处理
        xmlHttpRequest.onreadystatechange = function(){
            if(xmlHttpRequest.readyState == 4 && xmlHttpRequest.status == 200){
                //得到页面返回的DOM文档
                var docxml = xmlHttpRequest.responseXML;
                //得到文档树中所有的mid和title
                var mids = docxml.getElementsByTagName("mid");
                var titles = docxml.getElementsByTagName("title");
                addOption(eleName,mids,titles);
            }
        }
    }
    //创建XMLHttpRequest对象的方法
    function createXMLHttpRequest(){

        if(window.XMLHttpRequest){
            xmlHttpRequest = new XMLHttpRequest();
         //判断是否为IE6的浏览器
        }else if(ActiveXObject){
            xmlHttpRequest = new ActiveXObject("Microsoft.XMLHttp");
        }
    }
    //提交请求方法
    function createMenua(){
        createXMLHttpRequest();
        //使用XMLHttpRequest对象设置请求类型
        //设置请求参数接收位置
        xmlHttpRequest.open("post","MenuServlet/listAll");
        //发送请求
        xmlHttpRequest.send(null);
        //针对服务器请求,做出回应处理
        xmlHttpRequest.onreadystatechange = function(){
           if(xmlHttpRequest.readyState == 4 && xmlHttpRequest.status == 200){
               //得到页面返回的DOM文档
               var docxml = xmlHttpRequest.responseXML;
               //得到文档树中所有的mid和title
               var mids = docxml.getElementsByTagName("mid");
               var titles = docxml.getElementsByTagName("title");
               addOption("menua",mids,titles);
           }
        }
    }
    function addOption(selectId,values,params){
        var selectEle = document.getElementById(selectId);
            //清除原有的option
            selectEle.length = 1;
            for(var x = 0; x < values.length;x++){
                var newOption = document.createElement("option");
                newOption.setAttribute("value",values[x].firstChild.nodeValue);
                newOption.appendChild(document.createTextNode(params[x].firstChild.nodeValue));
                selectEle.appendChild(newOption);
            }



    }

</script>
<body>
    <div>
        <table>
            <tr>
                <td>
                    <select id="menua"><option value="0">==========请选择分类==========</option></select>
                </td>
                <td>
                    <select id="menub"><option value="0">==========请选择分类==========</option></select>
                </td>
                <td>
                    <select id="menuc"><option value="0">==========请选择分类==========</option></select>
                </td>
            </tr>
        </table>
    </div>
</body>
</html>

• 整体效果