实现网页分页显示

如何实现分页显示

使用 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 DRPManagement
select * 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>

                <% 
                    } 
                %>
展开阅读全文

没有更多推荐了,返回首页