本文介绍了一个使用MyBatis实现分页及多条件查询的具体案例，包括前端页面设计、SQL语句编写及后端Controller层的实现。
因为自己对mybaties的查询语句还不熟悉，刚开始就没有想过把自己的一系列条件查询和分页显示联系在一起。在学长的点播下学会了灵活的应用sql语句
步骤
1.前端我为了美观去网上下载一个分页的jquary插件
<%@page
language="java"
import="java.util.*"
pageEncoding="UTF-8"
%>
<%@ taglib
prefix="c"
uri="http://java.sun.com/jsp/jstl/core"
%>
<%@ taglib
prefix="fmt"
uri="http://java.sun.com/jsp/jstl/fmt"
%>
<html
xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta
http-equiv="Content-Type"
content="text/html;charset=utf-8"/>
<title>人力资源管理系统</title>
<script
type="text/javascript"src="${pageContext.request.contextPath}/Js/timeFormat.js"></script>
<style
type="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>
<link
href="${pageContext.request.contextPath}/css/css.css"
rel="stylesheet"
type="text/css"/>
<%--<script type="text/javascript"src="${pageContext.request.contextPath}/Js/jquery.js"></script>--%>
<link
rel="stylesheet"
href="${pageContext.request.contextPath}/css/jquery.pagination.css"/>
<script
type="text/javascript"src="${pageContext.request.contextPath}/Js/jquery-1.js"></script>
<%--<scriptsrc="http://libs.baidu.com/jquery/1.10.2/jquery.min.js"></script>--%>
<script
src="${pageContext.request.contextPath}/Js/jquery.pagination.min.js"></script>
<link
href="${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>
<table
width="100%"
border="0"
cellspacing="0"
cellpadding="0">
<tr>
<td
height="30">
<table
width="100%"
border="0"
cellspacing="0"
cellpadding="0">
<tr>
<td
height="62"
background="${pageContext.request.contextPath}/images/nav04.gif"> </td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table
id="subtree1"
style="DISPLAY:
" width="100%"
border="0"
cellspacing="0"
cellpadding="0">
<tr>
<td>
<table
width="95%"
border="0"
align="center"
cellpadding="0"
cellspacing="0">
<tr>
<td
height="20"><span
class="newfont07">人员信息查看</span>
</td>
</tr>
<tr>
<td
height="40"
style="text-align:
right">
<input
type="button"
onclick="delEdu()"
value="批量删除"
style="color:#0080FF;
border-radius:5px;
vertical-align:middle;height:30px;
width:70px;
"/>
</td>
</tr>
<tr>
<td>
<select
name="jumpMenu"
id="jumpMenu"
style="color:#0080FF;
border-radius:5px;
vertical-align:middle;height:30px;
width:140px;
">
<option
value="1">入职时间</option>
<option
value="2">出生日期</option>
</select>
<input
type="date"
value="${startTime}"
id="startTime"
style="color:#0080FF;
border-radius:5px;
vertical-align:middle;height:30px;
width:140px;
"/>
<input
type="date"
value="${endTime}"
id="endTime"
style="color:#0080FF;
border-radius:5px;
vertical-align:middle;height:30px;
width:140px;
"/>
<input
type="text"
placeholder="输入姓名关键字"
value="${username}"
id="nameIn"
style="color:#95a5a6;
border-radius:5px;
vertical-align:middle;height:30px;
width:140px;
"/>
<input
type="button"
value="搜索"
style="color:#0080FF;
border-radius:5px;
vertical-align:middle;height:30px;
width:50px;
"
onclick="lookup()"/>
</td>
</tr>
<tr>
<td
height="40"
class="font42">
<table
width="100%"
border="0"
cellpadding="4"
cellspacing="1"
bgcolor="#464646"
class="newfont03">
<thead>
<tr
class="CTitle">
<td
height="22"
colspan="8"
align="center"
style="font-size:16px">人员信息列表
</td>
</tr>
<tr
bgcolor="#EEEEEE">
<td
width="4%"
align="center"
height="30">用户名</td>
<td
width="10%"
align="center"
height="30">真实姓名</td>
<td
width="10%"
align="center">性别</td>
<td
width="10%"
align="center">出生日期</td>
<td
width="10%"
align="center">入职时间</td>
<td
width="15%"
align="center">简介</td>
<td
width="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();--%>
<%--%>--%>
<tbody
id="usersTable">
<c:choose>
<c:when
test="${empty
user}">暂时没有用户信息</c:when>
<c:otherwise>
<%--<c:forEachitems="${list.userList}" var="u">--%>
<c:forEach
items="${user}"
var="u">
<tr
bgcolor="#FFFFFF"
id="userDateFor">
<td
height="22"
align="center">
<c:out
value="${u.username}"/>
</td>
<td
height="22"
align="center">
<c:out
value="${u.name}"/>
</td>
<td
height="22"
align="center">
<c:choose>
<c:when
test="${u.sex==1}">
<c:out
value="男"></c:out>
</c:when>
<c:otherwise>
<c:out
value="女"></c:out>
</c:otherwise>
</c:choose>
<%--<%=newByte("1").equals(u.getSex())?"男":"女"%>--%>
</td>
<td
height="22"
align="center">
<fmt:formatDate
value="${u.birthday
}"
pattern="yyyy-MM-dd"/>
<%--<%=StringUtil.notNull(DateUtil.parseToString(u.getBirthday(),DateUtil.yyyyMMdd))%>--%>
</td>
<td
height="22"
align="center">
<fmt:formatDate
value="${u.createtime
}"
pattern="yyyy-MM-dd"/>
</td>
<td
height="22"
align="center">
<c:out
value="${u.content}"/>
<%--<%=StringUtil.notNull(u.getContent())%> --%>
</td>
<td
height="22"
align="center">
<a
href="detailuserView.do?action=deleteUserView&id=<c:out
value="${u.id}"/>">详情</a>
<a
href="modifyuser.do?action=modifyUser&id=<c:out
value="${u.id}"/>">修改</a>
<a
href="deleteuser.do?action=deleteUser&id=<c:out
value="${u.id}"/>">删除</a>
<input
type='checkbox'
name='isSelect'
value='<c:out
value="${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>
<input
type="hidden"
id="tol"
value="${tolcount}"/>
<input
type="hidden"
id="pagesize"
value="${pageSize}"/>
<input
type="hidden"
id="pagenum"
value="${pageNum}"/>
</table>
<div
class="box">
<div
id="pagination1"
class="page fl"></div>
<div
class="info fl">
<p>当前页数：<span
id="current1"
class="currentpage">1</span></p>
</div>
</div>
<script
type="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 =
$('#jumpMenu
option: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"; }
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
