如何ResultSet对象转换为一个JSP分页看法？

例如，这是我的查询结果集：

pst = con.prepareStatement("select userName, job, place from contact");
rs = pst.executeQuery();

Answer 1:

To start, you need to add one or two extra request parameters to the JSP: firstrow and (optionally) rowcount. The rowcount can also be left away and definied entirely in the server side.

Then add a bunch of paging buttons to the JSP: the next button should instruct the Servlet to increment the value of firstrow with the value of rowcount. The previous button should obviously decrement the value of firstrow with the value of rowcount. Don't forget to handle negative values and overflows correctly! You can do it with help of SELECT count(id).

Then fire a specific SQL query to retrieve a sublist of the results. The exact SQL syntax however depends on the DB used. In MySQL and PostgreSQL it is easy with LIMIT and OFFSET clauses:

private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM"
    + " contact ORDER BY id LIMIT %d OFFSET %d";

public List<Contact> list(int firstrow, int rowcount) {
    String sql = String.format(SQL_SUBLIST, firstrow, rowcount);

    // Implement JDBC.
    return contacts;
}

In Oracle you need a subquery with rownum clause which should look like:

private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM"
    + " (SELECT id, username, job, place FROM contact ORDER BY id)"
    + " WHERE ROWNUM BETWEEN %d AND %d";

public List<Contact> list(int firstrow, int rowcount) {
    String sql = String.format(SQL_SUBLIST, firstrow, firstrow + rowcount);

    // Implement JDBC.
    return contacts;
}

In DB2 you need the OLAP function row_number() for this:

private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM"
    + " (SELECT row_number() OVER (ORDER BY id) AS row, id, username, job, place"
    + " FROM contact) AS temp WHERE row BETWEEN %d AND %d";

public List<Contact> list(int firstrow, int rowcount) {
    String sql = String.format(SQL_SUBLIST, firstrow, firstrow + rowcount);

    // Implement JDBC.
    return contacts;
}

I don't do MSSQL, but it's syntactically similar to DB2. Also see this topic.

Finally just present the sublist in the JSP page the usual way with JSTL c:forEach.

<table>
    <c:forEach items="${contacts}" var="contact">
        <tr>
            <td>${contact.username}</td>
            <td>${contact.job}</td>
            <td>${contact.place}</td>
        </tr>
    </c:forEach>
</table>
<form action="yourservlet" method="post">
    <input type="hidden" name="firstrow" value="${firstrow}">
    <input type="hidden" name="rowcount" value="${rowcount}">
    <input type="submit" name="page" value="next">
    <input type="submit" name="page" value="previous">
</form>

Note that some may suggest that you need to SELECT the entire table and save the List<Contact> in the session scope and make use of List#subList() to paginate. But this is far from memory-efficient with thousands rows and multiple concurrent users.

For ones who are interested in similar answer in JSF/MySQL context using h:dataTable component, you may find this article useful. It also contains some useful language-agnostic maths to get the "Google-like" pagination nicely to work.

Answer 2:

此Oracle例子是错误的。

是的，在外部选择磨片具有良好的ROWNUM值，但它仍然是伪列，所以我们不能在它的储存空间。我们还需要一个选择。

正确的SQL代码：

SELECT c.*
  FROM (SELECT c.*, ROWNUM as rnum
          FROM (SELECT id, username, job, place FROM contact ORDER BY id) c) c
 WHERE c.rnum BETWEEN 5 AND 10

同志们，用坚实的SQL字符串和Statement类是SLOOOW。甲骨文有充分的执行它的时间来分析你的SQL。

//Slooow example
Satement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from my_table where id = 11");

使用PreparedStatement和绑定参数。

   //Faster example
   PreparedStatement ps = conn.getPrepareStatement("select * from my_table where id = ?");
   ps.setInt(1, 11);

和最快的解决方案是把你的SQL在Oracle存储过程并使用CallableStatement的调用它。

//Fastest example
CallableStatement cs = conn.prepareCall("{? = call my_plsql_function(?)}");
cs.setInt(1, 11);

Answer 3:

这里有几件事情可以做：

马歇尔结果集的对象/记录的一些列表
根据您所需的页面大小，计算出你需要多少页面都基于结果集。
请根据项目在页面上显示的数量为所需页面和偏移请求参数。所以，如果你是第4页上12来显示，你的偏移量为48。
确定的基础上，项目数的总页数。
显示基于偏移，你确定你的项目（只显示在第48项开始）
生成的基础上，你确定的总页数页面数量的分页。

=======

这是你的基本方法。你可以调整这个：

确定一种方法来限制查询页面（但是这不会帮助您确定页面大小）
分页的花式方式
等等..

Answer 4:

查找值列表模式，并应用。这是典型来处理这类事情的最好方法。

Answer 5:

您可以使用displaytag为paigination或结果集，但U下载从displattag一些jar文件

首先创建一个servlet的StudentList.java

public class StudentList extends HttpServlet

{公共无效服务（HttpServletRequest的请求，响应HttpServletResponse的）抛出的ServletException，IOException异常{

        ArrayList al=new ArrayList();
        StudentDao stdo=new StudentDao(); // this is DAO Class (Data Acccess Object)

        try
        {
            al=stdo.getStudentList(); //getstudent list dao method
        }
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
        catch (Exception e) 
        {
            e.printStackTrace();
        }
        request.setAttribute("al",al);

        RequestDispatcher rd=request.getRequestDispatcher("StudentPaging.jsp");
        rd.forward(request,response);

}

}

// dao method

public ArrayList getStudentList() throws SQLException,Exception
{
    ArrayList ai=new ArrayList();
    Connection con=null;
    Statement st=null;
    ResultSet rs=null;
    Date dt=new Date();
    SimpleDateFormat sdf=new SimpleDateFormat("dd/MM/yyyy");
    StudentInformation sdata=null;

    con=MyConnection.creatConnection();
    if(con!=null)
    {
        st=con.createStatement();
        String select="select * from STUDENT";
        System.out.println(select);

        rs=st.executeQuery(select);
        if(rs!=null)
        {
            while(rs.next())
            {
                sdata=new StudentInformation();
                sdata.setSid(rs.getString("SID"));
                sdata.setFirstName(rs.getString("FIRSTNAME"));
                sdata.setMiddleName(rs.getString("MIDDLENAME"));
                sdata.setLastName(rs.getString("LASTNAME"));
                dt=rs.getDate("SDATE");
                sdata.setDateofbirth(sdf.format(dt));
                sdata.setGender(rs.getString("GENDER"));
                sdata.setAddress(rs.getString("ADDRESS"));
                sdata.setHigestQulification(rs.getString("HIQULIFICATION"));
                sdata.setLanguageKnow(rs.getString("LANGUAGE"));
                sdata.setHobby(rs.getString("HOBBY"));
                sdata.setTermCondition(rs.getString("TERMCON"));
                ai.add(sdata);
            }
        }
    }
    return ai;
}