PageHelper插件实现分页查询请戳这。
本篇在分页的基础上实现模糊查询，因为单表有点简单，直接介绍多表，输入关键字查找相关内容，之前几篇有点脱离毕设项目了，这篇开始尽量用项目做例子。没什么好说的，直接进入正文。
前置条件：耐性100%; IDEA2019; MySQL5.7; SSM框架; PageHelper分页; XML
预期效果（菜单管理为例）
- 模糊查询菜名中带有“肉”字且类型名带有“早餐”的数据；
- 这里样式用了bootstrap框架，可以忽视；
数据库创建表
Menu（菜单表）
type1(类型表)
type_menu(关联表)
常见的多对多查询
创建对应实体类
这里用了lombok，也可以自己写get/set方法
Menu类
package com.qiang.domain;
import lombok.Data;
import java.sql.Timestamp;
import java.util.List;
/**
* @author Mr.锵
* date 2020-02-21
*/
@Data
public class Menu {
private String menuid;
private String menuname;
private Float price;
private String Image;
private Timestamp createtime;
private Timestamp updatetime;
private Integer sell_num;
private String status;
private String description;
private Integer kucun;
}
Type类
package com.qiang.domain;
import lombok.Data;
import java.sql.Timestamp;
import java.util.List;
/**
* @author Mr.锵
* date 2020-02-21
*/
@Data
public class Type1 {
private String typeid;
private String name;
private String status;
private Timestamp createtime;
private Timestamp updatetime;
private List<Menu> menus;
}
TypeMenu类
package com.qiang.domain;
import lombok.Data;
/**
* @author Mr.锵
* date 2020-02-21
*/
@Data
public class TypeMenu {
private String typeid;
private String menuid;
}
编写Dao方法
联表查询建议使用xml实现，注释实现比较麻烦
ITypeDao接口
package com.qiang.dao;
import com.qiang.domain.Type1;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
* @author Mr.锵
* date 2020-02-23
*/
@Repository
public interface ITypeDao {
/**
* 分页模糊查询菜单类型
* @param tyname
* @param mname
* @return
*/
List<Type1> findTM(@Param("tyname") String tyname,@Param("mname") String mname);
}
resources对应路径下创建ITypeDao.xml
- resultMap标签中的property可以看做在代码中的变量名，column可以看做在sql语句中的参数名；
- resultMap标签中只需要将在sql语句查询出来的参数和代码中的变量匹配即可，不用全表匹配；
- select标签中进行模糊查询方法的实现；
- 判断当该参数不为空或者null是执行对应if标签下的条件；
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qiang.dao.ITypeDao">
<resultMap id="TMap" type="com.qiang.domain.Type1">
<id property="typeid" column="typeid"></id>
<result property="name" column="name"></result>
<collection property="menus" ofType="com.qiang.domain.Menu">
<id property="menuid" column="menuid"></id>
<result property="menuname" column="menuname"></result>
<result property="price" column="price"></result>
<result property="image" column="image"></result>
<result property="sell_num" column="sell_num"></result>
<result property="description" column="description"></result>
<result property="kucun" column="kucun"></result>
</collection>
</resultMap>
<select id="findTM" resultMap="TMap" parameterType="java.lang.String">
SELECT
t.*,
m.*
FROM
type1 t,
type_menu tm,
menu m
WHERE
t.typeid = tm.typeid
AND tm.menuid = m.menuid
AND t. STATUS = '上架'
AND m. STATUS = '上架'
<if test="tyname!=null and tyname!=''">
and t.name like '%${tyname}%'
</if>
<if test="mname!=null and mname!=''">
and m.menuname like '%${mname}%'
</if>
AND m.kucun > 0
</select>
</mapper>
编写service接口及实现类
因为是用PageHelper插件分页，所以还是要用PageInfo类型封装结果
接口
package com.qiang.service;
import com.github.pagehelper.PageInfo;
import com.qiang.domain.Type1;
import java.util.List;
/**
* @author Mr.锵
* date 2020-02-23
*/
public interface ITypeService {
/**
* 分页模糊查询菜单类型
* @param num
* @param tyname
* @param mname
* @return
*/
PageInfo<Type1> findPageTM(Integer num,Integer size,String tyname,String mname);
}
实现类
package com.qiang.service.impl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.qiang.dao.ITypeDao;
import com.qiang.domain.Type1;
import com.qiang.service.ITypeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author Mr.锵
* date 2020-02-23
*/
@Service("typeService")
public class ITypeServiceImpl implements ITypeService {
@Autowired
private ITypeDao typeDao;
@Override
public PageInfo<Type1> findPageTM(Integer num,Integer size,String tyname,String mname) {
PageHelper.startPage(num,size);
List<Type1> all = typeDao.findTM(tyname,mname);
PageInfo<Type1> tmPageInfo = new PageInfo<>(all);
return tmPageInfo;
}
}
编写控制器方法
- 这里默认size=5（即每页显示5条数据）;
- 因为是全局刷新，所以需要把前台拿到的输入框中的值封装进modelMap中返回给jsp页面；
- required设置为false，表示可以没有传值
package com.qiang.controller;
import com.github.pagehelper.PageInfo;
import com.qiang.domain.Menu;
import com.qiang.domain.Type1;
import com.qiang.service.ITypeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
/**
* @author Mr.锵
* date 2020-02-24
*/
@Controller
@RequestMapping("/type")
public class TypeController {
@Autowired
private ITypeService typeService;
@RequestMapping("/findPageTM")
public ModelAndView findPageTM(@RequestParam(required = false,defaultValue ="1") Integer num,
@RequestParam(required = false,defaultValue ="5") Integer size,
@RequestParam(required = false) String tyname,
@RequestParam(required = false) String mname){
ModelMap modelMap = new ModelMap();
PageInfo<Type1> tmlist = typeService.findPageTM(num,size,tyname,mname);
modelMap.addAttribute("tmlist",tmlist);
modelMap.addAttribute("tyname",tyname);
modelMap.addAttribute("mname",mname);
ModelAndView typemenulist = new ModelAndView("typemenulist", modelMap);
return typemenulist;
}
}
编写jsp页面
a标签的href属性需要获取输入框中的值，不然会出现模糊查询之后按页码跳转会变回查询所有的情况；
<%--
Created by IntelliJ IDEA.
User: Administrator
Date: 2020/3/12
Time: 11:17
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html lang="zh-CN">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
<title>typemenu</title>
<link rel="stylesheet" href="../css/bootstrap.css" type="text/css">
<style>
body{
background-color: whitesmoke;
}
table{
table-layout:fixed;
}
td{
overflow:hidden;
white-space:nowrap;
text-overflow:ellipsis;
}
.inner-container {
position: absolute; left: 0;
overflow-x: hidden;
overflow-y: scroll;
}
/* for Chrome 只针对谷歌浏览器*/
.inner-container::-webkit-scrollbar {
display: none;
}
.right{
float: right;
padding-right: 50px;
}
</style>
<script type="text/javascript" src="../js/jquery.min.js"></script>
<script type="text/javascript" src="../js/bootstrap.min.js"></script>
<script>
$(function () {
$("#front").click(function () {
$("#front").attr("href","../type/findPageTM?num=${tmlist.prePage}&&tyname="+$("#tyname").val()+"&&mname="+$("#mname").val()+"")
});
$("#first").click(function () {
$("#first").attr("href","../type/findPageTM?num=1&&tyname="+$("#tyname").val()+"&&mname="+$("#mname").val()+"")
});
$("#last").click(function () {
$("#last").attr("href","../type/findPageTM?num=${tmlist.pages}&&tyname="+$("#tyname").val()+"&&mname="+$("#mname").val()+"")
});
$("#next").click(function () {
$("#next").attr("href","../type/findPageTM?num=${tmlist.nextPage}&&tyname="+$("#tyname").val()+"&&mname="+$("#mname").val()+"")
});
})
function gonum(num) {
$(".aurlcenter").attr("href","../type/findPageTM?num="+num+"&&tyname="+$("#tyname").val()+"&&mname="+$("#mname").val()+"")
}
</script>
</head>
<body class="inner-container">
<div class="table-responsive col-sm-12">
<table class="table table-striped table-bordered table-hover">
<thead>
<tr>
<td>类名</td>
<td>菜名</td>
<td>操作</td>
</tr>
<tr>
<form action="../type/findPageTM">
<td><input class="form-control" placeholder="输入类名查询" name="tyname" id="tyname" value="${tyname}"></td>
<td><input class="form-control" placeholder="输入菜名查询" name="mname" id="mname" value="${mname}"></td>
<td><button type="submit" class="btn btn-success" id="pagebtn">查询</button></td>
</form>
</tr>
</thead>
<tbody>
<c:forEach items="${tmlist.list}" var="typemenu">
<c:forEach items="${typemenu.menus}" var="menu">
<tr>
<td title="${typemenu.name}">${typemenu.name}</td>
<td title="${menu.menuname}">${menu.menuname}</td>
<td><a href="../typemenu/deletetypemenu?typeid=${typemenu.typeid}&&menuid=${menu.menuid}" class="btn btn-danger">解绑</a></td>
</tr>
</c:forEach>
</c:forEach>
</tbody>
</table>
第${tmlist.pageNum}页，共${tmlist.pages}页/${tmlist.total}条
<%-- 分页条--%>
<nav aria-label="Page navigation" class="right">
<ul class="pagination">
<c:choose>
<c:when test="${tmlist.isFirstPage}">
<li class="disabled"><a href="#" aria-label="Previous"><span aria-hidden="true">«</span></a></li>
<li class="disabled"><a href="#" aria-label="Previous"><span aria-hidden="true">首</span></a></li>
</c:when>
<c:otherwise>
<li>
<a href="" aria-label="Previous" id="front">
<span aria-hidden="true">«</span>
</a>
</li>
<li>
<a href="" aria-label="Previous" id="first">
<span aria-hidden="true">首</span>
</a>
</li>
</c:otherwise>
</c:choose>
<c:forEach items="${tmlist.navigatepageNums}" var="num">
<c:choose>
<c:when test="${num==tmlist.pageNum}">
<li class="active">
<span>${num}<span class="sr-only">(current)</span></span>
</li>
</c:when>
<c:otherwise>
<li><a href="" class="aurlcenter" onclick='gonum("${num}")'>${num}</a></li>
</c:otherwise>
</c:choose>
</c:forEach>
<c:choose>
<c:when test="${tmlist.isLastPage}">
<li class="disabled"><a href="#" aria-label="Next"><span aria-hidden="true">尾</span></a></li>
<li class="disabled"><a href="#" aria-label="Next"><span aria-hidden="true">»</span></a></li>
</c:when>
<c:otherwise>
<li>
<a href="" aria-label="Next" id="last">
<span aria-hidden="true">尾</span>
</a>
</li>
<li>
<a href="" aria-label="Next" id="next">
<span aria-hidden="true">»</span>
</a>
</li>
</c:otherwise>
</c:choose>
</ul>
</nav>
</div>
</body>
</html>
结尾
模糊查询不难，就是允许模糊查询的变量越多，分页跳转时携带的值就可能越多，暂时没找到更简便的查询方法。
感觉有用就点个赞呗~
下一篇写一下ajax局部刷新
扫一扫
1378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
