基于mysql的分页实现

为了简单的测试一下，在数据库中新建了一个userinfo的表生成的1000条数据是一个函数写的。

BEGIN DECLARE i INT;set  i=1;WHILE i<1000 DOINSERT INTO userinfo(username,nickname) VALUES(CONCAT("xiaoming",i),CONCAT("leon",i)); SET i=i+1; END WHILE;END

运行的结果

util包的类

DBUtil.java

public class DBUtil {    private static final String url = "jdbc:mysql://localhost/pager";    private static final String user = "root";    private static final String password = "123456";    public static Connection getConn() {        Connection conn = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection(url, user, password);        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }        return conn;    }    // public static void main(String[] args) {    // System.out.println(getConn());    // }    public static  void close(ResultSet rs, Statement stmt, Connection conn) {        if (rs != null) {            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (stmt != null) {            try {                stmt.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

model里面的类

User.java

package com.pager.model;public class User {    /**     * 用户的实体类     */    private int id;    private String username;    private String nickname;    public User() {    }    public User(int id, String username, String nickname) {        super();        this.id = id;        this.username = username;        this.nickname = nickname;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    public String getNickname() {        return nickname;    }    public void setNickname(String nickname) {        this.nickname = nickname;    }}

Pager.java

package com.pager.model;import java.util.List;/** * 分页的实体类，为了便于维护，单独让分页是一个实体类 */public class Pager<T> {    /**     * 开始值（页数）     */    private int pagerIndex;    /**     * 每页有多少条记录     */    private int pagerSize;    /**     * 有多少条记录     */    private int pagerRecord;    /**     * 开始值（记录数）     */    private int pagerOffset;    /**     * 共有多少页     */    private int totalPage;    /**     * 为了以后代码通用，这里List后面的泛型不用User     */    private List<T> datas;    public int getPagerIndex() {        return pagerIndex;    }    public void setPagerIndex(int pagerIndex) {        this.pagerIndex = pagerIndex;    }    public int getPagerSize() {        return pagerSize;    }    public void setPagerSize(int pagerSize) {        this.pagerSize = pagerSize;    }    public int getPagerRecord() {        return pagerRecord;    }    public void setPagerRecord(int pagerRecord) {        this.pagerRecord = pagerRecord;    }    public int getPagerOffset() {        return pagerOffset;    }    public void setPagerOffset(int pagerOffset) {        this.pagerOffset = pagerOffset;    }    public int getTotalPage() {        return totalPage;    }    public void setTotalPage(int totalPage) {        this.totalPage = totalPage;    }    public List<T> getDatas() {        return datas;    }    public void setDatas(List<T> datas) {        this.datas = datas;    }}

SystemThread.java

package com.pager.model;public class SystemThread {    /**     * 分页的ThreadLocal     */    private static ThreadLocal<Integer> pagerIndex = new ThreadLocal<>();    private static ThreadLocal<Integer> pagerOffset = new ThreadLocal<>();    private static ThreadLocal<Integer> pagerSize = new ThreadLocal<>();    public static int getPagerSize() {        return pagerSize.get();    }    public static void setPagerSize(int _pagerSize) {        pagerSize.set(_pagerSize);    }    public static int getPagerOffset() {        return pagerOffset.get();    }    public static void setPagerOffset(int _pagerOffset) {        pagerOffset.set(_pagerOffset);    }    public static int getPagerIndex() {        return pagerIndex.get();    }    public static void setPagerIndex(int _pagerIndex) {        pagerIndex.set(_pagerIndex);    }    public static void removePagerIndex() {        pagerIndex.remove();    }    public static void removePagerOffset() {        pagerOffset.remove();    }    public static void removePagerSize() {        pagerSize.remove();    }}

filter包里 的类

SystemFilter.java

package com.pager.filter;import java.io.IOException;import javax.servlet.Filter;import javax.servlet.FilterChain;import javax.servlet.FilterConfig;import javax.servlet.ServletException;import javax.servlet.ServletRequest;import javax.servlet.ServletResponse;import javax.servlet.http.HttpServletRequest;import com.pager.model.SystemThread;/** * 实现分页的过滤器 *  * @author Administrator * */public class SystemFilter implements Filter {    public static final String PAGE_SIZE_NAME = "ps";    public void destroy() {    }    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)            throws IOException, ServletException {        HttpServletRequest httpRequest = (HttpServletRequest) request;        // 设置分页参数        SystemThread.setPagerOffset(getOffset(httpRequest));        SystemThread.setPagerSize(getPagesize(httpRequest));        try {            chain.doFilter(request, response);        } finally {            SystemThread.removePagerOffset();            SystemThread.removePagerSize();        }    }    private int getOffset(HttpServletRequest httpRequest) {        int offset = 0;        try {            offset = Integer.parseInt(httpRequest.getParameter("pager.offset"));// 得到标签自己计算出的pager.offset        } catch (Exception ignore) {        }        return offset;    }    // 获取每页显示的行数    private int getPagesize(HttpServletRequest httpRequest) {        String pageSize = httpRequest.getParameter("pagesize");// 得到用户自己的每页显示的行数        if (pageSize != null) {            Integer ps;            try {                ps = Integer.parseInt(pageSize);                httpRequest.getSession().setAttribute(PAGE_SIZE_NAME, ps);            } catch (Exception ignore) {            }        }        Integer pagesize = (Integer) httpRequest.getSession().getAttribute(PAGE_SIZE_NAME);        if (pagesize == null) {// 如果用户没有设置就每日显示10行，并将其放入session供以便在显示页面赋值给maxPageItems使用            httpRequest.getSession().setAttribute(PAGE_SIZE_NAME, 10);            return 10;        } else {            return pagesize;        }    }    public void init(FilterConfig arg0) throws ServletException {    }}

dao包的类

UserDao.java

package com.pager.dao;import com.pager.model.Pager;import com.pager.model.User;public interface UserDao {    /**     * 查询所有用户，并且带有分页功能     */    public Pager<User> getAllUsers();}

daoImpl包的类

UserDaoImpl.java

package com.pager.daoImpl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.pager.dao.UserDao;import com.pager.model.Pager;import com.pager.model.SystemThread;import com.pager.model.User;import com.pager.util.DBUtil;public class UserDaoImpl implements UserDao {    // 实现查询所有用户的编码    @Override    public Pager<User> getAllUsers() {        Pager<User> pagers = new Pager<>();        Connection conn = null;        PreparedStatement psmt = null;        ResultSet rs = null;        // 定义pagerOffset和pagerSize        int pagerOffset = SystemThread.getPagerOffset();        int pagerSize = SystemThread.getPagerSize();        // 设置pagerOffset和pagerSize        pagers.setPagerOffset(pagerOffset);        pagers.setPagerSize(pagerSize);        // 创建User的List集合        List<User> datas = new ArrayList<>();        // 放入pager中        pagers.setDatas(datas);        // 链接数据库，并执行sql语句        conn = DBUtil.getConn();        String sql = "SELECT  id,username,nickname FROM userinfo LIMIT ?,?";        String sqlCount = "SELECT COUNT(*) FROM userinfo";        // 执行SQL语句        try {            psmt = conn.prepareStatement(sql);            psmt.setInt(1, pagerOffset);            psmt.setInt(2, pagerSize);            rs = psmt.executeQuery();            // 循环取出数据库中的数据            while (rs.next()) {                User user = new User();                user.setId(rs.getInt("id"));                user.setUsername(rs.getString("username"));                user.setNickname(rs.getString("nickname"));                datas.add(user);            }        } catch (SQLException e) {            e.printStackTrace();        }        // 执行第二条语句        try {            psmt = conn.prepareStatement(sqlCount);            rs = psmt.executeQuery();            while (rs.next()) {                // 总的记录数                int totalRecord = rs.getInt(1);                // 总页数                int totalPage = (totalRecord - 1) / pagerSize + 1;                pagers.setTotalPage(totalPage);                pagers.setPagerRecord(totalRecord);            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            DBUtil.close(rs, psmt, conn);        }        return pagers;    }}

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@page import="com.pager.model.User"%><%@page import="com.pager.model.Pager"%><%@page import="com.pager.dao.UserDao"%><%@page import="com.pager.daoImpl.UserDaoImpl"%><%@page import="java.util.List"%><!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>Insert title here</title></head><body>    <%        UserDaoImpl userDao = new UserDaoImpl();        Pager<User> datas = userDao.getAllUsers();        int totalRecord = datas.getPagerRecord();    %>    <table border="1" >        <tr>            <td>id</td>            <td>username</td>            <td>nickname</td>        </tr>        <%            for (User user : datas.getDatas()) {        %>        <tr>            <td><%=user.getId()%></td>            <td><%=user.getUsername()%></td>            <td><%=user.getNickname()%></td>        </tr>        <%            }        %>    </table>    <jsp:include page="/include/page.jsp">        <jsp:param value="<%=totalRecord%>" name="items" />    </jsp:include></body></html>

pager.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@taglib prefix="pg" uri="http://jsptags.com/tags/navigation/pager"%><style> a{    text-decoration: none;}</style><%    int items = Integer.parseInt(request.getParameter("items"));%><pg:pager maxItems="15" items="<%=items%>" export="curPage=pageNumber" >    <!--<pg:last>           共：<%=items%>,共<%=pageNumber%>页    </pg:last>    -->    <pg:first >        <a   href="<%=pageUrl%>">首页</a>    </pg:first>    <pg:prev>        <a href="<%=pageUrl%>">上一页</a>    </pg:prev>    <pg:pages>        <%            if (curPage == pageNumber) {        %>        [<%=pageNumber%>]        <%            } else {        %>        <a   href="<%=pageUrl%>"><%=pageNumber%></a>        <%            }        %>    </pg:pages>    <pg:next>        <a  href="<%=pageUrl%>">下一页</a>    </pg:next>    <pg:last>        <a  href="<%=pageUrl%>">尾页</a>    </pg:last></pg:pager>

web.xml

    <!-- 配置过滤器 -->    <filter>        <filter-name>SystemFilter</filter-name>        <filter-class>com.pager.filter.SystemFilter</filter-class>    </filter>    <filter-mapping>        <filter-name>SystemFilter</filter-name>        <url-pattern>/*</url-pattern>    </filter-mapping>