Javaweb分页功能

**

Dao类

**
package com.newer.dao;

import com.newer.model.Tb_Cate;
import com.newer.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PagingDao {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
public List<Tb_Cate> selectPage(int page,int pageSize) throws SQLException {
List<Tb_Cate> list=new ArrayList<>();
try {
conn=DBUtil.getconn();
String sql=" select * from (select row_number() over(order by CATE_ID)num,tb.* from TB_CATE tb)"+" temp where temp.num between ? and ? ";
ps=conn.prepareStatement(sql);
ps.setObject(1,(page-1)pageSize+1);
ps.setObject(2,pagepageSize);
rs=ps.executeQuery();
while (rs.next()){
Tb_Cate t=new Tb_Cate();
t.setCate_id(rs.getInt(“cate_id”));
t.setCate_name(rs.getString(“cate_name”));
t.setOri_price(rs.getInt(“ori_price”));
t.setCur_price(rs.getInt(“cur_price”));
t.setImg_path(rs.getString(“img_path”));
t.setDescript(rs.getString(“descript”));
list.add(t);
}

    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        DBUtil.closeAll(rs,ps,conn);
    }
    return list;
}
public int count() throws SQLException {
    try {
        conn=DBUtil.getconn();
        String sql="select count(*) from tb_cate";
        ps=conn.prepareStatement(sql);
        rs=ps.executeQuery();
        if(rs.next()){
            return rs.getInt(1);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        DBUtil.closeAll(rs,ps,conn);
    }
    return 0;
}

}

**

servlet

**
package com.newer.servlet;

import com.alibaba.druid.util.StringUtils;
import com.newer.dao.PagingDao;
import com.newer.model.Tb_Cate;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

@WebServlet("/paging")
public class PagingServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    req.setCharacterEncoding("UTF-8");
    resp.setContentType("text/html;charset=UTF-8");

    int pageSize=6;
    String page=req.getParameter("page");
    if(page==null||"0".equals(page)){
        page="1";
    }
    if(page!=null){
        int page1=Integer.parseInt(page);
    }

    PagingDao dao=new PagingDao();
    try {
        List<Tb_Cate> list = dao.selectPage(Integer.parseInt(page), pageSize);
        HttpSession session = req.getSession();
        session.setAttribute("tb_cate",list);
        session.setAttribute("page",page);
        session.setAttribute("spage",Integer.parseInt(page)-1);
        session.setAttribute("xpage",Integer.parseInt(page)+1);

        int count = dao.count();

        int pageCount=count%pageSize==0?count/pageSize:(count/pageSize)+1;
        session.setAttribute("lastpage",pageCount);
        req.setAttribute("list",list);
        session.setAttribute("totalcount",count);
        resp.sendRedirect("show.jsp");
    } catch (SQLException e) {
        e.printStackTrace();
    }

}

}

**

页面知识点

**
共$lastpage页</a><ahref="">首页</a><ahref="${pageContext.request.contextPath}/paging?page=$spage">上一页</a><ahref="${pageContext.request.contextPath}/paging?page=$xpage">下一页</a><ahref="${pageContext.request.contextPath}/paging?page=$lastpage">末页</a><a>第${page}页
共条${totalcount}记录