用户分页多条件查询
来源：互联网 发布：手机淘宝撤销投诉 编辑：程序博客网 时间：2024/06/05 19:22
因为自己对mybaties的查询语句还不熟悉，刚开始就没有想过把自己的一系列条件查询和分页显示联系在一起。在学长的点播下学会了灵活的应用sql语句
步骤
1.前端我为了美观去网上下载一个分页的jquary插件
<%@pagelanguage="java"import="java.util.*"pageEncoding="UTF-8" %>
<%@ taglibprefix="c"uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglibprefix="fmt"uri="http://java.sun.com/jsp/jstl/fmt" %>
<htmlxmlns="http://www.w3.org/1999/xhtml">
<head>
<metahttp-equiv="Content-Type"content="text/html;charset=utf-8"/>
<title>人力资源管理系统</title>
<scripttype="text/javascript"src="${pageContext.request.contextPath}/Js/timeFormat.js"></script>
<styletype="text/css">
<!--
body {
margin-left: 0px;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 0px;
}
.tabfont01{
font-family: "宋体";
font-size: 9px;
color: #555555;
text-decoration: none;
text-align: center;
}
.font051 {
font-family: "宋体";
font-size: 12px;
color: #333333;
text-decoration: none;
line-height: 20px;
}
.font201 {
font-family: "宋体";
font-size: 12px;
color: #FF0000;
text-decoration: none;
}
.button {
font-family: "宋体";
font-size: 14px;
height: 37px;
}
html {
overflow-x: auto;
overflow-y: auto;
border: 0;
}
</style>
<linkhref="${pageContext.request.contextPath}/css/css.css"rel="stylesheet"type="text/css"/>
<%--<script type="text/javascript"src="${pageContext.request.contextPath}/Js/jquery.js"></script>--%>
<linkrel="stylesheet"href="${pageContext.request.contextPath}/css/jquery.pagination.css"/>
<scripttype="text/javascript"src="${pageContext.request.contextPath}/Js/jquery-1.js"></script>
<%--<scriptsrc="http://libs.baidu.com/jquery/1.10.2/jquery.min.js"></script>--%>
<scriptsrc="${pageContext.request.contextPath}/Js/jquery.pagination.min.js"></script>
<linkhref="${pageContext.request.contextPath}/css/style.css"rel="stylesheet"type="text/css"/>
<style>
* {
margin: 0;
padding: 0;
}
body {
font-family: "微软雅黑";
background: #eee;
}
.box {
width: 800px;
margin: 100px auto 0;
height: 34px;
}
.page {
width: 600px;
}
.info {
width: 200px;
height: 34px;
line-height: 34px;
}
.fl {
float: left;
}
</style>
</head>
<body>
<tablewidth="100%"border="0"cellspacing="0"cellpadding="0">
<tr>
<tdheight="30">
<tablewidth="100%"border="0"cellspacing="0"cellpadding="0">
<tr>
<tdheight="62"background="${pageContext.request.contextPath}/images/nav04.gif"> </td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<tableid="subtree1"style="DISPLAY:" width="100%"border="0"cellspacing="0"cellpadding="0">
<tr>
<td>
<tablewidth="95%"border="0"align="center"cellpadding="0"cellspacing="0">
<tr>
<tdheight="20"><spanclass="newfont07">人员信息查看</span>
</td>
</tr>
<tr>
<tdheight="40"style="text-align:right">
<inputtype="button"onclick="delEdu()"value="批量删除"
style="color:#0080FF; border-radius:5px;vertical-align:middle;height:30px; width:70px;"/>
</td>
</tr>
<tr>
<td>
<selectname="jumpMenu"id="jumpMenu"
style="color:#0080FF; border-radius:5px;vertical-align:middle;height:30px; width:140px;">
<optionvalue="1">入职时间</option>
<optionvalue="2">出生日期</option>
</select>
<inputtype="date"value="${startTime}"id="startTime"
style="color:#0080FF; border-radius:5px;vertical-align:middle;height:30px; width:140px;"/>
<inputtype="date"value="${endTime}"id="endTime"
style="color:#0080FF; border-radius:5px;vertical-align:middle;height:30px; width:140px;"/>
<inputtype="text"placeholder="输入姓名关键字"value="${username}"id="nameIn"
style="color:#95a5a6; border-radius:5px;vertical-align:middle;height:30px; width:140px;"/>
<inputtype="button"value="搜索"
style="color:#0080FF; border-radius:5px;vertical-align:middle;height:30px; width:50px;"
onclick="lookup()"/>
</td>
</tr>
<tr>
<tdheight="40"class="font42">
<tablewidth="100%"border="0"cellpadding="4"cellspacing="1"bgcolor="#464646"
class="newfont03">
<thead>
<trclass="CTitle">
<tdheight="22"colspan="8"align="center"style="font-size:16px">人员信息列表
</td>
</tr>
<trbgcolor="#EEEEEE">
<tdwidth="4%"align="center"height="30">用户名</td>
<tdwidth="10%"align="center"height="30">真实姓名</td>
<tdwidth="10%"align="center">性别</td>
<tdwidth="10%"align="center">出生日期</td>
<tdwidth="10%"align="center">入职时间</td>
<tdwidth="15%"align="center">简介</td>
<tdwidth="12%"align="center">执行操作</td>
</tr>
</thead>
<%--<% Listlist=(List)request.getAttribute("user");--%>
<%--SimpleDateFormat simpleDateFormat=newSimpleDateFormat("yyyy-MM-dd");--%>
<%--if(list!=null&&list.size()>0) {--%>
<%--Iterator it = list.iterator();--%>
<%--while (it.hasNext()) {--%>
<%--Users u = (Users) it.next();--%>
<%--%>--%>
<tbodyid="usersTable">
<c:choose>
<c:whentest="${emptyuser}">暂时没有用户信息</c:when>
<c:otherwise>
<%--<c:forEachitems="${list.userList}" var="u">--%>
<c:forEachitems="${user}"var="u">
<trbgcolor="#FFFFFF"id="userDateFor">
<tdheight="22"align="center">
<c:outvalue="${u.username}"/>
</td>
<tdheight="22"align="center">
<c:outvalue="${u.name}"/>
</td>
<tdheight="22"align="center">
<c:choose>
<c:whentest="${u.sex==1}">
<c:outvalue="男"></c:out>
</c:when>
<c:otherwise>
<c:outvalue="女"></c:out>
</c:otherwise>
</c:choose>
<%--<%=newByte("1").equals(u.getSex())?"男":"女"%>--%>
</td>
<tdheight="22"align="center">
<fmt:formatDatevalue="${u.birthday}"
pattern="yyyy-MM-dd"/>
<%--<%=StringUtil.notNull(DateUtil.parseToString(u.getBirthday(),DateUtil.yyyyMMdd))%>--%>
</td>
<tdheight="22"align="center">
<fmt:formatDatevalue="${u.createtime}"
pattern="yyyy-MM-dd"/>
</td>
<tdheight="22"align="center">
<c:outvalue="${u.content}"/>
<%--<%=StringUtil.notNull(u.getContent())%> --%>
</td>
<tdheight="22"align="center">
<ahref="detailuserView.do?action=deleteUserView&id=<c:outvalue="${u.id}"/>">详情</a>
<ahref="modifyuser.do?action=modifyUser&id=<c:outvalue="${u.id}"/>">修改</a>
<ahref="deleteuser.do?action=deleteUser&id=<c:outvalue="${u.id}"/>">删除</a>
<inputtype='checkbox'name='isSelect'
value='<c:outvalue="${u.id}"/>'/>
</td>
<%--<td><ahref="/listuser.jsp">尚未评估</a></td>--%>
</tr>
</c:forEach>
</c:otherwise>
</c:choose>
</tbody>
<%--<%if(!"1".equals(u)){%><%}%>--%>
<%--<% }--%>
<%--}else{--%>
<%--%>--%>
<tr></tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<inputtype="hidden"id="tol"value="${tolcount}"/>
<inputtype="hidden"id="pagesize"value="${pageSize}"/>
<inputtype="hidden"id="pagenum"value="${pageNum}"/>
</table>
<divclass="box">
<divid="pagination1"class="page fl"></div>
<divclass="info fl">
<p>当前页数：<spanid="current1"class="currentpage">1</span></p>
</div>
</div>
<scripttype="text/JavaScript">
var toll = $('#tol').val();
var psize = 5;
var pNum = $('#pagenum').val();
var total = Math.ceil(Math.ceil(toll) / Math.ceil(psize));
$(function() {
$("#pagination1").pagination({
currentPage:pNum,
totalPage:total,
callback: function(current) {
// $("#current1").text(current)
pNum = current;
// location.href = '${pageContext.request.contextPath}/users/toListUser?pageNum='+pNum+"&pageSize="+psize;
$.ajax({
type:'POST',
cache:false,
url:'${pageContext.request.contextPath}/users/toListUser?pageNum='+ pNum + "&pageSize=" +psize+"&",
contentType:"application/json;charset=utf-8",
//data : {"name":name},
success:function(data) {
$("#subtree1").html(data);
$(".box").eq(0).css("display","none");
$(".currentpage").text(current)
}
});
}
});
});
function lookup() {
var btn = $('#nameIn').val();
var drop = $('#jumpMenuoption:selected').val();
var start = $('#startTime').val();
var end = $('#endTime').val();
if (btn== '') {
alert("请输入要查询的姓名!");
}
else {
location.href='${pageContext.request.contextPath}/users/toListUser?sort='+ drop + "&startTime=" + start +"&endTime=" + end + "&username="+btn;
}
}
</script>
</body>
</html>
2.写sql语句，一个sql语句是根据条件查询统计出的数据记录总条数，一个是查询出的用户具体信息，在测试的时候可以把mybaties语句变为一般的sql语句子啊mysql里面新建查询已验证自己写的语句是否有效。用户简介显示的5个字的摘要，如果简介小于5个字就显示简介全文。列表的多条件就写子啊<trim></tim>里面的if语句里面，当多条件不为空时就执行多条件里面条件，进行查询。
<!--where 1=1代表查询所有--><select id="selectAllUser" resultMap="users" parameterType="map"> select id,username,name,password,sex,birthday,createtime, CASE WHEN (length(content) <=5) THEN content ELSE CONCAT(left(content,5),'……') END as content from users where 1=1 <trim> <if test="username != null" > and username like concat(concat('%',#{username}),'%') </if> <if test="sort ==1 and starTime != null"> and createtime > #{starTime} </if> <if test="sort ==1 and endTime != null"> and createtime < #{endTime} </if> <if test="sort ==2 and starTime != null"> and birthday > #{starTime} </if> <if test="sort ==2 and endTime != null"> and birthday < #{endTime} </if> <if test="pageSize != null and pageNum != null"> limit #{pageNum} ,#{pageSize} </if> </trim></select><select id="selectAllCount" resultType="int" parameterType="map"> select count(*) from users where 1=1 <trim> <if test="username != null" > and username like concat(concat('%',#{username}),'%') </if> <if test="sort ==1 and starTime != null"> and createtime > #{starTime} </if> <if test="sort ==1 and endTime != null"> and createtime < #{endTime} </if> <if test="sort ==2 and starTime != null"> and birthday > #{starTime} </if> <if test="sort ==2 and endTime != null"> and birthday < #{endTime} </if> </trim></select>
注意：mybaties语句里面的大于小于符号是和sql里面不一样的，统计查询到的记录数量时，条件不能加上分页的查询条件。
3.controler里面的语句
@RequestMapping("/toListUser")public String toListUser(ModelMap model, String sort, String startTime ,String endTime,String username,String pageNum,String pageSize ) { List<Users> list = userService.selectUsers(sort,startTime,endTime,username, pageNum, pageSize); int count=userService.selectAllCount(sort,startTime,endTime,username); model.addAttribute("username",username); model.addAttribute("sort",sort); model.addAttribute("startTime",startTime); model.addAttribute("endTime",endTime); model.addAttribute("pageNum",pageNum); model.addAttribute("pageSize",pageSize); model.addAttribute("user", list); model.addAttribute("tolcount", count); return "/listuser";}
- 用户分页多条件查询
- yii 多条件查询，分页
- SSM分页、多条件查询
- JPA多条件查询+分页
- 分页&条件查询分页
- Grails 多条件查询和分页
- Oracle多条件查询分页存储过程
- Spring jpa data多条件分页查询
- 分页和多条件查询功能
- MVC 列表多条件动态查询分页
- 多条件查询无刷新分页
- 分页和多条件查询功能
- 多查询条件的MVC分页
- Hibernate多条件模糊分页查询
- ajax多条件查询动态分页
- MyBatis级联多条件分页查询
- 简单的多条件分页查询
- ajax 多条件模糊查询分页
- JS修改昵称
- 【CUGBACM15级BC第20场 A】hdu 5123 who is the best?
- ssh框架搭建出错
- Makefile :=和=区别
- 知识储备：02数组与字符串：判断元素出现与否及次数
- 用户分页多条件查询
- POJ2001 字典树
- POJ-1324:Holedox Moving(BFS+状态压缩+蛇蛇历险记)
- FCN和U-Net
- 经典试题一
- 深度优先搜索寻找割点
- python查看如何查看版本信息
- NOIP2016 天天爱跑步
- 六、改进神经网络的学习方法（2）：Softmax输出层
