Servlet实战-菜单管理
1.查询菜单数据
(1)查询的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.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* 创建日期：2021/12/29
* 作者：高靖博
* 公司：华为（昆明）数字经济学院
*/
@WebServlet("/getMenuListPages")
public class GetMenuListPagesServlet extends HttpServlet {
//创建service层对象
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 res = new Result();
//接收参数
int page = Integer.valueOf(req.getParameter("page"));//页码 - layui提供
int limit = Integer.valueOf(req.getParameter("limit"));//每页显示的行数 - layui提供
//查询参数
String keyWorld = req.getParameter("keyWorld");
//调用service层
try {
Map<String,Object> menuListPages = menuService.getMenuListPages(page, limit, keyWorld);
res.setMsg("查询成功！");
res.setCode("0000");
res.setData(menuListPages);
} catch (SQLException e) {
System.err.println("菜单表格查询异常！");
res.setMsg("菜单表格查询异常！");
res.setCode("9999");
e.printStackTrace();
}
resp.setContentType("application/json;charset=UTF-8");
PrintWriter writer = resp.getWriter();
writer.print(res.toString());
writer.flush();
writer.close();
}
}
(2)查询菜单的service-接口
/**
* 分页查询菜单内容
* @param page 页码
* @param limit 每页显示数量
* @param keyWorld 菜单编号或者菜单名称查询条件
* @return 菜单集合信息
* @throws SQLException
*/
Map<String,Object> getMenuListPages(int page, int limit, String keyWorld) throws SQLException;
(3)查询菜单的service-实现
/**
* 分页查询菜单内容
* @param page 页码
* @param limit 每页显示数量
* @param keyWorld 菜单编号或者菜单名称查询条件
* @return 菜单集合信息
* @throws SQLException
*/
@Override
public Map<String,Object> getMenuListPages(int page, int limit, String keyWorld) throws SQLException {
Map<String,Object> resMap = new HashMap<>();
List<MenuDto> list = new ArrayList<>();
//获取连接
Connection con = MyC3P0Util.getCon();
//准备查询SQL
String sql = "SELECT * FROM `t_menu` where isDelete=0 and( menuCode like ? or m_name like ?) limit ?,?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,"%"+keyWorld+"%");
ps.setString(2,"%"+keyWorld+"%");
int pyl = (page - 1)*limit;
ps.setInt(3,pyl);
ps.setInt(4,limit);
System.out.println(ps);
ResultSet resultSet = ps.executeQuery();
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"));
list.add(dto);
}
//归还连接
resultSet.close();
ps.close();
con.close();
resMap.put("pages",list);
//需要查询新的再获取一个连接
Connection con2 = MyC3P0Util.getCon();
//查询总共有多少条（要带上条件，查询当前条件下有多少行数据）
String sql2 = "select count(*) as cc from t_menu where isDelete=0 and( menuCode like ? or m_name like ?)";
PreparedStatement ps2 = con2.prepareStatement(sql2);
ps2.setString(1,"%"+keyWorld+"%");
ps2.setString(2,"%"+keyWorld+"%");
ResultSet resultSet2 = ps2.executeQuery();
int totleCount = 0;
while(resultSet2.next()){
totleCount = resultSet2.getInt("cc");
}
resMap.put("totleCount",totleCount);
//查询后归还连接
resultSet2.close();
ps2.close();
con2.close();
return resMap;
}
(4)前端layui实现数据表格、数据表格的查询选项
html:
<body>
<!-- 查询条件 -->
<div class="layui-row">
<div class="layui-col-xs3 layui-col-sm3 layui-col-md3" style="margin-right: 5px">
<!-- 菜单名称或编码 -->
<input type="text" id="s_name" name="keyWorld" placeholder="查询菜单编号、菜单名称" autocomplete="off"
class="layui-input"/>
</div>
<!-- 查询、清空 -->
<div class="layui-col-xs1 layui-col-sm1 layui-col-md1">
<button type="button" class="layui-btn layui-btn-normal" id="toSerach">查询</button>
<button type="button" class="layui-btn layui-btn-warm"id="toRest">重置</button>
</div>
</div>
<!-- 数据表格 -->
<div class="layui-row">
<div class="layui-col-xs12 layui-col-sm12 layui-col-md12">
<table id="menuTable" lay-filter="menuTableFilter"></table>
</div>
</div>
</body>
js:layui代码部分
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<script type="text/javascript">
$(function(){
layui.use(['table','form'], function() {
var table = layui.table;
var form = layui.table;
//页面内容初始化
init();
//初始化方法
function init() {
//加载表格
initMenuTable();
}
/***--- JQuery监听事件 ---**/
//查询事件
$("#toSerach").on("click",function(e){
console.dir("查询！！");
initMenuTable();
});
//重置事件
$("#toRest").on("click",function(e){
console.dir("重置！！");
$("#s_name").val("");
initMenuTable();
});
/***----------------- layui代码部分 ------------------------------------***/
//初始化表格
function initMenuTable(){
table.render({
elem: '#menuTable' //dom元素id
,id:'menuTableID' //layui唯一标识
,limits:[2,5,10] //分页选项配置
,limit:2 //默认每页分页数，从limits属性里选择
,url: '<%=basePath%>/getMenuListPages' //数据接口
,where:{'keyWorld':$("#s_name").val()} //数据接口的参数
,page: true //开启分页
,parseData: function(res){ //设置返回数据解析规则
return {
"code": res.code=="0000"?0:res.code, //解析接口状态,状态值为0时才叫正常
"msg": res.msg, //解析提示文本
"count": res.data.totleCount, //如果分页必须要写（不分页的总行数）
"data": res.data.pages //解析数据列表
};
}
,cols: [[ //表头
{type:'checkbox',fixed: 'left'}
,{field: 'menuCode', title: '编号'}
,{field: 'm_name', title: '名称'}
,{field: 'm_href', title: '地址'}
,{field: 'level', title: '层级'}
,{field: 'orders', title: '排序'}
,{field: 'parentID', title: '父级菜单'}
,{title: '操作'}
]]
});
}
});
});
</script>
2.菜单管理-新增
（1）实现表头工具栏、行工具栏
html:
。。。。
</body>
<!-- 表头工具栏 -->
<script type="text/html" id="tableHeadTools">
<button type="button" class="layui-btn layui-btn-normal"><i class="layui-icon"></i> 新增</button>
<button type="button" class="layui-btn layui-btn-danger"><i class="layui-icon"></i> 批量删除</button>
</script>
<!-- 行工具栏 -->
<script type="text/html" id="tableRowsTools">
<button title="详情" type="button" class="layui-btn layui-btn-xs layui-btn-normal"><i class="layui-icon"></i></button>
<button title="修改" type="button" class="layui-btn layui-btn-xs layui-btn-warm"><i class="layui-icon"></i></button>
<button title="删除" type="button" class="layui-btn layui-btn-xs layui-btn-danger"><i class="layui-icon"></i></button>
</script>
js:
table.render({
elem: '#menuTable'
,id:'menuTableID'
,limits:[2,5,10]
,limit:2
,url: '<%=basePath%>/getMenuListPages'
,where:{'keyWorld':$("#s_name").val()}
,page: true
,toolbar:"#tableHeadTools" //表头工具栏指向id
,parseData: function(res){
return {
"code": res.code=="0000"?0:res.code,
"msg": res.msg,
"count": res.data.totleCount,
"data": res.data.pages
};
}
,cols: [[ //表头
{type:'checkbox',fixed: 'left'}
,{field: 'menuCode', title: '编号'}
,{field: 'm_name', title: '名称'}
,{field: 'm_href', title: '地址'}
,{field: 'level', title: '层级'}
,{field: 'orders', title: '排序'}
,{field: 'parentID', title: '父级菜单'}
,{title: '操作',
toolbar:"#tableRowsTools",//行工具栏指向id
align:'center',
fixed: 'right'}
]]
});
（2）表头工具栏和行工具栏事件绑定（layui事件触发机制实现）
html部分：
<!-- 标注lay-event事件名称 -->
<button type="button" lay-event="add" class="layui-btn layui-btn-normal"><i class="layui-icon"></i> 新增</button>
js部分：
/**---------------- Layui事件监听内容 -------------------------------------*/
//表头工具栏监听（在dom元素上一定要写lay-event否则无法监听）
table.on('toolbar(menuTableFilter)', function(obj){
var eventStr = obj.event;
if(eventStr=="add"){//新增
}else if(eventStr=="deletes"){//批量删除
}
});
//表格行-工具栏监听（在dom元素上一定要写lay-event否则无法监听）
table.on('tool(menuTableFilter)', function(obj){
var eventStr = obj.event;
if(eventStr=="info"){//查看
}else if(eventStr=="delete"){//删除
}else if(eventStr=="edit"){//修改
}
});
（3）新增、修改模态框
需要引入xm-select.js
#在common.jsp中引入类库
<!-- 树形下拉表单组件xm-select -->
<script type="text/javascript" src="<%=basePath%>/lib/xm-select.js"></script>
html：**
<!-- 新增\编辑菜单信息弹出框 -->
<div id="addOrEditMenu" style="display: none;">
<form class="layui-form"lay-filter="addOrEditMenuForm" style="padding: 10px">
<div class="layui-form-item">
<label class="layui-form-label">菜单编号</label>
<div class="layui-input-block">
<input type="text" name="menuCode" required lay-verify="required"
placeholder="请输入菜单编号" autocomplete="off"
class="layui-input" />
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">菜单名称</label>
<div class="layui-input-block">
<input type="text" name="m_name" required lay-verify="required"
placeholder="请输入菜单名称" autocomplete="off"
class="layui-input" />
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">链接地址</label>
<div class="layui-input-block">
<input type="text" name="m_href"
placeholder="请输入链接地址" autocomplete="off"
class="layui-input" />
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">排序</label>
<div class="layui-input-block">
<input type="text" name="orders"
placeholder="请输入顺序号" autocomplete="off"
class="layui-input" />
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">是否顶级</label>
<div class="layui-input-block">
<input type="checkbox" id="isParent" lay-filter="isParentFilter"
lay-text="是|否" lay-skin="switch" name="isParent" />
</div>
</div>
<div class="layui-form-item" id="selectParentDiv">
<label class="layui-form-label">父级菜单</label>
<div class="layui-input-block">
<!-- xmSelect渲染的Dom元素 -->
<div style="width: calc(100% - 10px)" id="menuSelect" class="xm-select-demo"></div>
</div>
</div>
<div class="layui-form-item">
<button type="button" lay-submit lay-filter="menuFormSub" class="layui-btn layui-btn-normal">保存</button>
<button type="reset" id="menuFormRest" class="layui-btn layui-btn-normal">重置</button>
</div>
</form>
</div>
js:
#渲染xm-select下拉框
/***----------------- xm-select代码部分 ------------------------------------***/
//渲染xm-select树形下拉
var myXmSelectObj;
function initAddSelect(){
//树形数据变量
var menuTree = [];
//ajax查询树形数据
$.ajax({
url:"<%=basePath%>/getMenuList",
type:"GET",
dataType:"JSON",
async:false,
success:function(data){
console.dir(data);
menuTree = data.data;
//遍历循环修改树形结构属性内容
//传入数组类型（引用类型）传递的是地址值的拷贝
toForMenuTree(menuTree);
},
error:function(XMLHttpRequest, textStatus, errorThrown){
console.dir("请求失败！");
}
});
//渲染表单元素
myXmSelectObj = xmSelect.render({
el: '#menuSelect',
autoRow: true,
filterable: true,
radio: true,
direction: 'down',
tree: {
show: true,
showFolderIcon: true,
showLine: true,
indent: 20,
strict: false,
},
height: 'auto',
data: function(){
return menuTree
}
})
}
前端遍历树形数据内容方法
//遍历循环修改树形结构属性内容
function toForMenuTree(menuTree){
for(var i in menuTree){
menuTree[i].name = menuTree[i].m_name;//xm-select要的是name和value属性
menuTree[i].value = menuTree[i].menuID;
if(menuTree[i].children.length>0){
toForMenuTree(menuTree[i].children);
}
}
}
模态框弹出js**
//先渲染xm下拉组件
initAddSelect();
layer.open({
type: 1,
area: ['60%','80%'],
content: $('#addOrEditMenu'), //这里content是一个DOM，注意：最好该元素要存放在body最外层，否则可能被其它的相对元素所影响
cancel: function(index, layero){
$('#addOrEditMenu').css("display","none");
layer.close(index);//关闭当前窗口
//重置表单
clearAddOrEditForm();
return false;
}
});
(4)表单操作：提交、xmselect渲染
//监听表单提交
form.on('submit(menuFormSub)', function(data){
var formData = data.field; //当前容器的全部表单字段，名值对形式：{name: value}
if(formData.select==""){//顶级菜单
formData.parentID = "0";
formData.level = 1;
}else{
formData.parentID = formData.select;
//level的计算：顶级level=》1 子集是父级level+1
//取出xm-select返回结果
var valueArr = myXmSelectObj.getValue();
var p_level = valueArr[0].level;
formData.level = p_level+1;
}
console.dir(formData);
//发送保存
$.ajax({
url:"<%=basePath%>/CURDMenu",
type:"POST",
dataType:"JSON",
data:JSON.parse(JSON.stringify(formData)),
async:false,
success:function(data){
console.dir(data);
if(data.code=="0000"){
layer.msg(data.msg, {icon: 1});
//关闭模态框
layer.close(addOrEditIndex);
//重置表单
clearAddOrEditForm();
$('#addOrEditMenu').css("display","none");
//刷新表格
initMenuTable();
}else{
layer.msg(data.msg, {icon: 5});
}
},
error:function(XMLHttpRequest, textStatus, errorThrown){
console.dir("请求失败！");
}
});
return false; //阻止表单跳转。如果需要表单跳转，去掉这段即可。
});
(5) 动态选择：是否为父级菜单的选择
如果是父级菜单，隐藏父级菜单选项，反之显示
html:
<div class="layui-form-item">
<label class="layui-form-label">是否顶级</label>
<div class="layui-input-block">
<input type="checkbox" id="isParent" lay-filter="isParentFilter"
lay-text="是|否" lay-skin="switch" name="isParent" />
</div>
</div>
js:
//监听form表单中-开关的变化
form.on('switch(isParentFilter)', function(data){
var isParent = data.elem.checked; //开关是否开启，true或者false
if(isParent){//是顶级菜单
myXmSelectObj.setValue([]);
$("#selectParentDiv").css("display","none");
}else{//是子集菜单
myXmSelectObj.setValue([]);
$("#selectParentDiv").css("display","block");
}
});
（6）后端需要生成树形结构中的innerCode值，创建一个工具类完成innerCode的生成
编写工具类：MyBaseUtil，用于存放基础操作经常使用的方法(生成主键id、生成innerCode)
package com.huawei.javaservletdemo.sys.util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;
/**
* 基础框架工具类
* 创建日期：2021/12/31
* 作者：高靖博
* 公司：华为（昆明）数字经济学院
*/
public class MyBaseUtil {
private MyBaseUtil(){}
/**
* 生成主键
* @return
*/
public static String getPrimaryKey(){
String pk = UUID.randomUUID().toString().replaceAll("-","").substring(0,20);
return pk;
}
/**
* 根据表以及父级id生成对应的innerCode值
* @param tableName 对应的数据表名称
* @param pkName 数据表中主键字段名称
* @param parentID 要生成的数据父级id
* @return
*/
public static String getInnerCode(String tableName,String pkName,String parentID) throws SQLException {
//要返回的innerCode
String innerCode = "";
//1.判断是否是顶级
if("0".equals(parentID)){//顶级
//1.1 获取连接
Connection con = MyC3P0Util.getCon();
Statement statement = con.createStatement();
//sql语句
String sql = "select count(*) as cc from "+tableName+" where parentID='"+parentID+"'";
System.out.println("SQL(1):"+sql);
ResultSet resultSet = statement.executeQuery(sql);
resultSet.next();
//获取顶级菜单现有的数量
int cc = resultSet.getInt("cc");
//根据当前层级的数值统计生成当前层级的innerCode
String thisLevelInnerCode = getThisLevelInnerCode(cc);
innerCode = thisLevelInnerCode;
resultSet.close();
statement.close();
con.close();
}else{//子集
//1.先获取父级的innerCode值
Connection con = MyC3P0Util.getCon();
Statement statement = con.createStatement();
String sql = "select innerCode from "+tableName+" where "+pkName+"='"+parentID+"'";
System.out.println("SQL(2):"+sql);
ResultSet resultSet = statement.executeQuery(sql);
//父级的innerCode值
String parentInnerCode = "";
while(resultSet.next()){
parentInnerCode = resultSet.getString("innerCode");
}
resultSet.close();
statement.close();
con.close();
//2.将当前层级的innerCode值和父级innerCode拼接
//获取当前层级的innerCode
Connection con2 = MyC3P0Util.getCon();
Statement statement2 = con2.createStatement();
//sql语句
String sql2 = "select count(*) as cc from "+tableName+" where parentID='"+parentID+"'";
System.out.println("SQL(3):"+sql2);
ResultSet resultSet2 = statement2.executeQuery(sql2);
resultSet2.next();
//获取顶级菜单现有的数量
int cc2 = resultSet2.getInt("cc");
resultSet2.close();
statement2.close();
con2.close();
//根据当前层级的数值统计生成当前层级的innerCode
String thisLevelInnerCode = getThisLevelInnerCode(cc2);
//和父级拼接返回
innerCode = parentInnerCode + thisLevelInnerCode;
}
return innerCode;
}
/**
* 根据当前层级有多少数据生成当前层级的innerCode值
* @param nowCount 当前层级有多少值
* @return
*/
private static String getThisLevelInnerCode(int nowCount){
nowCount+=1;
String innerCode = "";
if(nowCount>=0&&nowCount<10){//0~10 0000 + nowCount
innerCode = "0000"+nowCount;
}else if(nowCount>=10&&nowCount<100){ // 10~100 000 + nowCount
innerCode = "000"+nowCount;
}else if(nowCount>=100&&nowCount<1000){ // 100~1000 00 + nowCount
innerCode = "00"+nowCount;
}else if(nowCount>=1000&&nowCount<10000){ // 1000~10000 0 + nowCount
innerCode = "0"+nowCount;
}else if(nowCount>=10000&&nowCount<100000){// 10000~100000 nowCount
innerCode = ""+nowCount;
}
return innerCode;
}
}
(7) 后端实现新增功能代码
package com.huawei.javaservletdemo.sys.service.impl;
import com.huawei.javaservletdemo.sys.dto.MenuDto;
import com.huawei.javaservletdemo.sys.service.CURDMenuService;
import com.huawei.javaservletdemo.sys.util.MyBaseUtil;
import com.huawei.javaservletdemo.sys.util.MyC3P0Util;
import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.UUID;
/**
* 创建日期：2021/12/30
* 作者：高靖博
* 公司：华为（昆明）数字经济学院
*/
public class CURDMenuServiceImpl implements CURDMenuService {
/**
* 添加菜单信息
* @param menuCode 菜单编码
* @param m_name 菜单名称
* @param m_href 菜单地址
* @param level 菜单层级
* @param orders 排序号
* @param parentID 父级菜单id
* @return
*/
@Override
public MenuDto addMenu(HttpServletRequest req,String menuCode,
String m_name, String m_href,
int level, int orders, String parentID) throws SQLException {
MenuDto dto = new MenuDto();
//生成主键字段值-menuID（UUID生成）
String primaryKey = MyBaseUtil.getPrimaryKey();
//获取客户端id
String clientAddress = req.getRemoteAddr();
//生成innerCode
String innerCode = MyBaseUtil.getInnerCode("t_menu", "menuID", parentID);
//插入数据
Connection con = MyC3P0Util.getCon();
String sql = "INSERT INTO `t_menu`(`menuID`, `isDelete`, " +
"`ipaddress`, `addTime`, `updateTime`, `deleteTime`, " +
"`menuCode`, `m_name`, `m_href`, `level`, `innerCode`, " +
"`orders`, `parentID`) " +
"VALUES (?, " +//menuID
"0, " +
"?, " +//ipaddress
"?, " +//addTime
"NULL, " +
"NULL, " +
"?, " +//menuCode
"?, " +//m_name
"?, " +//m_href
"?, " +//level
"?, " +//innerCode
"?, " +//orders
"?);\n";//parentID
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,primaryKey);
ps.setString(2,clientAddress);
ps.setDate(3, new java.sql.Date(new Date().getTime()));
ps.setString(4,menuCode);
ps.setString(5,m_name);
ps.setString(6,m_href);
ps.setInt(7,level);
ps.setString(8,innerCode);
ps.setInt(9,orders);
ps.setString(10,parentID);
int i = ps.executeUpdate();
if(i>0){//插入成功！
dto.setMenuID(primaryKey);
dto.setMenuCode(menuCode);
dto.setM_name(m_name);
dto.setM_href(m_href);
dto.setLevel(level);
dto.setInnerCode(innerCode);
dto.setParentID(parentID);
}else{//插入失败
throw new SQLException();
}
return dto;
}
}
3.更新菜单信息
（1）前端代码，让新增和修改使用同一个表单操作
1.1 在form表单中添加一个隐藏域，保存menuID字段值
<form class="layui-form"lay-filter="addOrEditMenuForm" style="padding: 10px">
<input type="hidden" name="menuID" />
。。。
1.2 当表单清空时也需要清空menuID
form.val("addOrEditMenuForm", { //formTest 即 class="layui-form" 所在元素属性 lay-filter="" 对应的值
"menuCode": ""
,"m_name": ""
,"m_href": ""
,"orders": ""
,"isParent":""
,"menuID":"" //menuID一定要清空
});
1.3 当点击编辑按钮，实现效果其实和新增一致，只是修改了弹出框的title树形为“编辑菜单”
...
else if(eventStr=="edit"){//修改
//先渲染xm下拉组件
initAddSelect();
//渲染要编辑的表单数据(ajax)
var menuData= getMenuDataByID(rowData.menuID);
//判断是否是顶级
if(menuData.parentID=="0"){//顶级
//开关显示为-是
$("#isParent").attr("checked","checked");//使用dom的方式赋值开关
form.render();
element.init();//刷新表单状态
//隐藏选择
$("#selectParentDiv").css("display","none");
}else{//子集
//开关显示为-否（其实就不用赋值即可）
//展示选择-xmSelect赋值
$("#selectParentDiv").css("display","block");
myXmSelectObj.setValue([menuData.parentID]);
}
//将查询出的值渲染到表单中
form.val("addOrEditMenuForm",menuData);
addOrEditIndex = layer.open({
type: 1,
title:"修改菜单",
area: ['60%','80%'],
content: $('#addOrEditMenu'), //这里content是一个DOM，注意：最好该元素要存放在body最外层，否则可能被其它的相对元素所影响
cancel: function(index, layero){
$('#addOrEditMenu').css("display","none");
layer.close(index);//关闭当前窗口
//重置表单
clearAddOrEditForm();
return false;
}
});
}
1.4 当表单提交时ajax提交的URL判断发生变化
...
var ajaxURL = "";//新增和修改只是url变化
if(formData.menuID==""){//新增
ajaxURL = "<%=basePath%>/CURDMenu";
}else{//修改
ajaxURL = "<%=basePath%>/UpdateMenu";
}
//发送保存
$.ajax({
url:ajaxURL,
type:"POST",
....
(2) 后端代码，编写修改菜单信息
1.1 根据menuID查询菜单数据
#----------------------------Servlet部分-----------------------------------
/**
* 查询菜单信息通过菜单ID
* @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 res = new Result();
//接收参数
String menuID = req.getParameter("menuID");
//调用service层
try {
Map<String,Object> resMap = curdMenuService.getMenuDataByID(req,menuID);
res.setMsg("查询成功！");
res.setCode("0000");
res.setData(resMap);
} catch (SQLException e) {
System.err.println("查询菜单异常！");
res.setMsg("查询菜单异常！");
res.setCode("9999");
e.printStackTrace();
}
resp.setContentType("application/json;charset=UTF-8");
PrintWriter writer = resp.getWriter();
writer.print(res.toString());
writer.flush();
writer.close();
}
#----------------------------接口部分-----------------------------------
/**
* 查询菜单信息
* @param req 请求对象
* @param menuID 查询菜单id
* @return
*/
Map<String, Object> getMenuDataByID(HttpServletRequest req, String menuID) throws SQLException;
#----------------------------实现部分-----------------------------------
/**
* 查询菜单信息
* @param req 请求对象
* @param menuID 查询菜单id
* @return
*/
@Override
public Map<String, Object> getMenuDataByID(HttpServletRequest req, String menuID) throws SQLException {
Map<String,Object> resMap = new HashMap<>();
//获取连接
Connection con = MyC3P0Util.getCon();
String sql = "select t_menu.*,pm.m_name as parentName from t_menu left join t_menu as pm\n" +
"on t_menu.parentID = pm.menuID\n" +
"where t_menu.menuID=?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,menuID);
ResultSet resultSet = ps.executeQuery();
while(resultSet.next()){
resMap.put("menuID",resultSet.getString("menuID"));
resMap.put("menuCode",resultSet.getString("menuCode"));
resMap.put("m_name",resultSet.getString("m_name"));
resMap.put("m_href",resultSet.getString("m_href"));
resMap.put("level",resultSet.getInt("level"));
resMap.put("innerCode",resultSet.getString("innerCode"));
resMap.put("orders",resultSet.getInt("orders"));
resMap.put("parentID",resultSet.getString("parentID"));
resMap.put("parentName",resultSet.getString("parentName"));
}
resultSet.close();
ps.close();
con.close();
return resMap;
}
1.1 修改菜单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.CURDMenuService;
import com.huawei.javaservletdemo.sys.service.impl.CURDMenuServiceImpl;
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.sql.SQLException;
/**
* 创建日期：2021/12/31
* 作者：高靖博
* 公司：华为（昆明）数字经济学院
*/
@WebServlet("/updateMenu")
public class UpdateMenu extends HttpServlet {
private CURDMenuService curdMenuService = new CURDMenuServiceImpl();
/**
* 修改菜单信息
* @param req
* @param resp
* @throws ServletException
* @throws IOException
*/
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("UTF-8");
Result res = new Result();
//接收参数
String menuID = req.getParameter("menuID"); //相比新增多了一个要修改的menuID字段
String menuCode = req.getParameter("menuCode");
String m_name = req.getParameter("m_name");
String m_href = req.getParameter("m_href");
int level = Integer.valueOf(req.getParameter("level"));
int orders = Integer.valueOf(req.getParameter("orders"));
String parentID = req.getParameter("parentID");
//调用service层
try {
MenuDto dto = curdMenuService.updateMenu(req,menuID,menuCode,m_name,m_href,level,orders,parentID);
res.setMsg("修改成功！");
res.setCode("0000");
res.setData(dto);
} catch (SQLException e) {
System.err.println("修改菜单异常！");
res.setMsg("修改菜单异常！");
res.setCode("9999");
e.printStackTrace();
}
resp.setContentType("application/json;charset=UTF-8");
PrintWriter writer = resp.getWriter();
writer.print(res.toString());
writer.flush();
writer.close();
}
}
1.2 修改菜单-接口层( 可以和新增操作同一个接口)
/**
* 修改菜单信息
* @param req 请求对象
* @param menuID 菜单id
* @param menuCode 菜单编码
* @param m_name 菜单名称
* @param m_href 菜单地址
* @param level 菜单层级
* @param orders 排序号
* @param parentID 父级菜单id
* @return
*/
MenuDto updateMenu(HttpServletRequest req, String menuID, String menuCode, String m_name, String m_href, int level, int orders, String parentID) throws SQLException;
1.3 修改菜单-实现
/**
* 修改菜单信息
* @param req 请求对象
* @param menuID 菜单id
* @param menuCode 菜单编码
* @param m_name 菜单名称
* @param m_href 菜单地址
* @param level 菜单层级
* @param orders 排序号
* @param parentID 父级菜单id
* @return
*/
@Override
public MenuDto updateMenu(HttpServletRequest req, String menuID, String menuCode, String m_name, String m_href, int level, int orders, String parentID) throws SQLException {
MenuDto dto = new MenuDto();
//修改时间
java.sql.Date updateDate = new java.sql.Date(new Date().getTime());
//获取连接
Connection con = MyC3P0Util.getCon();
String getOldSQL = "select parentID from t_menu where menuID='"+menuID+"'";
Statement statement = con.createStatement();
ResultSet resultSet = statement.executeQuery(getOldSQL);
resultSet.next();
//原先的parentID值
String oldParentID = resultSet.getString("parentID");
resultSet.close();
statement.close();
con.close();
//在修改之前先查询老数据中的parentID和我当前的parentID是否一致
boolean isNeedUpdateInnerCode = false;
//准备一个innerCode
String innerCode = "";
//如果一致则不用更新innerCode字段
if(oldParentID.equals(parentID)){
isNeedUpdateInnerCode = false;
}else{//如果不一致说明结构发生改变，从新生成innerCode
isNeedUpdateInnerCode = true;
innerCode = MyBaseUtil.getInnerCode("t_menu","menuID",parentID);
}
Connection update_con = MyC3P0Util.getCon();
String sql = "UPDATE `t_menu` " +
"SET `isDelete` = 0, " +
"`updateTime` = ?, " +
"`menuCode` = ?, " +
"`m_name` = ?, " +
"`m_href` = ?, `level` = ?, "+
" `orders` = ?, " ;
if(isNeedUpdateInnerCode){
sql += "`innerCode` = ?,";
}
sql +=
"`parentID` = ? WHERE `menuID` = ?;\n";
PreparedStatement ps = update_con.prepareStatement(sql);
ps.setDate(1, updateDate);
ps.setString(2,menuCode);
ps.setString(3,m_name);
ps.setString(4,m_href);
ps.setInt(5,level);
ps.setInt(6,orders);
if(isNeedUpdateInnerCode){
ps.setString(7,innerCode);
ps.setString(8,parentID);
ps.setString(9,menuID);
}else{
ps.setString(7,parentID);
ps.setString(8,menuID);
}
int i = ps.executeUpdate();
if(i>0){//更新成功！
dto.setMenuID(menuID);
dto.setMenuCode(menuCode);
dto.setM_name(m_name);
dto.setM_href(m_href);
dto.setLevel(level);
dto.setInnerCode(innerCode);
dto.setParentID(parentID);
}else{//更新失败
throw new SQLException();
}
//归还连接
ps.close();
update_con.close();
return dto;
}
4.删除功能
注意:删除功能不管是批量还是单个删除，如果子集有数据，子集连带删除
（1）批量删除
前端判断批量删除长度、问询是否要删除
#----- 当点击批量删除按钮时 表头工具栏监听
else if(eventStr=="deletes"){//批量删除
//获取选中的行数
var checkStatus = table.checkStatus('menuTableID'); //menuTableID 即为layui表格基础参数 id 对应的值
console.dir(checkStatus.data);
if(checkStatus.data.length>0){
//问询是否要删除
layer.confirm('确定要删除这【'+checkStatus.data.length+'】行?', {icon: 3, title:'删除提示'}, function(index){
var delRes = deleteMenus(checkStatus.data);//调用方法
if(delRes.code=="0000"){
layer.msg(delRes.msg, {icon: 1});
}else{
layer.msg(delRes.msg, {icon: 5});
}
layer.close(index);
});
}else{
layer.msg("请至少选择一行数据！", {icon: 5});
}
}
封装方法，进行批量删除
//批量删除数据
function deleteMenus(arr){
var res;
//组装数据到后端为id1,id2,id3的字符串
var ids = "";
for(var i in arr){
ids += arr[i].menuID+","
}
if(ids!=""){
ids = ids.substring(0,ids.length-1);
}
$.ajax({
url:"<%=basePath%>/deleteMenu",
type:"POST",
dataType:"JSON",
data:{"menuID":ids},
async:false,
success:function(data){
console.dir(data);
res = data;
},
error:function(XMLHttpRequest, textStatus, errorThrown){
console.dir("请求失败！");
}
});
return res;
}
(2)行删除
#点击行工具栏删除 行工具栏监听
else if(eventStr=="delete"){//删除
//问询删除
layer.confirm('确定要删除这【1】行?', {icon: 3, title:'删除提示'}, function(index){
//该删除方法需要一个数组就封装一个数组，就可以实现批量删除和单个删除共用一个方法了
var ids = [{"menuID":rowData.menuID}];
var delRes = deleteMenus(ids);//将封装的数组传入方法中
if(delRes.code=="0000"){
layer.msg(delRes.msg, {icon: 1});
}else{
layer.msg(delRes.msg, {icon: 5});
}
layer.close(index);
});
}
（3）后端删除实现（批量事务执行操作）
/**
* 删除菜单
* @param req 请求对象
* @param menuID 菜单id
* @return
*/
@Override
public int deleteMenu(HttpServletRequest req, String menuID) throws SQLException {
//获取连接
Connection con = MyC3P0Util.getCon();
String sql = "update t_menu set isDelete = 1 where innerCode in (\n" +
" select cc.* from (\n" +
"\t select innerCode from t_menu where innerCode \n" +
"like CONCAT((\n" +
" select zcx1.innerCode from t_menu as zcx1 where zcx1.menuID=? \n" +
"),'%')\n" +
"\t)as cc\n" +
")";
PreparedStatement ps = con.prepareStatement(sql);
//截取ids XX1,XX2,XX3
String[] split = menuID.split(",");
for(String id : split){
ps.setString(1,id);
ps.addBatch();
}
return ps.executeBatch().length;
}