实现网页分页显示

如何实现分页显示

使用 JSP+TOMCAT+SQLSERVER

完成效果为，分页

1. 建立页面信息对象
   包括结果集，记录数，每页多少条数据，第几页，总页数，首页，前一页，下一页，尾页

package com.houlu.drp;import java.util.List;/** * 分页信息类 * @author Administrator * @param <T> * */public class PageModel<T> {    //结果集    private List<T> list;    //记录数    private int totalRecords;    //每页多少条数据    private int pageSize;    //第几页    private int pageNo;    //总页数    private int totalPages;    //首页    private int topPageNo = 1;    //前一页    private int previousPageNo;    //下一页    private int nextPageNo;    //尾页    private int buttomPageNo;    public List<T> getList() {        return list;    }    public void setList(List<T> list) {        this.list = list;    }    public int getTotalRecords() {        return totalRecords;    }    public void setTotalRecords(int totalRecords) {        this.totalRecords = totalRecords;    }    public int getPageSize() {        return pageSize;    }    public void setPageSize(int pageSize) {        this.pageSize = pageSize;    }    public int getPageNo() {        return pageNo;    }    public void setPageNo(int pageNo) {        this.pageNo = pageNo;    }    /**     * 取得总页数     * @return     */    public int getTotalPages() {        return (totalRecords + pageSize - 1) / pageSize;    }    /**     * 首页     * @return     */    public int getTopPageNo() {        return topPageNo;    }    /**     * 前一页     * @return     */    public int getPreviousPageNo() {        if (this.pageNo <= 1) {            return 1;        }        return this.pageNo - 1;    }    /**     * 后一页     * @return     */    public int getNextPageNo() {        if(this.pageNo >= totalPages){            return topPageNo;        }        return this.pageNo + 1;    }    /**     * 尾页     * @return     */    public int getButtomPageNo() {        return getTotalPages();    }}

2.使用JAVA查找数据库表，计算页信息，利用rowNumber()以及子查询的方法实现分页查询

use DRPManagementselect * from(    select row_number() over(order by user_id asc) as r2,* from         (            select row_number() over(order by user_id asc) as r1,* from T_USER where USER_ID not in('root')        )t1 where t1.r1<=3 )t2 where t2.r2>0 

/**     * 分页查询     * @param pageNo    第几页     * @param pageSize  每页多少条数据     * @return  pageMode     */    public PageModel findUserList(int pageNo,int pageSize){        //拼串创建SQL        StringBuffer sb = new StringBuffer();        sb.append("select * from(");        sb.append("select row_number() over(order by user_id asc) as r2,* from ");        sb.append("(");        sb.append("select row_number() over(order by user_id asc) as r1,* from T_USER where USER_ID not in('root')");        sb.append(")t1 where t1.r1<= ?");        sb.append(")t2 where t2.r2> ?");        String sql = new String(sb);        Connection conn = null;        PreparedStatement pstmt = null;        ResultSet rs = null;        PageModel pageModel = null;        try {            conn = DbUtil.getConnection();              pstmt = conn.prepareStatement(sql);             pstmt.setInt(1, pageNo*pageSize);            pstmt.setInt(2,(pageNo-1)*pageSize);            rs = pstmt.executeQuery();              List<User> userList = new ArrayList<User>();            while(rs.next()){                User user = new User();                user.setUserId(rs.getString("user_id"));                user.setUserName(rs.getString("user_name"));                user.setPassword(rs.getString("password"));                user.setContactTel(rs.getString("contact_tel"));                user.setEmail(rs.getString("email"));                user.setCreateDate(rs.getTimestamp("create_date"));                userList.add(user);            }            pageModel = new PageModel();            pageModel.setList(userList);            pageModel.setTotalRecords(getTotalRecords(conn));            pageModel.setPageSize(pageSize);            pageModel.setPageNo(pageNo);        } catch (Exception e) {            e.printStackTrace();        }finally{            DbUtil.close(rs);            DbUtil.close(pstmt);            DbUtil.close(conn);        }        return pageModel;    }    /**     * 获取总记录数     * @param conn     * @return     * @throws SQLException     */    private int getTotalRecords(Connection conn)throws SQLException{        String sql = "select count(*) from t_user where user_id not in('root')";        PreparedStatement pstmt = null;        ResultSet rs = null;        int count=0;        try {            pstmt = conn.prepareStatement(sql);            rs = pstmt.executeQuery();            rs.next();            count=rs.getInt(1);        } finally{            DbUtil.close(rs);            DbUtil.close(pstmt);        }        return count;    }

此时pageMode对象保存有多个user对象，即每一页查询结果

3.使用JSP输出

设置页面大小以及第几页

<%    int pageNo = 1;    int pageSize = 2;    PageModel pageMode = UserManager.getInstance().findUserList(pageNo, pageSize);  %>

遍历pagemode中的user对象，输出至页面

    <%                     List userList = pageMode.getList();                    for(Iterator it = userList.iterator();it.hasNext();){                        User user = (User)it.next();                %>                <tr>                    <td class="rd8">                        <input type="checkbox" name="selectFlag" class="checkbox1"                            value="<%=user.getUserId()%>">                    </td>                    <td class="rd8">                        <%=user.getUserId()%>                    </td>                    <td class="rd8">                        <%=user.getUserName()%>                    </td>                    <td class="rd8">                        <%=user.getContactTel()%>                    </td>                    <td class="rd8">                        <%=user.getEmail()%>                    </td>                    <td class="rd8">                        <%=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getCreateDate())%>                    </td>                </tr>                <%                     }                 %>