Servlet+mysql实现分页技术

先让数据库中的数据显示出来

数据库信息：

然后通过Servlet，使数据库中的信息在浏览器中通过表格的形式显示出来：

package com.page.one;import javafx.print.Printer;import java.io.IOException;import java.io.PrintWriter;import java.sql.*;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class Page extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {    response.setContentType("text/html;charset=utf-8");    request.setCharacterEncoding("utf-8");    PrintWriter out = response.getWriter();    try{        //1.注册驱动        Class.forName("com.mysql.jdbc.Driver");        //2.获得数据库连接        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");        //3.创建句柄        Statement stmt = conn.createStatement();        //4.执行SQL语句        ResultSet rs = stmt.executeQuery("SELECT * FROM users");        //5.处理执行结果        out.println("<table border=1 width=500px>");        out.println("<tr><th>ID</th><th>用户名</th><th>电子邮件</th><th>等级</th></tr>");        while(rs.next()){            out.println("<tr><td>"+rs.getInt("id")+"</td><td>"+                    rs.getString("username")+"</td><td>"+                    rs.getString("email")+"</td><td>"+                    rs.getInt("grade")+"</td></tr>");        }        out.println("</table><br/>");    }catch (SQLException e){        e.printStackTrace();    }catch (ClassNotFoundException e){        e.printStackTrace();    }}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {    this.doGet(request, response);}}

显示内容：

添加分页

定义分页变量：
1. pagenow:表示第几页 (由用户来决定， 因此变化)
2. pageSize：每页显示多少条记录 (有程序制定，也可以让用户来定制)
3. pageCount: 表示共有多少页 (这是计算出来的)
4. rowCount: 共有多少条记录 (通过查询数据库得到的)

先设置变量：

   //设置总页数变量    int pageCount=1;    //设置总行数变量    int rowCount=1;    //设置目前所在页面    int pageNow=1;    //设置每页信息行数    int pageSize=4;

求出信息总行数

//执行SQL语句，来确定总信息数量 ResultSet rs2 = stmt.executeQuery("SELECT count(*) FROM users"); rs2.next(); rowCount = rs2.getInt(1);        

求出总页数

//计算PageCountpageCount = rowCount%pageSize==0 ? rowCount/pageSize : rowCount/pageSize+1;

执行语句显示本页面的信息

ResultSet rs = stmt.executeQuery("SELECT * FROM users ORDER BY ID LIMIT "+(pageNow-1)*pageSize+","+pageSize);

在表下方显示页面

for(int i = 1; i <= pageCount; i++){            out.println("<a href='/Page2?pageNow="+i+"'><"+i+"></a>");        }

接收超链接所提交的页面选项

String spageNow = request.getParameter("pageNow");if(spageNow != null){      pageNow = Integer.parseInt(spageNow);}

因为没有选的时候接收到的是空值，直接不需要转换，使用初定义就行了。

page2.java

public class Page extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {    response.setContentType("text/html;charset=utf-8");    request.setCharacterEncoding("utf-8");    PrintWriter out = response.getWriter();    //设置总页数变量    int pageCount=1;    //设置总行数变量    int rowCount=1;    //设置目前所在页面    int pageNow=1;    //设置每页信息行数    int pageSize=4;    String spageNow = request.getParameter("pageNow");    if(spageNow != null){        pageNow = Integer.parseInt(spageNow);    }    try{        //1.注册驱动        Class.forName("com.mysql.jdbc.Driver");        //2.获得数据库连  接        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");        //3.创建句柄        Statement stmt = conn.createStatement();        //执行SQL语句，来确定总信息数量        ResultSet rs2 = stmt.executeQuery("SELECT count(*) FROM users");        rs2.next();        rowCount = rs2.getInt(1);        out.println("rowCount = "+rowCount);        //计算PageCount        pageCount = rowCount%pageSize==0 ? rowCount/pageSize : rowCount/pageSize+1;        //执行SQL语句        ResultSet rs = stmt.executeQuery("SELECT * FROM users ORDER BY ID LIMIT "+(pageNow-1)*pageSize+","+pageSize);        //处理执行结果        out.println("<table border=1 width=500px>");        out.println("<tr><th>ID</th><th>用户名</th><th>电子邮件</th><th>等级</th></tr>");        while(rs.next()){            out.println("<tr><td>"+rs.getInt("id")+"</td><td>"+                    rs.getString("username")+"</td><td>"+                    rs.getString("email")+"</td><td>"+                    rs.getInt("grade")+"</td></tr>");        }        out.println("</table><br/>");        for(int i = 1; i <= pageCount; i++){            out.println("<a href='/Page2?pageNow="+i+"'><"+i+"></a>");        }    }catch (SQLException e){        e.printStackTrace();    }catch (ClassNotFoundException e){        e.printStackTrace();    }}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {    this.doGet(request, response);}}

结果显示

当然也可以完善一些，添加上上一页与下一页，以及跳转页数，但是要注意上一页到第一页与下一页到最后一页的情况，以及跳转时输入不在范围内的情况：

直接上代码吧。。
上下一页：

        //当到第一页时就没有上一页这个选项了        if(pageNow!=1){            out.println("<a href='/Page2?pageNow="+(pageNow-1)+"'>上一页</a>");        }        for(int i = 1; i <= pageCount; i++){            out.println("<a href='/Page2?pageNow="+i+"'><"+i+"></a>");        }        //当到最后一页时就没有下一页这个选项了        if(pageNow!=pageCount){            out.println("<a href='/Page2?pageNow="+(pageNow+1)+"'>下一页</a>");        }

显示分页信息：

       //显示分页信息        out.println("　　　当前页:"+pageNow+"| 总页数:"+pageCount);

跳转：

        out.println("<br/><br/>");        out.println("跳转到第<input type='text' id='pageNow' name='pageNow'/> <input type='button' onClick='gotoPageNow()' value='跳'/>");

JavaScript语言来实现跳转：

 //添加JavaScript代码    out.println("<script type='text/javascript' language='javascript'>");    out.println("function gotoPageNow(){ "+            "var pageNow=document.getElementById('pageNow');"+            //window.open能把pageNow传递给本页面，而且能够通过request.getParameter获得。            "window.open('/Page2?pageNow='+pageNow.value,'_self'); }");    out.println("</script>");

注意超出范围的情况：

String spageNow = request.getParameter("pageNow");        if(spageNow!=null){            int temp = Integer.parseInt(spageNow);            if(temp<=pageCount&&temp>=1){                pageNow = temp;            }else{                out.println("<script type='text/javascript' language='javascript'>");                out.println("window.alert('所跳页面不在范围内'); ");                out.println("</script>");            }        }

page.java

public class Page extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {    response.setContentType("text/html;charset=utf-8");    request.setCharacterEncoding("utf-8");    PrintWriter out = response.getWriter();    //添加JavaScript代码    out.println("<script type='text/javascript' language='javascript'>");    out.println("function gotoPageNow(){ "+            "var pageNow=document.getElementById('pageNow');"+            //window.open能把pageNow传递给本页面，而且能够通过request.getParameter获得。            "window.open('/Page2?pageNow='+pageNow.value,'_self'); }");    out.println("</script>");    //设置总页数变量    int pageCount=1;    //设置总行数变量    int rowCount=1;    //设置目前所在页面    int pageNow=1;    //设置每页信息行数    int pageSize=4;    try{        //1.注册驱动        Class.forName("com.mysql.jdbc.Driver");        //2.获得数据库连  接        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");        //3.创建句柄        Statement stmt = conn.createStatement();        //执行SQL语句，来确定总信息数量        ResultSet rs2 = stmt.executeQuery("SELECT count(*) FROM users");        rs2.next();        rowCount = rs2.getInt(1);        out.println("rowCount = "+rowCount);        //计算PageCount        pageCount = rowCount%pageSize==0 ? rowCount/pageSize : rowCount/pageSize+1;        String spageNow = request.getParameter("pageNow");        if(spageNow!=null){            int temp = Integer.parseInt(spageNow);            if(temp<=pageCount&&temp>=1){                pageNow = temp;            }else{                out.println("<script type='text/javascript' language='javascript'>");                out.println("window.alert('所跳页面不在范围内'); ");                out.println("</script>");            }        }        //执行SQL语句        ResultSet rs = stmt.executeQuery("SELECT * FROM users ORDER BY ID LIMIT "+(pageNow-1)*pageSize+","+pageSize);        //处理执行结果        out.println("<table border=1 width=500px>");        out.println("<tr><th>ID</th><th>用户名</th><th>电子邮件</th><th>等级</th></tr>");        while(rs.next()){            out.println("<tr><td>"+rs.getInt("id")+"</td><td>"+                    rs.getString("username")+"</td><td>"+                    rs.getString("email")+"</td><td>"+                    rs.getInt("grade")+"</td></tr>");        }        out.println("</table><br/>");        //当到第一页时就没有上一页这个选项了        if(pageNow!=1){            out.println("<a href='/Page2?pageNow="+(pageNow-1)+"'>上一页</a>");        }        for(int i = 1; i <= pageCount; i++){            out.println("<a href='/Page2?pageNow="+i+"'><"+i+"></a>");        }        //当到最后一页时就没有下一页这个选项了        if(pageNow!=pageCount){            out.println("<a href='/Page2?pageNow="+(pageNow+1)+"'>下一页</a>");        }        //显示分页信息        out.println("　　　当前页:"+pageNow+"|&nbsp;总页数:"+pageCount);        out.println("<br/><br/>");        out.println("跳转到第<input type='text' id='pageNow' name='pageNow'/> <input type='button' onClick='gotoPageNow()' value='跳'/>");    }catch (SQLException e){        e.printStackTrace();    }catch (ClassNotFoundException e){        e.printStackTrace();    }}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {    this.doGet(request, response);}}

结果：