因为自己对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" οnclick="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; "
οnclick="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"; }
2034
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
