day18_查询功能 合并servlet
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 ,pageSize
limit 0,10
-- 总记录数 (查询条件需要与分段查询的保持一致 保持数据一致性)
select count(0) from admin_menu am1
where am1.menuname like CONCAT('%','理','%')
and am1.pid = 13
注意点:
-- 1.查询语句需要考虑到关联数据的易用性 (提高用户体验)
-- 2.分页+条件 查询
-- 3.分页组件或者功能 经常需要总记录数total 通过total+pageSize 算总记录数
-- 算总页数 是否能整除 能整除直接整除
-- 有余数 除完加一页
总页数计算公式
@Test
public 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 {
@Override
protected 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调用service
AdminMenuService 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
//给分页组件 页面信息对象 pageInfo
PageInfo 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
@Override
public List<AdminMenu> listMenuByCondition(AdminMenu inputMenu, Integer page, Integer pageSize) {
SqlSession sqlSession = MyBatisHealper.getSqlSession();
AdminMenuDao mapper = sqlSession.getMapper(AdminMenuDao.class);
//把page 转成limit语句 需要的 起始索引 (page-1)*pageSize
List<AdminMenu> adminMenus = mapper.listMenuByCondition(inputMenu,(page-1)*pageSize,pageSize);
MyBatisHealper.backAndSaveSqlSession(sqlSession);
return adminMenus;
}
@Override
public 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-select
v-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-pagination
v-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=>{
//多做日志输出 定位错误
//打断点 分析错误 debugger
console.log(resp.data);
if(resp.data.code == 20000){
tableData.dataList = resp.data.returnData
tableData.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 {
@Override
protected 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);
}
}
}
浙公网安备 33010602011771号