分页插件pagehelper.jar:
https://oss.sonatype.org/content/repositories/releases/com/github/pagehelper/pagehelper/
http://repo1.maven.org/maven2/com/github/pagehelper/pagehelper/
由于使用了sql解析工具,你还需要下载jsqlparser.jar
4.1.0及以后版本需要0.9.4版本
http://repo1.maven.org/maven2/com/github/jsqlparser/jsqlparser/0.9.4/
4.1.0以前版本需要0.9.1版本
http://repo1.maven.org/maven2/com/github/jsqlparser/jsqlparser/0.9.1/
2.在spring-mybatis添加配置
<!-- spring和MyBatis完美整合,不需要mybatis的配置映射文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- 自动扫描mapping.xml文件 -->
<property name="mapperLocations" value="classpath:cxc/hotel/mapping/*.xml"></property>
<!-- 引入分页插件 -->
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<!-- config params as the following -->
<value>
helperDialect=oracle
</value>
</property>
</bean>
</array>
</property>
</bean>
注意:不能少<value>,我写的时候少了,就一直报错,找了很久才发现是自己写掉了
3.建立所需要的类:
1)页面上查询信息封装的实体类:QueryRecord.java
package cxc.hotel.entity;
import java.io.Serializable;
import java.util.Date;
/*
* 用于封装查询登录信息的实体类
*/
public class QueryRecord implements Serializable{//实体类中尽量使用包装类,如果返回字段值为null,int类型会报错,Integer不会报错
private static final long serialVersionUID = 8460668120409851975L;
private String username;//操作员用户名
private Date startTime;
private Date endTime;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getStartTime() {
return startTime;
}
public void setStartTime(Date startTime) {
this.startTime = startTime;
}
public Date getEndTime() {
return endTime;
}
public void setEndTime(Date endTime) {
this.endTime = endTime;
}
@Override
public String toString() {
return "QueryRecord [username=" + username + ", startTime=" + startTime
+ ", endTime=" + endTime + "]";
}
}
2)查询的每一条结果封装的实体类:
package cxc.hotel.entity;
import java.io.Serializable;
import java.util.Date;
/*
* 操作员登录信息实体类
*/
public class LoginRecord implements Serializable{//实体类中尽量使用包装类,如果返回字段值为null,int类型会报错,Integer不会报错
private static final long serialVersionUID = 8460668120409851985L;
private Integer id;//操作员id
private String username;//操作员用户名
private String loginip;//登录ip
private Date logintime;//登录的时间
public LoginRecord(){
}
public LoginRecord(Integer id, String username, String loginip,Date logintime) {
super();
this.id = id;
this.username = username;
this.loginip = loginip;
this.logintime = logintime;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getLoginip() {
return loginip;
}
public void setLoginip(String loginip) {
this.loginip = loginip;
}
public Date getLogintime() {
return logintime;
}
public void setLogintime(Date logintime) {
this.logintime = logintime;
}
@Override
public String toString() {
return "LoginRecord [id=" + id + ", username=" + username
+ ", loginip=" + loginip + ", logintime=" + logintime + "]";
}
}
3).持久层--->控制层
3.1)FindLoginRecordMapper.xml
<?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="cxc.hotel.dao.FindLoginRecordDao" >
<resultMap id="BaseResultMap" type="cxc.hotel.entity.LoginRecord" >
<id property="id" column="id" jdbcType="INTEGER" /> <!-- 前面是实体类的字段,后面是数据库的字段,一一对应 -->
<result property="username" column="username" jdbcType="VARCHAR" />
<result property="loginip" column="loginip" jdbcType="VARCHAR" />
<result property="logintime" column="logintime" jdbcType="DATE" />
</resultMap>
<!-- 将查询的结果分页处理 -->
<select id="LoginRecordPageInfo" resultMap="BaseResultMap" parameterType="cxc.hotel.entity.QueryRecord">
select * from tb_loginrecored
where 1=1
<if test="username!='' and username!=null">
AND username LIKE concat(concat('%',#{username}),'%')
</if>
<if test="startTime!='' and startTime!=null">
AND logintime >=#{startTime}
</if>
<if test="endTime!='' and endTime!=null">
AND logintime <=#{endTime}
</if>
order by logintime desc
</select>
</mapper>
注意:1)在mapper.xml中,>是>;,<是<----------2)在oracle数据库中不支持concat的三个参数的拼接,故使用三个参数用上面的方式
3.2)FindLoginRecordDao.java
package cxc.hotel.dao;
import java.util.Date;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import cxc.hotel.entity.LoginRecord;
import cxc.hotel.entity.QueryRecord;
public interface FindLoginRecordDao {
/** 方法名 与 UserMapper.xml 文件中的 sql语句的 id 相同*/
/**该方法用于查询登录记录*/
List<LoginRecord> LoginRecordPageInfo(QueryRecord queryRecord);
}
3.3)LoginRecordService.java
package cxc.hotel.service;
import com.github.pagehelper.PageInfo;
import cxc.hotel.entity.LoginRecord;
import cxc.hotel.entity.QueryRecord;
public interface LoginRecordService {
//得到登录记录的分页信息,查询信息封装成的一个对象,当前页数,每页多少条
public PageInfo<LoginRecord> getLoginRecordPageInfo(QueryRecord queryRecord,Integer currentPage,Integer pageSize);
}
3.4)LoginRecordServiceImpl.java
package cxc.hotel.service.impl;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import cxc.hotel.dao.FindLoginRecordDao;
import cxc.hotel.entity.LoginRecord;
import cxc.hotel.entity.QueryRecord;
import cxc.hotel.service.LoginRecordService;
@Service
public class LoginRecordServiceImpl implements LoginRecordService{
//得到登录记录的分页信息,查询信息封装成的一个对象,当前页数,每页多少条
@Resource
private FindLoginRecordDao findLoginRecorddao;
//传入的三个参数,查询的条件,第几页,每页多少条数据
public PageInfo<LoginRecord> getLoginRecordPageInfo(QueryRecord queryRecord,Integer currentPage,Integer pageSize){
//第几页,每页多少条数据
PageHelper.startPage(currentPage,pageSize);
//1.根据条件查询所有满足查询条件的结果集
List<LoginRecord> list=findLoginRecorddao.LoginRecordPageInfo(queryRecord);
PageInfo<LoginRecord> pageInfo=new PageInfo<LoginRecord>(list);
//2.把查询出来的结果集封装成PageInfo对象
return pageInfo;
}
}
3.5)LoginUserController.java
@Controller
@RequestMapping("/user")
public class LoginUserController {
@Resource
private LoginRecordService loginRecordService;
//该方法用于form表单提交 Date类型数据绑定
@InitBinder
public void initBinder(WebDataBinder binder) {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
dateFormat.setLenient(false);
binder.registerCustomEditor(Date.class, new CustomDateEditor(dateFormat, true));
}
//该方法用于管理员查询用户的登录记录
@RequestMapping(value = "/userRecord")
public String userRecord(HttpServletRequest request,Model model,QueryRecord queryRecord) throws Exception {
String page = request.getParameter("currentPage");
if (StringUtils.isEmpty(page)) {
page = "1";
}
//LoginUser user = (LoginUser) SecurityUtils.getSubject().getSession().getAttribute("currentUser");
PageInfo<LoginRecord> pageInfo = null;
pageInfo = loginRecordService.getLoginRecordPageInfo(queryRecord,Integer.parseInt(page),SystemConstant.PageSize.PAGESIZE_10);
model.addAttribute("queryRecord",queryRecord);
//model.addAttribute("user", user);
model.addAttribute("pageInfo", pageInfo);
return "userRecord";
}
}
4.userRecord.jsp
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ include file="/WEB-INF/jsp/taglib.jsp"%>
<%@ taglib prefix="p" uri="/WEB-INF/page.tld"%>
<script type="text/javascript"> var ctx = "${ctx}";</script><!-- 方便后面使用,直接使用ctx即可 -->
<script src="${ctx}/script/jquery-1.11.1.js"></script>
<script src="${ctx}/script/jquery.json-2.4.min.js"></script>
<script src="${ctx}/script/My97DatePicker/WdatePicker.js"></script><!-- 引入时间插件 -->
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>便捷酒店管理系统</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" type="text/css" href="${ctx}/style/middle.css" />
</head>
<body >
<div class="container" >
<div class="codrops-top">
<a href="">
<strong>点击刷新当前页面 </strong>
</a>
<span class="right">
<a href="https://blog.csdn.net/qq_39986274/">
<strong>跳转到开发者博客</strong>
</a>
</span>
<div class="clr"></div>
</div>
<form:form modelAttribute="queryRecord" class="form" id="userForm" action="${ctx}/user/userRecord.do" method="post"
style="height: 420px;width: 900px; color:#ffffff;">
<div> 用户名:
<form:input id="username" path="username" type="text" style="width: 120px;" />
起始日期:
<!-- <input type="text" class="Wdate" id="startTime"
onClick="WdatePicker({autoPickDate:true,dateFmt:'yyyy-MM-dd',maxDate:'#F{$dp.$D(\'endTime\')||\'new Date()\'}',readOnly:true})" style="width: 120px;"/> -->
<form:input id="startTime" path="startTime" style="width: 120px;" class="Wdate" />
截止日期:
<!-- <input type="text" class="Wdate" id="endTime"
onClick="WdatePicker({autoPickDate:true,dateFmt:'yyyy-MM-dd',minDate:'#F{$dp.$D(\'startTime\')}',maxDate:new Date(),readOnly:true})" style="width: 120px;"/> -->
<form:input id="endTime" path="endTime" style="width: 120px;" class="Wdate" />
<input type="button" class="btn" id="queryButton" value="点击查询" />
</div>
<br/>
<table class="welcometable" id="tb" width="850px" >
<tr height="30px" >
<td width="180px" rowspan="12" align="center" >登录记录</td>
<th width="200px"> 用户名</th>
<th width="260px"> 登录时间</th>
<th width="210px"> 登录IP</th>
<th width="150px" > 登录次数</th>
</tr>
<tr height="28px">
<td> ${pageInfo.list[0].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[0].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[0].loginip}</td>
<td rowspan="10" align="center">${pageInfo.getTotal()}次</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[1].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[1].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[1].loginip}</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[2].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[2].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[2].loginip}</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[3].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[3].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[3].loginip}</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[4].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[4].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[4].loginip}</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[5].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[5].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[5].loginip}</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[6].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[6].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[6].loginip}</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[7].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[7].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[7].loginip}</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[8].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[8].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[8].loginip}</td>
</tr>
<tr height="28px">
<td> ${pageInfo.list[9].username}</td>
<td> <fmt:formatDate value="${pageInfo.list[9].logintime}" pattern="yyyy-MM-dd HH:mm:ss" /></td>
<td> ${pageInfo.list[9].loginip}</td>
</tr>
<tr height="50px" >
<td colspan="4">
<!-- 自定义p标签,用于分页 -->
<div class="c m_t20">
<p:cxc> </p:cxc>
</div>
</td>
</tr>
</table>
</form:form>
</div>
</body>
<footer class="main-footer" >
<strong height="10px">Copyright © 2018-2020 <a href="https://blog.csdn.net/qq_39986274/">程绪才</a>.</strong>
------------------------------------------------
All rights reserved.
------------------------------------------------------------
<b height="10px">Version</b> 1.0
</footer>
</html>
<script language="javascript" type="text/javascript">
$(function() {
$("#startTime").bind("click", {
'startId' : 'startTime',
'endId' : 'endTime'
}, dateStart);
$("#endTime").bind("click", {
'startId' : 'endTime',
'endId' : 'startTime'
}, dateEnd);
})
function dateStart(obj) {
var sId = obj.data.startId;
var eId = obj.data.endId;
var maxda = "";
if ($("#" + eId).val() == "") {
maxda = '%y-%M-%d';
} else {
maxda = '#F{$dp.$D(\'' + eId + '\')}';
}
WdatePicker({
el : sId,
autoPickDate : true,
minDate : '#F{$dp.$D(\'' + eId + '\',{d:-30});}',
maxDate : maxda,
dateFmt : 'yyyy-MM-dd'
});
}
//结束时间
function dateEnd(obj) {
var Id = obj.data.startId;
var eId = obj.data.endId;
var maxda = "";
if ($("#" + eId).val() != "" && DateDiff($("#" + eId).val())) {
maxda = '#F{$dp.$D(\'' + eId + '\',{d:+30});}';
} else {
maxda = '%y-%M-%d';
}
WdatePicker({
el : Id,
autoPickDate : true,
minDate : '#F{$dp.$D(\'' + eId + '\')}',
maxDate : maxda,
dateFmt : 'yyyy-MM-dd'
});
}
function DateDiff(str1) {
var date1 = str1.split("-");
var strDate1 = new Date(date1[0], date1[1] - 1, date1[2]);
var strDate2 = new Date();// 当前时间
return (strDate2.getTime() - strDate1.getTime()) / 1000 / 60 / 60 / 24 > 30;
}
$(function(){
$("#queryButton").bind("click", query);
})
function query() {
$("#userForm").submit();
}
function formSubmit() {
query();
}
</script>
该处时间插件自行在网上下载
5.page.tld
<?xml version="1.0" encoding="UTF-8" ?>
<taglib xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-jsptaglibrary_2_0.xsd"
version="2.0">
<description>cxc page tag</description>
<display-name>page tag</display-name>
<tlib-version>1.0</tlib-version>
<short-name>cxc</short-name>
<tag>
<name>cxc</name>
<tag-class>cxc.hotel.entity.PageTag</tag-class>
<body-content>JSP</body-content>
<attribute>
<name>formIndex</name>
<required>false</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
</tag>
</taglib>
6.PageTag.java
package cxc.hotel.entity;
import java.io.IOException;
import javax.servlet.jsp.JspException;
import javax.servlet.jsp.tagext.TagSupport;
import com.github.pagehelper.PageInfo;
public class PageTag extends TagSupport {
private static final long serialVersionUID = 5678672299634741305L;
private Long formIndex = 0L;// 表单索引
@Override
public int doAfterBody() throws JspException {
return TagSupport.SKIP_BODY;
}
@Override
public int doStartTag() throws JspException {
try {
PageInfo pageInfo = new PageInfo();
Object page = pageContext.getRequest().getAttribute("pageInfo");
if (page != null) {
pageInfo = (PageInfo) page;
}
StringBuilder sb = new StringBuilder();
sb.append("<div>");
sb.append("<span class='page_left fl'> 有 <font color='red'>");
sb.append(pageInfo.getTotal());//总记录数
sb.append("</font> 条记录,当前第 <font color='red'>");
if (pageInfo.getPages() > 0) {//总页数
sb.append(pageInfo.getPageNum());//当前页
} else {
sb.append(0);
}
sb.append("</font>/<font color='red'>");
sb.append(pageInfo.getPages());
sb.append("</font> 页,每页记录数 <font color='red'>");
sb.append(pageInfo.getPageSize());
sb.append("</font> 条 <input type='hidden' id='currentPage' name='currentPage' value='1'></span>");
sb.append("<span class='page_right fr'>");
if (pageInfo.getPageNum() == 1
|| pageInfo.getPageNum() == 0
|| pageInfo.getPages() == 0) {
sb.append("首页 | 上页 ");// 判断是否为首页
} else {
sb.append("<a href=\"javascript:page_doSubmint('frist');\">首页</a>");
sb.append("| <a href=\"javascript:page_doSubmint('up');\">上页</a>");
}
if (pageInfo.getPageNum() == pageInfo.getPages()
|| pageInfo.getPages() == 0) {
sb.append("| 下页 | 末页 ");// 判断是否为末页
} else {
sb.append("| <a href=\"javascript:page_doSubmint('down');\">下页</a>");
sb.append("| <a href=\"javascript:page_doSubmint('last');\">末页</a>");
}
sb.append(" 跳转到第 <input id='jump' value='"
+ pageInfo.getPageNum()
+ "' style='width:50px;text-align:center;'"
+ " class='jump' οnkeyup='validate.clearNoInt(this)'/>");
sb.append(" 页 ");
if (pageInfo.getPages() < 2) {
sb.append("跳转");
} else {
sb.append("<a href=\"javascript:page_jump();\">跳转</a>");
}
sb.append(" </span></div>");
sb.append("<script type='text/javascript'>");
sb.append("function page_doSubmint(opr){");
sb.append("var currentPage=");
sb.append(pageInfo.getPageNum());
sb.append(";");
sb.append("var currentPagehidden=document.getElementById('currentPage');");
sb.append("if(opr=='frist'){");// 首页判断
sb.append("currentPagehidden.value=1;");
sb.append("}else if(opr=='up'){");// 上一页判断
sb.append("currentPagehidden.value=currentPage-1;");
sb.append("}else if(opr=='down'){");// 下一页判断
sb.append("currentPagehidden.value=currentPage+1;");
sb.append("}else if(opr=='last'){");// 最后一页判断
sb.append("currentPagehidden.value=" + pageInfo.getPages()
+ ";");
sb.append("}else{");// 跳转处理
sb.append("currentPagehidden.value=opr;");
sb.append("}");
sb.append("return formSubmit()");
sb.append("}");
sb.append("function page_jump(){");// 跳转函数
sb.append("var jump=document.getElementById('jump').value;");
sb.append("if(isNaN(jump)||jump==''){");// 非数字及为空验证(将其置空并直接返回不做处理)
sb.append("document.getElementById('jump').value='';");
sb.append("return;");
sb.append("}");
sb.append("page_doSubmint(jump);");
sb.append("}");
sb.append("</script>");
pageContext.getOut().println(sb.toString());
} catch (IOException e) {
e.printStackTrace();
}
return EVAL_BODY_INCLUDE;
}
@Override
public int doEndTag() throws JspException {
return EVAL_PAGE;
}
public Long getFormIndex() {
return formIndex;
}
public void setFormIndex(Long formIndex) {
this.formIndex = formIndex;
}
}
7.遇到的问题
7.1)spring-mybatis中添加pageHelper配置时,少添加<value>一直报错
7.2)在mapper.xml中,模糊查询oracle中不支持concat的三个参数的拼接,所以一直报Cause: java.sql.SQLSyntaxErrorException: ORA-00909: 参数个数无效
7.3)前台输入汉字的时候,后台接收的username一直乱码
解决办法:在web.xml中添加
<filter>
<filter-name>encodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
<init-param>
<param-name>forceEncoding</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
解决乱码相关链接:点击打开链接
7.4)form表单提交--包含时间类型的数据
解决方法相关链接:点击打开链接
8.运行程序的截图
Mybatis分页插件的使用
相关链接1:点击打开链接
相关链接2:点击打开链接