结果集到分页 [英] ResultSet to Pagination

问题描述

如何将Resultset对象转换为JSP上的分页视图？

How do I convert Resultset object to a paginated view on a JSP?

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

For example, this is my query and result set:

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

推荐答案

首先，您需要添加一两个额外的请求JSP参数： firstrow 和（可选） rowcount 。 rowcount 也可以保留在服务器端完全定义。

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.

然后添加一堆分页按钮到JSP： next 按钮应该指示 Servlet 增加 firstrow 的值值为 rowcount 。 上一个按钮显然应该减去 firstrow 的值，其值为 rowcount 。不要忘记正确处理负值和溢出！您可以在 SELECT count（id）的帮助下完成。

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).

然后触发特定的SQL查询以检索子列表的结果。但确切的SQL语法取决于使用的DB。在MySQL和PostgreSQL中，使用 LIMIT 和 OFFSET 子句很容易：

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;
}

在Oracle中，您需要一个带 rownum的子查询子句应如下所示：

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;
}

在DB2中，您需要OLAP函数 row_number（） 为此：

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;
}

我不做MSSQL，但它在语法上与DB2类似。另请参阅此主题。

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

最后在JSTL中以常用方式呈现JSP页面中的子列表 c：forEach 。

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>

请注意，某些可能建议您需要 SELECT 整个表并在会话范围中保存 List< Contact> 并使用 List＃subList（） 分页。但这远远超过远内存效率，有数千行和多个并发用户。

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.

对于那些对JSF / MySQL中的类似答案感兴趣的人上下文使用 h：dataTable 组件，你可能会发现这篇文章很有用。它还包含一些有用的与语言无关的数学，以使类似Google的分页很好地工作。

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.

这篇关于结果集到分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！