jsp中利用MySQL实现分页技术

分页是很常用的一种技术，而mysql中对于分页的操作也很简单，今天就说说如何在jsp页面中利用标签来最简化的实现分页：

链接：MySQL分页技术详解http://blog.csdn.net/u011637069/article/details/49928513

step1:编写DAO中代码：

public List<Employee> findAll2(int page, int perPageRows) throws Exception {
    List<Employee> employees = new ArrayList<Employee>();
    Connection conn = null;
    try{
        conn = DBUtil.getConnection();
        PreparedStatement prep =
            conn.prepareStatement("select id,name,salary,age from t_emp limit ?,?");
        prep.setInt(1, (page-1)*perPageRows);
        prep.setInt(2,perPageRows);
        ResultSet rs = prep.executeQuery();
        while(rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double salary = rs.getDouble("salary");
            int age = rs.getInt("age");
            Employee e = new Employee(id, name, salary, age);
            employees.add(e);
        }
    }catch (Exception e) {
        e.printStackTrace();
        throw e;
    }finally{
        DBUtil.close(conn);
    }
    return employees;
}

public int findPages(int perPageRows) throws Exception {
    int totalPage = 0;
    Connection conn = null;
    try{
        conn = DBUtil.getConnection();
        Statement state = conn.createStatement();
        ResultSet rs = state.executeQuery("select count(*) from t_emp");
        int rows = 0;
        if(rs.next()){
            rows = rs.getInt(1);
        }
        if(rows % perPageRows == 0){
            totalPage = rows / perPageRows;
        }else{
            totalPage = rows / perPageRows + 1;
        }
    }catch (Exception e) {
        e.printStackTrace();
        throw e;
    }finally{
        DBUtil.close(conn);
    }
    return totalPage;
}

step2:编写servlet代码：

EmployeeDAO dao = (EmployeeDAO) Factory.getInstance("EmployeeDAO");
List<Employee> employees = null;
String pageStr = request.getParameter("page");

int perPageRows = 5;
int totalPage = 0;
int page = 1;
try {
    totalPage = dao.findPages(perPageRows);
    if(pageStr!=null){
        page = Integer.parseInt(pageStr);
    }
    if(page<1 || page>totalPage){
        page = 1;
    }
    employees = dao.findAll2(page,perPageRows);
    //转发
    //step1，绑定数据
    request.setAttribute("employees",employees);
    request.setAttribute("page",page);
    request.setAttribute("totalPage",totalPage);
    //step2，获得转发器
    RequestDispatcher rd = request.getRequestDispatcher("/emplist.jsp");
    //step3，转发
    rd.forward(request, response);
} catch (Exception e1) {
    e1.printStackTrace();
    //1-转发处理异常
    //request.setAttribute("syserror", "系统正忙，请稍后重试！");
    //request.getRequestDispatcher("error.jsp").forward(request, response);
    //2-交给容器处理
    throw new ServletException(e1);
}

step3:jsp代码：

<c:choose>
    <c:when test="${page>1}">
        <a href="list.do?page=${page-1}">上一页</a>
    </c:when>
    <c:otherwise>
        上一页
    </c:otherwise>
</c:choose>
第${page}页
<c:choose>
    <c:when test="${page<totalPage}">
        <a href="list.do?page=${page+1}">下一页</a>
    </c:when>
    <c:otherwise>
        下一页
    </c:otherwise>
</c:choose>
共${totalPage}页