IDEA数据库分页实现

这只是一个个人笔记，敬明天的我。

首先因为总数据这个要经常用到，所以先建一个查询总数据的工具类。

public class TotalCount {
    //获取数据总数量
    public static PreparedStatement pstm = null;
    public static ResultSet rs = null;
    public static Object[] params = {};

    public static int getTotalCount(Connection connection,String sql) throws SQLException {
        int count = -1;
        if (connection!=null){
            rs = BaseDao.execute(connection,pstm,rs,sql,params);
            if (rs.next()){
                count = rs.getInt(1);
            }
        }
        BaseDao.closeResource(null,pstm,rs);
        return count;
    }
} 

Dao层获取总数

//查询数据总数
    public int getCount(Connection connection) throws SQLException {
        String sql = "select count(1) from chaindx_user";
        int count = TotalCount.getTotalCount(connection,sql);
        return count;
    }

Dao层获取查询指定页集合

//查询当前页的数据集合
    public List<User> getUserList(Connection connection, int nowPage,int pageSize) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int sum = nowPage*pageSize;
        Object[] params = {sum,pageSize};
        List<User> userList = new ArrayList<>();
        String sql = "select * from chaindx_user limit ?,?";
        if (connection!=null){
            rs = BaseDao.execute(connection,pstm,rs,sql,params);
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setAge(rs.getInt("age"));
                user.setSex(rs.getString("sex"));
                user.setBirth(rs.getString("birth"));
                user.setNumber(rs.getInt("number"));
                user.setAddress(rs.getString("address"));
                userList.add(user);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return userList;
    }

service查询总数

public int getTotalCount() {
        Connection connection = null;
        SysDao sysDao = new SysDaoImpl();
        int count = 0;
        try {
            connection = BaseDao.getConnection();
            count = sysDao.getCount(connection);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return count;
    }

service当前页的数据集合

public List<User> userList(int nowPage, int pageSize) {
        Connection connection = null;
        SysDao sysDao = new SysDaoImpl();
        List<User> userList= new ArrayList<>();
        try {
            connection = BaseDao.getConnection();
            userList = sysDao.getUserList(connection, nowPage, pageSize);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return userList;
    }

servlet层实现

public class PageServlet extends HttpServlet {
    @Override
    //分页
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        int currentPage = 0;
        String currentPage1 = req.getParameter("currentPage");
        String pageSize1 = req.getParameter("pageSize");
        
        if (!StringUtils.isNullOrEmpty(currentPage1)){
            currentPage = Integer.parseInt(currentPage1)-1;
        }


        SysService sysService = new SysServiceImpl();
        PageUtil pages = new PageUtil();

        if (!StringUtils.isNullOrEmpty(pageSize1)){
            int pageSize = Integer.parseInt(pageSize1);
            Constants.session = pageSize;
            if (pageSize<=0){
                pageSize = 1;
                Constants.session = 1;
            }
            pages.setPageSize(pageSize);
        }else {
            pages.setPageSize(Constants.session);
        }


        pages.setTotalCount(sysService.getTotalCount());

        if (currentPage<0){
            currentPage=0;
        }else if (currentPage>pages.getTotalPage()){
            currentPage = pages.getTotalPage();
        }
        pages.setCurrentPage(currentPage);


        List<User> userList = sysService.userList(currentPage, pages.getPageSize());
        pages.setUserList(userList);


        req.setAttribute("pages",pages);
        req.getRequestDispatcher("jsp/select.jsp").forward(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        super.doPost(req, resp);
    }

}

PageUtil工具类

public class PageUtil {
    private int currentPage;
    private int pageSize;
    private List<User> userList;
    private int totalCount;
    private int totalPage;

    public PageUtil() {
    }

    public PageUtil(int currentPage, int pageSize, List<User> userList, int totalCount, int totalPage) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.userList = userList;
        this.totalCount = totalCount;
        this.totalPage = totalPage;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public List<User> getUserList() {
        return userList;
    }

    public void setUserList(List<User> userList) {
        this.userList = userList;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
        this.totalPage = totalCount%pageSize==0?totalCount/pageSize-1:totalCount/pageSize;
    }

    public int getTotalPage() {
        return totalPage;
    }


}

Jsp实现

<%@ page import="com.shuang.pojo.User" %>
<%@ page import="java.util.List" %>
<%@ page import="com.shuang.servlet.sysuser.PageServlet" %>
<%@ page import="com.shuang.util.PageUtil" %>
<%@ page contentType="text/html;charset=UTF-8" language="java"
         pageEncoding="UTF-8" %>
<%@include file="/jsp/common/head2.jsp"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<link rel="stylesheet" type="text/css" href="../css/pwd.css">
<link rel="stylesheet" type="text/css" href="../css/table.css">
<div class="all">
    <div class="right">
        <a href="/jsp/sysform.jsp" class="add">添加</a>
        <div class="providerAdd">
            <center>
                <table>
                    <tr>
                        <td>Id</td>
                        <td>用户名</td>
                        <td>姓名</td>
                        <td>密码</td>
                        <td>性别</td>
                        <td>年龄</td>
                        <td>出生日期</td>
                        <td>地址</td>
                        <td>操作</td>
                    </tr>

                    <%
                        PageUtil pages = (PageUtil) request.getAttribute("pages");
                        for (User user:pages.getUserList()){
                    %>
                    <tr>
                            <td><%=user.getId()%></td>
                            <td><%=user.getUserCode()%></td>
                            <td><%=user.getUsername()%></td>
                            <td><%=user.getPassword()%></td>
                            <td><%=user.getSex()%></td>
                            <td><%=user.getAge()%></td>
                            <td><%=user.getBirth()%></td>
                            <td><%=user.getAddress()%></td>
                            <td><a href="/jsp/select.do?id=<%=user.getId()%> " class="operate c_ff8">修改</a>&nbsp;&nbsp;
                                <a href="#" class="operate c_6d">删除</a>
                            </td>
                        </tr>
                    <%}%>
                </table>

                <form action="/pages.do" method="get">
                    <input type="submit" value="变更页面容量"><input type="text" name="pageSize" value="<%=pages.getPageSize()%>">
                    <input type="submit" value="跳转到"><input type="text" name="currentPage" value="<%=pages.getCurrentPage()+1%>">
                </form>
                <% if (pages.getCurrentPage()==0){%>
                     <a href="/pages.do?currentPage=<%=pages.getCurrentPage()+2%>">下一页</a>
                     <a href="/pages.do?currentPage=<%=pages.getTotalPage()+1%>">尾页</a>
                <% }else if(pages.getCurrentPage()==pages.getTotalPage()){%>
                     <a href="/pages.do?currentPage=0">首页</a>
                     <a href="/pages.do?currentPage=<%=pages.getCurrentPage()%>">上一页</a>
                <% }else {%>
                     <a href="/pages.do?currentPage=0">首页</a>
                     <a href="/pages.do?currentPage=<%=pages.getCurrentPage()%>">上一页</a>
                     <a href="/pages.do?currentPage=<%=pages.getCurrentPage()+2%>">下一页</a>
                     <a href="/pages.do?currentPage=<%=pages.getTotalPage()+1%>">尾页</a>
                <%}%>
            </center>
        </div>
    </div>
</div>
</section>
</body
</html>

效果展示

本文地址：https://blog.csdn.net/cs1742121097/article/details/109959987