Java 结果集到分页

ResultSet to Pagination

提问by Gnaniyar Zubair

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

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

For example, this is my query and result set:

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

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

采纳答案by BalusC

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

首先，您需要向 JSP:firstrow和 (可选)添加一两个额外的请求参数rowcount。该rowcount也可以留下，距离完全在服务器端definied。

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

然后向 JSP 添加一堆分页按钮：下一个按钮应该指示使用 的值Servlet增加 的值。在前面的按钮应该明显递减的价值与价值。不要忘记正确处理负值和溢出！您可以在 的帮助下完成。firstrowrowcountfirstrowrowcountSELECT count(id)

Then fire a specific SQL query to retrieve a sublistof the results. The exact SQL syntax however depends on the DB used. In MySQL and PostgreSQL it is easy with LIMITand OFFSETclauses:

然后触发特定的 SQL 查询以检索结果的子列表。然而，确切的 SQL 语法取决于所使用的数据库。在 MySQL 和 PostgreSQL 中，很容易使用LIMITandOFFSET子句：

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 rownumclause which should look like:

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

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:

在 DB2 中row_number()，为此您需要 OLAP 函数：

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.

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

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

最后，只需使用 JSTL 以通常的方式在 JSP 页面中呈现子列表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 maysuggest that you need to SELECTthe entire table and save the List<Contact>in the session scope and make use of List#subList()to paginate. But this is farfrom memory-efficient with thousands rows and multiple concurrent users.

请注意，有些人可能会建议您需要SELECT将整个表保存List<Contact>在会话范围内并使用List#subList()分页。但是对于数千行和多个并发用户来说，这远不是内存高效的。

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

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

回答by Shaun F

Here's a couple things you can do:

您可以执行以下操作：

• Marshall the result set to some list of objects/records
• Based on your required page size, figure out how many pages you will have based on the result set.
• Check request parameter for the required page and offsets based on the number of items to display on the page. So if you're on page 4 with 12 to display, your offset is 48.

• Determine the total number of pages based on the count of the items.

• Display your items based on the offset that you determined (only display starting at item 48)

• Generate your pagination with the amount of pages based on the total number of pages that you determined.

• 将结果集编组为一些对象/记录列表
• 根据您所需的页面大小，根据结果集计算出您将拥有的页面数量。
• 根据页面上显示的项目数检查所需页面的请求参数和偏移量。因此，如果您在第 4 页上显示 12，则偏移量为 48。

• 根据项目数确定总页数。

• 根据您确定的偏移量显示您的项目（仅从项目 48 开始显示）

• 根据您确定的总页数生成具有页数的分页。

=======

========

That's your basic approach. You can tweak this with:

这是你的基本方法。您可以使用以下方法进行调整：

• Determining a way to limit the query to the page (but this wont help you with determining page sizes)
• Fancy ways of pagination
• etc..

• 确定将查询限制到页面的方法（但这不会帮助您确定页面大小）
• 花哨的分页方式
• 等等..

回答by Will Hartung

Look up the Value List Pattern, and apply that. That's typically the best way to handle these kinds of things.

查找值列表模式，并应用它。这通常是处理此类事情的最佳方式。

回答by Vasily Komarov

This Oracle example is wrong.

这个 Oracle 示例是错误的。

Yes, in the outer select whe have good ROWNUM values, but it is still pseudo column so we can not use BETWEEN on it. We need one more select.

是的，在具有良好 ROWNUM 值的外部选择中，但它仍然是伪列，因此我们不能在其上使用 BETWEEN。我们还需要一个选择。

The right sql code is:

正确的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

---

---

Comrades, using solid sql string and Statement class is SLOOOW. Oracle have to parse your SQL every time your execute it.

同志们，使用solid sql 字符串和Statement 类是SOOOW。每次执行 SQL 时，Oracle 都必须解析它。

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

Use PreparedStatement and binding parameters.

使用 PreparedStatement 和绑定参数。

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

And fastest solution is put your sql in oracle stored procedure and use CallableStatement to call it.

最快的解决方案是将您的 sql 放在 oracle 存储过程中并使用 CallableStatement 来调用它。

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

回答by Piyush Chaudhari

You can use displaytag for paigination or resultset but u download some jar file from displattag

您可以使用 displaytag 进行分页或结果集，但您可以从 displattag 下载一些 jar 文件

first you create one servlet StudentList.java

首先你创建一个servlet StudentList.java

public class StudentList extends HttpServlet 

{ public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

{ public void service(HttpServletRequest request, HttpServletResponse response) throws 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

// dao 方法

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