day18_查询功能 合并servlet
1.sql分析
-- 分页+条件 查询
select am1.*,IFNULL(am2.menuname,'无') as pname from admin_menu am1 left join admin_menu am2 on am1.pid = am2.mid-- 动态查询条件
where am1.menuname like CONCAT('%','理','%')and am1.pid = 13
-- page 当前页 pageSize 每页多少条 (page-1)*pageSize ,pageSizelimit 0,10-- 总记录数 (查询条件需要与分段查询的保持一致 保持数据一致性)
select count(0) from admin_menu am1
where am1.menuname like CONCAT('%','理','%')and am1.pid = 13
注意点:
-- 1.查询语句需要考虑到关联数据的易用性 (提高用户体验)
-- 2.分页+条件 查询
-- 3.分页组件或者功能 经常需要总记录数total 通过total+pageSize 算总记录数-- 算总页数 是否能整除 能整除直接整除
-- 有余数 除完加一页
总页数计算公式
@Testpublic void myTest(){/** -- totalpage 总页数-- total 总记录数-- pageSize 每页多少条* */Integer total = 35;Integer pageSize = 7;Integer totalPage = total%pageSize==0?total/pageSize:total/pageSize+1;System.out.println(totalPage);}
2.查询功能dao和测试
两个查询方法 分段记录 和统计总记录数
//查询分段记录 (带limit)
List<AdminMenu> listMenuByCondition(@Param("inputMenu") AdminMenu inputMenu,@Param("startIdx")Integer startIdx,@Param("pageSize")Integer pageSize);<select id="listMenuByCondition" resultMap="AdminMenuBaseMap">select am1.*,IFNULL(am2.menuname,'无') as pname from admin_menu am1 left join admin_menu am2 on am1.pid = am2.mid<where><if test="inputMenu.menuname != null and inputMenu.menuname !=''">am1.menuname like CONCAT('%',#{inputMenu.menuname},'%')</if><if test="inputMenu.pid != null ">and am1.pid = #{inputMenu.pid}</if></where>limit #{startIdx},#{pageSize}</select>
//统计总记录数Integer countMenuByCondition(@Param("inputMenu") AdminMenu inputMenu);<select id="countMenuByCondition" resultType="java.lang.Integer">select count(0) from admin_menu am1<where><if test="inputMenu.menuname != null and inputMenu.menuname !=''">am1.menuname like CONCAT('%',#{inputMenu.menuname},'%')</if><if test="inputMenu.pid != null ">and am1.pid = #{inputMenu.pid}</if></where></select>
注意:
查询时 保持查询条件一致
附加知识(了解):
java实体对象使用过程中 基础用法使用一个实体对象
为了体现模式化 可能会出现同一个实体类建立多个实体对象的情况
3查询接口
先考虑对接的数据
传入数据 menuname 菜单名称pid 上级编号page 当前第几页 必填项 设定默认值 可以变成非必填项pageSize 每页多少条 必填项 设定默认值 可以变成非必填项传出数据 json
package com.javasm.controller;import com.alibaba.fastjson.JSON;
import com.javasm.entity.AdminMenu;
import com.javasm.entity.PageInfo;
import com.javasm.entity.Result;
import com.javasm.entity.ReturnCode;
import com.javasm.service.AdminMenuService;
import com.javasm.service.impl.AdminMenuServiceImpl;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;/*** @className: MenuServlet* @description:* @author: gfs* @date: 2025/8/18 10:40* @version: 0.1* @since: jdk17*/
@WebServlet("/menus")
public class MenuServlet extends HttpServlet {@Overrideprotected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {//允许跨域访问暂时写在公共⽗类中// /* 允许跨域的主机地址*/resp.setHeader("Access-Control-Allow-Origin", "http://localhost:5173");/* 允许跨域的请求⽅法GET, POST, HEAD 等*/resp.setHeader("Access-Control-Allow-Methods", "*");/* 重新预检验跨域的缓存时间(s) */resp.setHeader("Access-Control-Max-Age", "3600");/* 允许跨域的请求头*/resp.setHeader("Access-Control-Allow-Headers", "*");/* 是否携带cookie */resp.setHeader("Access-Control-Allow-Credentials", "true");//1接参数 封对象//页码参数String pageStr = req.getParameter("page");String pageSizeStr = req.getParameter("pageSize");//把page pageSize 做成非必填项 有默认值Integer page = 1;if(pageStr!=null&&!"".equals(pageStr)){page = Integer.valueOf(pageStr);}Integer pageSize = 10;if(pageSizeStr!=null&&!"".equals(pageSizeStr)){pageSize = Integer.valueOf(pageSizeStr);}//查询参数String menuname = req.getParameter("menuname");String pidStr = req.getParameter("pid");Long pid = null;if(pidStr!=null&&!"".equals(pidStr)){pid = Long.valueOf(pidStr);}AdminMenu inputMenu = new AdminMenu(menuname, pid);//2调用serviceAdminMenuService adminMenuService = new AdminMenuServiceImpl();Integer total = adminMenuService.countMenuByCondition(inputMenu);List<AdminMenu> adminMenus = adminMenuService.listMenuByCondition(inputMenu, page, pageSize);//3根据执行结果返回数据Result result = new Result();if(adminMenus.size()>0){result.setCode(ReturnCode.QUERY_SUCCESS.getCode());result.setMsg(ReturnCode.QUERY_SUCCESS.getMsg());//给table 菜单列表 adminMenus//给分页组件 页面信息对象 pageInfoPageInfo pageInfo = new PageInfo(page,pageSize,total);result.setPageInfo(pageInfo);result.setReturnData(adminMenus);}else{result.setCode(ReturnCode.QUERY_NODATA.getCode());result.setMsg(ReturnCode.QUERY_NODATA.getMsg());}resp.setContentType("application/json;charset=utf-8");PrintWriter writer = resp.getWriter();writer.print(JSON.toJSONString(result));writer.close();}
}
注意:
查询分段记录的service中 要把page转成startIdx
@Overridepublic List<AdminMenu> listMenuByCondition(AdminMenu inputMenu, Integer page, Integer pageSize) {SqlSession sqlSession = MyBatisHealper.getSqlSession();AdminMenuDao mapper = sqlSession.getMapper(AdminMenuDao.class);//把page 转成limit语句 需要的 起始索引 (page-1)*pageSizeList<AdminMenu> adminMenus = mapper.listMenuByCondition(inputMenu,(page-1)*pageSize,pageSize);MyBatisHealper.backAndSaveSqlSession(sqlSession);return adminMenus;}@Overridepublic Integer countMenuByCondition(AdminMenu inputMenu) {SqlSession sqlSession = MyBatisHealper.getSqlSession();AdminMenuDao mapper = sqlSession.getMapper(AdminMenuDao.class);Integer total = mapper.countMenuByCondition(inputMenu);MyBatisHealper.backAndSaveSqlSession(sqlSession);return total;}
4查询页面
联调和测试流程
从简单到完整功能
1.先把table数据对接上
2.美化调整table
3.加入分页组件 控制table数据重新查询
4.加入条件查询
5.测试数据和修复bug
注意 :
json对象组合语法
let josn1 = {name:'jack',age:15};let json2 = {page:1,pageSize:10};console.log(josn1);console.log(json2);console.log({...josn1,...json2});
查询页面
<template><el-form ref="queryFormRef" :model="queryForm" :inline="true" ><el-form-item label="菜单名称" prop="menuname"><el-input v-model="queryForm.menuname" style="width: 240px;" placeholder="Approved by" clearable /></el-form-item><el-form-item label="上级菜单" prop="pid"><el-selectv-model="queryForm.pid"placeholder="请选择"style="width: 240px;"clearable><!-- 1.写死数据 --><!-- 2.动态加载 配一个菜单数据接口 --><el-option label="无" value="0" /><el-option v-for="option in menuList" :label="option.menuname" :value="option.mid" /></el-select></el-form-item><el-form-item><el-button type="primary" @click="querySubmit">查询</el-button><el-button @click="queryReset">重置</el-button></el-form-item></el-form><el-table height="500" :data="tableData.dataList" style="width: 100%"><el-table-column prop="mid" label="菜单编号" width="80" /><el-table-column prop="menuname" label="菜单名称"/><el-table-column prop="pid" label="上级编号" ><!-- 通过自定义列信息 美化页面 scope.row 当前行的所有数据 --><template #default="scope"><el-tag v-if="scope.row.pid == 0" type="success">{{ scope.row.pid }}</el-tag><el-tag v-else type="warning">{{ scope.row.pid }}</el-tag></template></el-table-column><el-table-column prop="pname" label="上级名称" ><template #default="scope"><el-tag v-if="scope.row.pid == 0" type="success">{{ scope.row.pname }}</el-tag><el-tag v-else type="warning">{{ scope.row.pname }}</el-tag></template></el-table-column><el-table-column prop="url" label="访问地址" /><el-table-column prop="glyphicon" label="图标" ><template #default="scope"><el-tag type="primary"><el-icon> <component :is="scope.row.glyphicon"></component> </el-icon> </el-tag><el-tag type="primary">{{ scope.row.glyphicon }}</el-tag></template></el-table-column></el-table><el-paginationv-model:current-page="tableData.pageInfo.page"v-model:page-size="tableData.pageInfo.pageSize":total="tableData.pageInfo.total":page-sizes="[10, 20, 30]"layout="total, sizes, prev, pager, next, jumper"@current-change="handlePageChange"@size-change="handleSizeChange"/></template><script setup>
import { ref,reactive,onMounted } from 'vue'
import {Check,Delete,Edit,Message,Search,Star,Share} from '@element-plus/icons-vue'
import {myGet,myPost} from '@/myaxios'
//如果有后端数据 需要覆盖 为了保持响应时对象 需要这样建立
//table数据 dataList
//页码信息数据 pageInfo
const tableData = reactive({dataList:[],pageInfo:{ page:4,pageSize:20,total:78}
})
//查询表单
const queryForm = reactive({menuname:'',pid:''
})//查询表单对象
const queryFormRef = ref()//查询表单 下拉列表数据
const menuList = ref([])//提交查询
const querySubmit = ()=>{let params = queryForm;//发送请求myQuery(params)}
//重置查询
const queryReset = ()=>{queryFormRef.value.resetFields()//重新查询数据myQuery({})
}//page改变
const handlePageChange = (currentPage)=>{console.log(currentPage);//修改page页面参数tableData.pageInfo.page = currentPage;//把改后的page 作为参数 通过请求传走let params1 = tableData.pageInfo;let params2 = queryForm;//json对象组合语法let allParam = {...params1,...params2}//发送请求myQuery(allParam)
}
//pageSiez改变
const handleSizeChange = (currentPageSize)=>{//修改page页面参数tableData.pageInfo.page = 1;tableData.pageInfo.pageSize = currentPageSize;//把改后的page 作为参数 通过请求传走let params1 = tableData.pageInfo;let params2 = queryForm;//json对象组合语法let allParam = {...params1,...params2}//发送请求myQuery(allParam)}//查询公共方法
const myQuery = (params)=>{myGet("/menus",params).then(resp=>{//多做日志输出 定位错误//打断点 分析错误 debuggerconsole.log(resp.data);if(resp.data.code == 20000){tableData.dataList = resp.data.returnDatatableData.pageInfo = resp.data.pageInfo}else if(resp.data.code == 20001){tableData.dataList = []tableData.pageInfo = {page:1,pageSize:10,total:0}}})
}
//查询一级菜单下拉列表
const queryMenuLevel1 = ()=>{myGet("/listMenuLevel1",{}).then(resp=>{menuList.value = resp.data.returnData})
}onMounted(()=>{myQuery({})queryMenuLevel1()
})
</script><style scoped></style>
5合并servlet
通过url体现执行的模块和方法
package com.javasm.controller;import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;/*** @className: BaseServlet* @description:* @author: gfs* @date: 2025/8/18 16:04* @version: 0.1* @since: jdk17*/
public class BaseServlet extends HttpServlet {@Overrideprotected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {/** service方法中 自定请求分发规则** 1.通过传参 + 自定义参数标记* 做方法分支* String doWhat = req.getParameter("doWhat");if("query".equals(doWhat)){this.listMenu(req,resp);}else if("listSelect".equals(doWhat)){this.listMenuLevel1(req,resp);}* 2.rest风格 http协议规定的处理方式* get 用于查询* post 用于修改* put 用于添加* delete 用于删除** 3.以url最后一段 写需要调用的方法名* 通过反射 做方法的通用调用方法 直接调用出入的方法名** *///允许跨域访问暂时写在公共⽗类中// /* 允许跨域的主机地址*/resp.setHeader("Access-Control-Allow-Origin", "http://localhost:5173");/* 允许跨域的请求⽅法GET, POST, HEAD 等*/resp.setHeader("Access-Control-Allow-Methods", "*");/* 重新预检验跨域的缓存时间(s) */resp.setHeader("Access-Control-Max-Age", "3600");/* 允许跨域的请求头*/resp.setHeader("Access-Control-Allow-Headers", "*");/* 是否携带cookie */resp.setHeader("Access-Control-Allow-Credentials", "true");System.out.println("baseServlet.........");String methodName = req.getRequestURI().substring(req.getRequestURI().lastIndexOf("/")+1);System.out.println(methodName);try {//根据方法名 获取方法对象Method method = this.getClass().getDeclaredMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);//可以获取到非public修饰符method.setAccessible(true);//调用方法method.invoke(this,req,resp);} catch (NoSuchMethodException e) {//throw new RuntimeException(e);resp.sendError(404,"loc error");} catch (InvocationTargetException e) {throw new RuntimeException(e);} catch (IllegalAccessException e) {throw new RuntimeException(e);}}}