问题
在工作中经常会遇到分页查询，我们都知道其实分页查询也很简单，思想都是类似的，不过你见过的分页方法有哪些呢？
详解
一、Easyui的分页查询
1、工具类：
Page.java：
public class Page {
private Integer page;
private Integer rows;
private Integer start;
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public void setStart(Integer start) {
this.start = start;
}
public Integer getRows() {
return rows;
}
public void setRows(Integer rows) {
this.rows = rows;
}
public Integer getStart() {
return (page-1) * rows;
}
}
EasyUIResult.java:
public class EasyUIResult {
private Integer total;
private List<?> rows;
public EasyUIResult(Integer total, List<?> rows) {
super();
this.total = total;
this.rows = rows;
}
public Integer getTotal() {
return total;
}
public void setTotal(Integer total) {
this.total = total;
}
public List<?> getRows() {
return rows;
}
public void setRows(List<?> rows) {
this.rows = rows;
}
}
2、代码使用
UserPage.java:
public class UserPage extends Page {
private String usercode;
public String getUsercode() {
return usercode;
}
public void setUsercode(String usercode) {
this.usercode = usercode;
}
}
UserController.java:
@RequestMapping("/list")
@ResponseBody
public EasyUIResult listAllUser(UserPage userPage){
List<User> list =userService.listAllUser(userPage);
Integer total = userService.findUserCount(userPage);
EasyUIResult result =new EasyUIResult(total, list);
return result;
}
UserServiceImpl.java:
public List<User> listAllUser(UserPage userPage){
return userMapper.findAllUsers(userPage);
}
public Integer findUserCount(UserPage userPage){
return userMapper.findUserCount(userPage);
}
UserMapper.java:
List<User> findAllUsers(UserPage userPage);
Integer findUserCount(UserPage userPage);
UserMapper.xml:
<select id="findAllUsers" resultMap="BaseResultMap" parameterType="com.pc.pojo.UserPage">
select
<include refid="Base_Column_List" />
from sys_user
<if test="usercode != null and usercode!=''" >
where usercode like '%${usercode}%'
</if>
limit #{start},#{rows}
</select>
<select id="findUserCount" resultType="java.lang.Integer" parameterType="com.pc.pojo.UserPage">
select
count(*)
from sys_user
<if test="usercode != null and usercode!=''" >
where usercode like '%${usercode}%'
</if>
</select>
3、总结
easyui的页面什么都不用管，因为他会向后台传递page(pc)、rows(ps),query(url),后台返回total(总页数)和rows(list)
二、url重写(页面上不显示查询条件，拼接在url上)(goods)
1、工具类
PageBean.java:
public class PageBean<T> {
private int pc;//当前页
private int tp;//总页数 TR/PS
private int tr;//总记录数
private int ps;//每页记录数
private String url;//查询条件
private List<T> beanList;//当前页数据
public int getPc() {
return pc;
}
public void setPc(int pc) {
this.pc = pc;
}
public int getTp() {
return tr%ps==0?tr/ps:tr/ps+1;
}
>>>>>>>>>>>>
get、set方法
>>>>>>>>>>>>
}
Expression.java:
public class Expression {
private String name;
private String operator;
private String value;
>>>>>>>>>>>>
get、set方法
>>>>>>>>>>>>
}
PageConstants.java:
public class PageConstants {
public static final int BOOK_PAGE_SIZE=12;
public static final int ORDER_PAGE_SIZE=5;
}
2、使用方法
BookServlet.java:
//单条件查询
public String findByBname(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//获得当前页
int pc =getPc(request);
//获得每页记录数
int ps = PageConstants.BOOK_PAGE_SIZE;
//获取查询参数
String bname = request.getParameter("bname");
//调用service获取pageBean
PageBean<Book> pageBean = bookService.findByBname(bname, pc, ps);
//获得url
String url =getUrl(request);
pageBean.setUrl(url);
request.setAttribute("pageBean", pageBean);
return"f:/jsps/book/list.jsp";
}
//组合查询
public String findByCombination(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//获得当前页
int pc =getPc(request);
//获得每页记录数
int ps = PageConstants.BOOK_PAGE_SIZE;
//获取查询参数
Book book =CommonUtils.toBean(request.getParameterMap(), Book.class);
//调用service获取pageBean
PageBean<Book> pageBean = bookService.findByCombination(book, pc, ps);
//获得url
String url =getUrl(request);
pageBean.setUrl(url);
request.setAttribute("pageBean", pageBean);
return"f:/jsps/book/list.jsp";
}
//获得url
private String getUrl(HttpServletRequest request) {
String url = request.getRequestURI()+"?"+request.getQueryString();
int index =url.indexOf("&pc=");
if(index!=-1){
url=url.substring(0, index);
}
return url;
}
//获得pc
private int getPc(HttpServletRequest request) {
int pc =1;
String param =request.getParameter("pc");
if(param!=null&&!param.trim().isEmpty()){
try{
pc = Integer.parseInt(param);
}catch(RuntimeException e){};
}
return pc;
}
BookService.java:
//单条件查询
public PageBean<Book> findByBname(String bname,int pc,int ps){
try {
return bookDao.findByBname(bname, pc, ps);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//组合查询
public PageBean<Book> findByCombination(Book criteria,int pc,int ps){
try {
return bookDao.findByCombination(criteria, pc, ps);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
BookDao.java
//单条件查询
public PageBean<Book> findByBname(String bnaem,int pc,int ps) throws SQLException{
List<Expression> expressions = new ArrayList<Expression>();
expressions.add(new Expression("bname", "like", "%"+bnaem+"%"));
return findByCriteria(expressions, pc, ps);
}
//组合查询
public PageBean<Book> findByCombination(Book criteria,int pc,int ps) throws SQLException{
List<Expression> exceptions = new ArrayList<Expression>();
exceptions.add(new Expression("bname","like","%"+criteria.getBname()+"%"));
exceptions.add(new Expression("author","like","%"+criteria.getAuthor()+"%"));
exceptions.add(new Expression("press","like","%"+criteria.getPress()+"%"));
return findByCriteria(exceptions, pc, ps);
}
//通用查询
public PageBean<Book> findByCriteria(List<Expression> expressions,int pc,int ps ) throws SQLException{
//拼接语句和参数
StringBuilder wheresql =new StringBuilder(" where 1=1");
List<Object> params = new ArrayList<Object>();
for (Expression expression : expressions) {
wheresql.append(" and "+expression.getName())
.append(" ").append(expression.getOperator()).append(" ");
if(!expression.getOperator().equals("is null")){
wheresql.append("?");
params.add(expression.getValue());
}
}
//总记录数
String sql="select count(*) from t_book"+wheresql;
Number number = (Number)qr.query(sql, new ScalarHandler(),params.toArray());
int tr=number.intValue();
//当前页记录
sql ="select * from t_book"+wheresql+" order by orderBy limit ?,?";
params.add((pc-1)*ps);
params.add(ps);
List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class),params.toArray());
//创建pagebean
PageBean<Book> pageBean = new PageBean<Book>();
pageBean.setPc(pc);
pageBean.setPs(ps);
pageBean.setTr(tr);
pageBean.setBeanList(beanList);
return pageBean;
}
pager.jsp:
%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<script type="text/javascript">
function _go() {
var pc = $("#pageCode").val();//获取文本框中的当前页码
if(!/^[1-9]d*$/.test(pc)) {//对当前页码进行整数校验
alert('请输入正确的页码！');
return;
}
if(pc > ${pageBean.tp}) {//判断当前页码是否大于最大页
alert('请输入正确的页码！');
return;
}
location = "${pageBean.url}&pc="+pc;
}
</script>
<div class="divBody">
<div class="divContent">
<%--上一页 --%>
<c:choose>
<c:when test="${pageBean.pc eq 1 }"><span class="spanBtnDisabled">上一页</span></c:when>
<c:otherwise> <a href="${pageBean.url }&pc=${pageBean.pc -1}" class="aBtn bold">上一页</a></c:otherwise>
</c:choose>
<%-- 计算begin和end --%>
<%-- 如果总页数<=6，那么显示所有页码，即begin=1 end=${pb.tp} --%>
<%-- 设置begin=当前页码-2，end=当前页码+3 --%>
<%-- 如果begin<1，那么让begin=1 end=6 --%>
<%-- 如果end>最大页，那么begin=最大页-5 end=最大页 --%>
<%-- 显示页码列表 --%>
<c:choose>
<c:when test="${pageBean.tp <= 6 }">
<c:set var="begin" value="1"/>
<c:set var="end" value="${pageBean.tp }"/>
</c:when>
<c:otherwise>
<c:set var="begin" value="${pageBean.pc-2 }"/>
<c:set var="end" value="${pageBean.pc + 3}"/>
<c:if test="${begin < 1 }">
<c:set var="begin" value="1"/>
<c:set var="end" value="6"/>
</c:if>
<c:if test="${end > pageBean.tp }">
<c:set var="begin" value="${pageBean.tp-5 }"/>
<c:set var="end" value="${pageBean.tp }"/>
</c:if>
</c:otherwise>
</c:choose>
<c:forEach begin="${begin }" end="${end }" var="i">
<c:choose>
<c:when test="${i eq pageBean.pc }">
<span class="spanBtnSelect">${i }</span>
</c:when>
<c:otherwise>
<a href="${pageBean.url }&pc=${i}" class="aBtn">${i }</a>
</c:otherwise>
</c:choose>
</c:forEach>
<%-- 显示点点点 --%>
<span class="spanApostrophe">...</span>
<%--下一页 --%>
<c:choose>
<c:when test="${pageBean.pc eq pageBean.tp }"><span class="spanBtnDisabled">下一页</span></c:when>
<c:otherwise><a href="${pageBean.url }&pc=${pageBean.pc+1}" class="aBtn bold">下一页</a> </c:otherwise>
</c:choose>
<%-- 共N页 到M页 --%>
<span>共${pageBean.tp }页</span>
<span>到</span>
<input type="text" class="inputPageCode" id="pageCode" value="${pageBean.pc }"/>
<span>页</span>
<a href="javascript:_go();" class="aSubmit">确定</a>
</div>
</div>
三、表单提交分页(在页面上设置表单和条件进行查询)
1、工具类：
PageHelper.java:
private Integer pageNum = 1;// 当前页数
private Integer pageSize = 10;// 每页显示条数
private Integer maxPageNum;// 总页数
private Integer dataTotal;// 总记录数
private List<?> list;//数据集合
>>>>>>>>>>>>
get、set方法
>>>>>>>>>>>>
public void setDataTotal(Integer dataTotal) {
this.dataTotal = dataTotal;
maxPageNum = (dataTotal + pageSize -1) / pageSize;
}
public Integer getLimitStart(Integer currentPage){
if (currentPage != null) {
this.pageNum = currentPage;
}
return (pageNum-1)*pageSize ;
}
BaseController.java:
略：
BaseService.java:
public abstract class BaseService<T> {
public abstract BaseMapper<T> setBaseMapper();
public int delete(Serializable id){
return this.setBaseMapper().deleteByPrimaryKey(id);
}
public int add(T t){
return this.setBaseMapper().insert(t);
}
public T get(Serializable id){
return this.setBaseMapper().selectByPrimaryKey(id);
}
public int update(T t){
return this.setBaseMapper().updateByPrimaryKey(t);
}
public int getCount(BaseQueryObject bqo){
return this.setBaseMapper().count(bqo);
}
public List<T> list(BaseQueryObject bqo){
return this.setBaseMapper().selectAll(bqo);
}
}
BaseMapper.java:
public interface BaseMapper<T>{
int deleteByPrimaryKey(Serializable id);
int insert(T t);
T selectByPrimaryKey(Serializable id);
int updateByPrimaryKey(T t);
int count(BaseQueryObject bqo);
List<T> selectAll(BaseQueryObject bqo);
}
2、使用方法：
ItemController.java:
@RequestMapping("/item")
@Controller
public class ItemController extends BaseController {
@RequiredLogin
@RequestMapping("/manage/list")
public String list(Integer currentPage, Model model, @ModelAttribute("iqm") ItemQueryModel iqm) {
PageHelper ph = new PageHelper();
iqm.setLimitStart(ph.getLimitStart(currentPage));//设置从哪开始
iqm.setPageSize(ph.getPageSize());//设置每页显示条数
List<Item> list = itemService.list(iqm);
int count = itemService.getCount(iqm);
ph.setDataTotal(count);//总记录数
ph.setList(list);
model.addAttribute("page", ph);
return "manage/item/list";
}
}
ItemService.java:
@Service
public class ItemService extends BaseService<Item>{
@Autowired
private ItemMapper itemMapper;
@Override
public BaseMapper<Item> setBaseMapper() {
return itemMapper;
}
public List<Item> findItemList(Integer category) {
return itemMapper.findItemList(category);
}
}
ItemMapper.java
public interface ItemMapper extends BaseMapper<Item>{
List<Item> findItemList(@Param("category") Integer category);
}
ItemMapper.xml
<resultMap id="BaseResultMap" type="com.shop.pojo.Item" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="category" property="category" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="introduction" property="introduction" jdbcType="VARCHAR" />
<result column="price" property="price" jdbcType="INTEGER" />
<result column="unit" property="unit" jdbcType="VARCHAR" />
<result column="num" property="num" jdbcType="INTEGER" />
<result column="pic_old_name" property="picOldName" jdbcType="VARCHAR" />
<result column="pic_new_name" property="picNewName" jdbcType="VARCHAR" />
<result column="descp" property="descp" jdbcType="VARCHAR" />
<result column="state" property="state" jdbcType="INTEGER" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="create_user" property="createUser" jdbcType="INTEGER" />
<result column="modify_time" property="modifyTime" jdbcType="TIMESTAMP" />
<result column="modify_user" property="modifyUser" jdbcType="INTEGER" />
<result column="delete_time" property="deleteTime" jdbcType="TIMESTAMP" />
<result column="delete_user" property="deleteUser" jdbcType="INTEGER" />
<result column="del_state" property="delState" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
id, category, name, introduction, price, unit, num, pic_old_name, pic_new_name, descp,
state, create_time, create_user, modify_time, modify_user, delete_time, delete_user,
del_state
</sql>
<sql id="base_where" >
<where>
del_state = 0
<if test="name != null" >
AND name LIKE concat('%',#{name},'%')
</if>
<if test="category != null" >
AND category = #{category}
</if>
<if test="state != null" >
AND state = #{state}
</if>
</where>
</sql>
<select id="selectAll" parameterType="com.shop.pojo.ItemQueryModel" resultMap="BaseResultMap">
SELECT <include refid="Base_Column_List" />
FROM item
<include refid="base_where" />
ORDER BY id DESC
<if test="limitStart >=0 and pageSize >=0">
LIMIT #{limitStart},#{pageSize}
</if>
</select>
<select id="count" parameterType="com.shop.pojo.UserinfoQueryModel" resultType="int">
SELECT COUNT(id) FROM item
<include refid="base_where" />
</select>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from item
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from item
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.shop.pojo.Item" >
insert into item (id, category, name,
introduction, price, unit,
num, pic_old_name, pic_new_name,
descp, state, create_time,
create_user, modify_time, modify_user,
delete_time, delete_user, del_state
)
values (#{id,jdbcType=INTEGER}, #{category,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR},
#{introduction,jdbcType=VARCHAR}, #{price,jdbcType=INTEGER}, #{unit,jdbcType=VARCHAR},
#{num,jdbcType=INTEGER}, #{picOldName,jdbcType=VARCHAR}, #{picNewName,jdbcType=VARCHAR},
#{descp,jdbcType=VARCHAR}, #{state,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP},
#{createUser,jdbcType=INTEGER}, #{modifyTime,jdbcType=TIMESTAMP}, #{modifyUser,jdbcType=INTEGER},
#{deleteTime,jdbcType=TIMESTAMP}, #{deleteUser,jdbcType=INTEGER}, #{delState,jdbcType=INTEGER}
)
</insert>
<update id="updateByPrimaryKey" parameterType="com.shop.pojo.Item" >
update item
set category = #{category,jdbcType=INTEGER},
name = #{name,jdbcType=VARCHAR},
introduction = #{introduction,jdbcType=VARCHAR},
price = #{price,jdbcType=INTEGER},
unit = #{unit,jdbcType=VARCHAR},
num = #{num,jdbcType=INTEGER},
pic_old_name = #{picOldName,jdbcType=VARCHAR},
pic_new_name = #{picNewName,jdbcType=VARCHAR},
descp = #{descp,jdbcType=VARCHAR},
state = #{state,jdbcType=INTEGER},
create_time = #{createTime,jdbcType=TIMESTAMP},
create_user = #{createUser,jdbcType=INTEGER},
modify_time = #{modifyTime,jdbcType=TIMESTAMP},
modify_user = #{modifyUser,jdbcType=INTEGER},
delete_time = #{deleteTime,jdbcType=TIMESTAMP},
delete_user = #{deleteUser,jdbcType=INTEGER},
del_state = #{delState,jdbcType=INTEGER}
where id = #{id,jdbcType=INTEGER}
</update>
<select id="findItemList" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List" />
FROM item
WHERE category = #{category} AND state = 0 AND del_state = 0
ORDER BY id DESC
LIMIT 0,4
</select>
3、页面
page.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!-- 分页 -->
<div class="dataTables_info">当前 <font color="red">${page.pageNum}</font>/${page.maxPageNum} 页</div>
<div class="dataTables_paginate paging_simple_numbers">
<a href='javascript:void(0)' class="paginate_button next disabled" id="first">首页</a>
<a href='javascript:void(0)' class="paginate_button previous disabled" id="pre">上一页</a>
<a href='javascript:void(0)' class="paginate_button next disabled" id="next">下一页</a>
<a href='javascript:void(0)' class="paginate_button next disabled" id="last">尾页</a>
<input type="text" class="input-text" id="skip" maxlength="4" style=" 50px;height: 26px">
<a href='javascript:void(0)' class="paginate_button next disabled" id="go">跳转</a>
</div>
<script>
$(function(){
//获取当前页
var currentPage = ${page.pageNum};
//获取最大页
var maxPage = ${page.maxPageNum};
//首页
$("#first").click(function(){
$("#currentPage").val(1);
$("#myForm").submit();
});
//上一页
$("#pre").click(function(){
if(currentPage <= 1){
currentPage = 1;
}else{
currentPage--;
}
$("#currentPage").val(currentPage);
$("#myForm").submit();
});
//下一页
$("#next").click(function(){
if(currentPage >= maxPage){
currentPage = maxPage;
}else{
currentPage++;
}
$("#currentPage").val(currentPage);
$("#myForm").submit();
});
//尾页
$("#last").click(function(){
$("#currentPage").val(maxPage);
$("#myForm").submit();
});
//输入跳转页
var v = 1;//默认值为1
$("#skip").keyup(function(){
//把非数字的都替换掉
$(this).val($(this).val().replace(/[^d]/g,""));
v = parseInt($(this).val(), 10);//转换成十进制数字
});
//跳转
$("#go").click(function(){
if($("#skip").val() == ''){
return;
}
if(v <= 1){
//首页
v = 1;
}
if (v >= maxPage){
//尾页
v = maxPage;
}
$("#currentPage").val(v);
$("#myForm").submit();
});
});
</script>
list.jsp:
<form action="${pageContext.request.contextPath}/item/manage/list" method="post" class="form form-horizontal responsive" id="myForm">
<input type="text" name="name" value="${iqm.name}" placeholder="商品名称" style="250px" class="input-text">
<select class="select" name="category">
<option value="">请选择</option>
<option value="0">新鲜水果</option>
<option value='1'>海鲜水产</option>
<option value='2'>猪肉牛肉</option>
<option value='3'>禽类蛋品</option>
<option value='4'>新鲜蔬菜</option>
<option value='5'>速冻食品</option>
</select>
<button class="btn btn-success" type="submit"><i class="Hui-iconfont"></i> 搜索</button>
<!-- 分页信息 -->
<input type="hidden" id="currentPage" name="currentPage" value="1">
</form>
<script type="text/javascript">
$("[name=category] option[value='${iqm.category}']").attr("selected","selected");
</script>
-----------------------------------------------------------------------------------------------------------------------------------------
<table class="table table-border table-bordered table-bg table-hover table-sort">
<thead>
<tr class="text-c">
<th width="20">序号</th>
<th width="80">商品类别</th>
<th width="150">商品名称</th>
<th width="80">单价(元)</th>
<th width="80">单位</th>
<th width="80">库存</th>
<th width="80">状态</th>
<th width="80">操作</th>
</tr>
</thead>
<tbody>
<c:forEach var="item" items="${page.list}" varStatus="i">
<tr class="text-c">
<td>${i.index+1}</td>
<td>
<c:if test="${item.category == 0}">新鲜水果</c:if>
<c:if test="${item.category == 1}">海鲜水产</c:if>
<c:if test="${item.category == 2}">猪肉牛肉</c:if>
<c:if test="${item.category == 3}">禽类蛋品</c:if>
<c:if test="${item.category == 4}">新鲜蔬菜</c:if>
<c:if test="${item.category == 5}">速冻食品</c:if>
</td>
<td>${item.name}</td>
<td><fmt:formatNumber value="${item.price / 100}" type="currency" pattern="#.00"/></td>
<td>${item.unit}</td>
<td>${item.num}</td>
<td>
<c:if test="${item.state == 0}">
<span class="label label-success radius">已上架</span>
</c:if>
<c:if test="${item.state == 1}">
<span class="label label-defaunt radius">未上架</span>
</c:if>
</td>
<td>
<c:if test="${item.state == 0}">
<a style="text-decoration:none" href="${pageContext.request.contextPath}/item/manage/editState?id=${item.id}&state=1" class="ml-5">
<i class="Hui-iconfont"></i>下架
</a>
</c:if>
<c:if test="${item.state == 1}">
<a style="text-decoration:none" href="${pageContext.request.contextPath}/item/manage/editState?id=${item.id}&state=0" class="ml-5">
<i class="Hui-iconfont"></i>上架
</a>
<a style="text-decoration:none" href="${pageContext.request.contextPath}/item/manage/add?id=${item.id}" class="ml-5">
<i class="Hui-iconfont"></i>编辑
</a>
<a style="text-decoration:none" class="ml-5" onClick="del(${item.id})" href="javascript:;">
<i class="Hui-iconfont"></i>删除
</a>
</c:if>
<a style="text-decoration:none" href="${pageContext.request.contextPath}/item/manage/detail?id=${item.id}" class="ml-5">
<i class="Hui-iconfont"></i>查看
</a>
</td>
</tr>
</c:forEach>
<c:if test="${page.list == null || fn:length(page.list) == 0}">
<tr>
<td style="text-align: center;" colspan="8">暂无数据</td>
</tr>
</c:if>
</tbody>
</table>
-------------------------------------------------------------------------------------------------------------------------------------
<!-- 分页信息-->
<c:if test="${page.list != null && fn:length(page.list) != 0}">
<jsp:include page="../../common/page.jsp" />
</c:if>
四、路径作为参数
1、工具类：
BaseController.java:
public class BaseController {
//分页
public Integer pageNum = 1;
public Integer pageCount = 20;//每页显示条数
public Integer maxPageNum;//总页数
public Integer dataTotal;//总记录数
//设置总条数
protected void setDataTotal(int dataTotal){
this.dataTotal = dataTotal ;
maxPageNum = (dataTotal + pageCount -1) / pageCount;
}
//前5后4的算法
protected int[] getBeginAndEnd(Integer currentPage) {
int[] pagelen=new int[2];
int totalPage = maxPageNum;
int begin = 1;
int end = totalPage == 0 ? 1 : totalPage;//如果最大页码数为0,则end=1，否则end=最大页码数,
if((end - begin) <= 9){
//显示十个页码，不足十个不做处理
}else{
if((currentPage - begin) >= 5){
if((currentPage+4) >= totalPage){
end = totalPage;
begin = end - 9;
}else{
end = currentPage + 4;
begin = currentPage -5;
}
}else{
if((currentPage -5) >= 1){
begin = currentPage - 5;
end = begin + 9;
}else{
begin = 1;
end=begin + 9;
}
}
}
pagelen[0] = begin;
pagelen[1] = end;
return pagelen;
}
}
PageVo.java:
public class PageVO {
private Integer start;//从哪开始
private Integer pageCount;//每页显示条数
private Integer type;//类型
private String q; //为查询扩展一个字段
>>>>>>>>>>>>>>>
setget
>>>>>>>>>>>>>>>
}
2、如何使用工具
HomeController.java:
@RequestMapping(value="/{type}/page/{currentPage}",method=RequestMethod.GET)
public String findItem(@PathVariable("type") int type,@PathVariable("currentPage")Integer currentPage,Model model){
//分页查询
PageVO pageVO = new PageVO();
pageVO.setStart((currentPage-1)*pageCount);//从哪开始
pageVO.setPageCount(pageCount);//每页显示条数
pageVO.setType(type);//模块类型
List<Home> list = homeService.getAllByHomeType(pageVO);
model.addAttribute("list", list);
model.addAttribute("item", Home.hometypeMap.get(type));
model.addAttribute("modular", "首页");//模块
//分页信息
//设置总数
Integer count = homeService.getCountByType(type);
setDataTotal(count);
int[] arr = getBeginAndEnd(currentPage);//起始页和结束页
int start = arr[0];//起始页
int end = arr[1];//结束页
model.addAttribute("start", start);
model.addAttribute("end", end);
model.addAttribute("type", type);//把type带到页面,为了分页请求
model.addAttribute("currentPage", currentPage);//把当前页带到页面,为了控制样式
return "home/item";
}
@RequestMapping("/search")
public String search(String q,Integer currentPage,Model model){
//分页查询
PageVO pageVO = new PageVO();
if (currentPage == null) {
currentPage = 1;
}
q = (q == null ? "请输入关键字" : q.trim());
pageVO.setStart((currentPage-1)*pageCount);//从哪开始
pageVO.setPageCount(pageCount);//每页显示条数
pageVO.setQ(q);
List<Home> list = homeService.getAllByTitle(pageVO);
model.addAttribute("list", list);
model.addAttribute("q", q);
//分页信息
//设置总数
Integer count = homeService.getCountByTitle(q);
setDataTotal(count);
int[] arr = getBeginAndEnd(currentPage);//起始页和结束页
int start = arr[0];//起始页
int end = arr[1];//结束页
model.addAttribute("start", start);
model.addAttribute("end", end);
model.addAttribute("currentPage", currentPage);//把当前页带到页面,为了控制样式
return "home/query";
}
HomeService.java:
public List<Home> getAllByHomeType(PageVO pageVO) {
return homeMapper.getAllByHomeType(pageVO);
}
public Integer getCountByType(int type) {
return homeMapper.getCountByType(type);
}
public List<Home> getAllByTitle(PageVO pageVO) {
return homeMapper.getAllByTitle(pageVO);
}
public Integer getCountByTitle(String title) {
return homeMapper.getCountByTitle(title);
}
HomeMapper.java:
public interface HomeMapper {
List<Home> getAllByHomeType(PageVO pageVO);
Integer getCountByType(int homeType);
List<Home> getAllByTitle(PageVO pageVO);
Integer getCountByTitle(String title);
}
HomeMaper.xml:
<select id="getAllByHomeType" parameterType="cn.hebust.web.util.PageVO" resultType="cn.hebust.web.pojo.Home">
SELECT * FROM home WHERE homeType = #{type} AND delState = 0 ORDER BY id DESC LIMIT #{start},#{pageCount};
</select>
<select id="getCountByType" parameterType="int" resultType="int">
SELECT COUNT(id) FROM home WHERE homeType = #{homeType} AND delState = 0 ;
</select>
<select id="getAllByTitle" parameterType="cn.hebust.web.util.PageVO" resultType="cn.hebust.web.pojo.Home">
<!-- ${}拼接符:字符串原样拼接,如果传入的参数是基本类型(string,long,double,int,boolean,float等),那么${}中的变量名称必须是value -->
SELECT * FROM home
<where>
delState = 0
<if test="q != null and q != '' ">
AND title LIKE concat('%',#{q},'%')
</if>
</where>
ORDER BY id DESC LIMIT #{start},#{pageCount};
</select>
<select id="getCountByTitle" parameterType="string" resultType="int">
SELECT COUNT(id) FROM home
<where>
delState = 0
<if test="value != null and value != '' ">
AND title LIKE concat('%',#{value},'%')
</if>
</where>
</select>