Java JDBC 分页

JDBC Pagination

提问by Zeeshan

I want to implement pagination using JDBC. The actual thing I want to know is "How can i get first 50 and then next 50 records from database for page 1 and 2 respectively"

我想使用 JDBC 实现分页。我想知道的实际情况是“我如何分别从数据库中获取第 1 页和第 2 页的前 50 条记录和接下来的 50 条记录”

My Query is Select * from data[data table contains 20,000 rows]

我的查询是Select * from data[数据表包含 20,000 行]

For page #1 I get 50 records and for page #2 I want to get next 50 records. How can I implement it efficiently in JDBC?

对于第 1 页，我得到 50 条记录，对于第 2 页，我想得到接下来的 50 条记录。如何在JDBC中有效地实现它？

I have searched and found that rs.absolute(row)is the way to skip first page records but it takes some amount of time on large result sets and I don't want to bear this amount of time. Also, I don't want to use rownumand limit+ offsetin query because these are not good to use in query, I dont know why, still I don't want to use it in query.

我已经搜索并发现这rs.absolute(row)是跳过第一页记录的方法，但在大型结果集上需要一些时间，我不想承受这么多时间。另外，我不想在查询中使用rownum和limit+offset因为这些在查询中不好使用，我不知道为什么，我仍然不想在查询中使用它。

Can anyone help me how to get limited ResultSetfor pagination or is there any way JDBC is giving us?

任何人都可以帮助我如何限制ResultSet分页，或者 JDBC 有什么方法可以给我们？

采纳答案by psp

There is no efficient way of doing this by simply using JDBC. You have to formulate the limit to n rowsand start from i-th itemclauses directly to the SQL for it to be efficient. Depending on the database this might actually be quite easy (see MySQL's LIMIT -keyword), on other databases such as Oracle it can be a little trickier (involves subquery and using rownum pseudo column).

简单地使用 JDBC 没有有效的方法来做到这一点。您必须将限制制定为 n 行，并从第 i 个项目子句直接开始到 SQL 以使其高效。根据数据库，这实际上可能很容易（请参阅 MySQL 的 LIMIT -keyword），在其他数据库（例如 Oracle）上可能会有点棘手（涉及子查询和使用 rownum 伪列）。

See this JDBC Pagination Tutorial: http://java.avdiel.com/Tutorials/JDBCPaging.html

请参阅此 JDBC 分页教程：http: //java.avdiel.com/Tutorials/JDBCPaging.html

回答by Thorbj?rn Ravn Andersen

I understand implicitly that you do not want the JDBC connection to have a single gigantic resultset which you keep open for a very long time and navigate when required.

我隐含地理解，您不希望 JDBC 连接有一个巨大的结果集，您可以在很长一段时间内保持打开状态并在需要时进行导航。

The usual approach is to add the SQL needed to only get a subset of the full request, which unfortunately is different from database to database, and will make your SQL statements vendor specific. If I recall correctly, LIMIT is used with MySQL. Ask for the appropriate range for every request.

通常的方法是添加仅获取完整请求的子集所需的 SQL，不幸的是，这因数据库而异，并且会使您的 SQL 语句供应商特定。如果我没记错的话，LIMIT 与 MySQL 一起使用。为每个请求请求适当的范围。

I also believe that Hibernate contains functionality which allows you to do this for HQL, but I am unfamiliar with it.

我也相信 Hibernate 包含允许您为 HQL 执行此操作的功能，但我不熟悉它。

回答by Nils Schmidt

If you are using MySQL or PostgreSQL limitand offsetare your keywords. MSSqlServer and Oracle have similar features, but I seems to be a bit more painful.

如果您使用 MySQL 或 PostgreSQL limit和offset是您的关键字。MSSqlServer 和 Oracle 有类似的功能，但是我的好像更痛苦一点。

For MySQLand PostgreSQLhave a look here:

对于MySQL和PostgreSQL，请看这里：

http://www.petefreitag.com/item/451.cfm

http://www.petefreitag.com/item/451.cfm

For Oraclehave a look here:

对于Oracle，请看这里：

http://www.oracle-base.com/forums/viewtopic.php?f=2&t=8635

http://www.oracle-base.com/forums/viewtopic.php?f=2&t=8635

回答by Lars

Are you using some kind of ORM Framework like hibernate or even Java Persistence API or just plain SQL?

您是否在使用某种 ORM 框架，例如 hibernate 甚至 Java Persistence API 或只是普通的 SQL？

My Answer then: use LIMIT and OFFSET http://www.petefreitag.com/item/451.cfm

我的答案是：使用 LIMIT 和 OFFSET http://www.petefreitag.com/item/451.cfm

Or go via ROWNUM Operator You need a wrapper arround your SQL then, but basicaly it's

或者通过 ROWNUM 运算符然后你需要一个包装器围绕你的 SQL，但基本上它是

  select * from (select bla.*, ROWNUM rn from (
  <your sql here>
  ) bla where rownum < 200) where rn >= 150'

回答by BalusC

You should query onlythe data you actuallyneed to display on the current page. Do not haul the entire dataset into Java's memory and then filter it there. It would only make things unnecessarily slower.

您应该只查询您实际需要在当前页面上显示的数据。不要将整个数据集拖入 Java 内存，然后在那里对其进行过滤。它只会使事情不必要地变慢。

If you actually have a hard time in implementing this properly and/or figuring the SQL query for the specific database, then have a look at my answerhere.

如果您实际上很难正确实施此操作和/或计算特定数据库的 SQL 查询，请在此处查看我的答案。

Update:since you're using Oracle, here's an Oracle-targeted extract from the aforementioned answer:

更新：由于您使用的是 Oracle，以下是上述答案中针对 Oracle 的摘录：

In Oracle you need a subquery with rownumclause 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;
}

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

回答by mdma

Oracle supports the standard ROW_NUMBER() window function since 8i so you can use that. You can do it as a parameterized query so you just need to set the start and end row numbers. E.g.

Oracle 从 8i 开始支持标准的 ROW_NUMBER() 窗口函数，因此您可以使用它。您可以将其作为参数化查询来执行，因此您只需要设置开始和结束行号。例如

SELECT * 
   FROM ( SELECT *, ROW_NUMBER() ORDER BY (sort key) AS rowNumber FROM <your table name> ) AS data
WHERE 
   rowNumber>=:start AND
   rowNumber<:end

(If you're not using named parameters, replace :start/:end with the positional parameter placeholder '?')

（如果您不使用命名参数，请将 :start/:end 替换为位置参数占位符 '?'）

See SELECT SQL, Window Functionson wikipedia. The article also lists the other DBs that support the ROW_NUMBER() standard windowing function.

请参阅维基百科上的SELECT SQL，窗口函数。文章还列出了其他支持 ROW_NUMBER() 标准窗口函数的 DB。

回答by Abhishek Gupta

This is link to a hibernate solution for paginating results: HQL - row identifier for pagination

这是分页结果的休眠解决方案的链接： HQL - 分页的行标识符

回答by Carl

PreparedStatement pStmt = // ... however you make it
pStmt.setFetchSize( /* desired number of records to get into memory */ ); 

Note, setFetchSize(int)is only a hint- last time I was using it with MySQL for example, it wasn't supported. Looking around briefly at the Oracle documentation, it looks like their JDBC does support it. I wouldn't quote me on that, but it's worth trying at least; and yes, this answer is fragile, but it might be enough less headache than implementing the robust solution.

请注意，setFetchSize(int)这只是一个提示- 例如，上次我将它与 MySQL 一起使用时，它不受支持。简单地环顾一下 Oracle 文档，看起来他们的 JDBC 确实支持它。我不会引用我的话，但至少值得一试；是的，这个答案是脆弱的，但与实施强大的解决方案相比，它可能足够令人头疼。

Essentially, you can issue the request for everything, and you only get the fetch size into memory at a time (providing you're not holding onto the previous results). So you'd set your fetch size to 50, make your connection/query, display the first 50 results (causing the another fetch for the next bite of your query) and so on.

本质上，您可以对所有内容发出请求，并且一次只能将提取大小放入内存（前提是您没有保留先前的结果）。因此，您将提取大小设置为 50，建立连接/查询，显示前 50 个结果（导致查询的下一个部分的另一个提取）等等。

回答by vincentlcy

Disclaimer: This blog post on SQL pagination & JDBC pagination is posted by me.

免责声明：这篇关于 SQL 分页和 JDBC 分页的博客文章是我发布的。

Disregarding Hibernate pagination, we can use SQL pagination / JDBC pagination

不考虑Hibernate分页，我们可以使用SQL分页/JDBC分页

SQL pagination

SQL 分页

There are two basic approaches:

有两种基本方法：

1. operating on piecemeal result set (New Query for Each Page)
2. operating on full result set

1. 对零散的结果集进行操作（每个页面的新查询）
2. 在完整结果集上操作

The way to do it is SQL specific

这样做的方法是特定于 SQL 的

For MySQL / many other SQLs it can be done with limit and offset

对于 MySQL / 许多其他 SQL，它可以通过限制和偏移来完成

Postgresql: http://microjet.ath.cx/WebWiki/ResultPaginationWithPostgresql.html

Postgresql：http: //microjet.ath.cx/WebWiki/ResultPaginationWithPostgresql.html

In Oracle, it use the same form as to handle "Top-N query" e.g. who are the 5 highest paid employee, which is optimized

在 Oracle 中，它使用与处理“Top-N 查询”相同的形式，例如谁是薪酬最高的 5 名员工，这是经过优化的

select *   from ( select a.*, rownum rnum

from ( YOUR_QUERY_GOES_HERE -- including the order by ) a

where rownum <= MAX_ROWS )

where rnum >= MIN_ROWS

Here is a very detailed explanation on ROW-NUM

这里有一个关于 ROW-NUM 的非常详细的解释

Similar SO Thread

类似的 SO 线程

JDBC Pagination

JDBC 分页

The question comes into mind is: when I execute the SQL, how is the result being loaded? Immediately or on request? same as this SO thread

想到的问题是：当我执行 SQL 时，结果是如何加载的？立即或应要求？与此 SO 线程相同

First we need to understand some basics of JDBC, as from Oracle

首先我们需要了解一些JDBC的基础知识，如Oracle

Per javadoc: statement.execute()

根据 javadoc： statement.execute()

execute: Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResutSet.

We access data in Resultset via a cursor. Note this cursor is different from that of DB while it is a pointer initially positioned before the first row of data.

我们通过游标访问结果集中的数据。请注意，此游标与 DB 的游标不同，它是最初位于第一行数据之前的指针。

The data is fetch on request. while when you do the execute() you are fetching for the first time.

数据是根据请求获取的。而当您执行 execute() 时，您是第一次获取。

Then, how many data is loaded? It is configurable. One can use the java API setFetchSize() method on ResultSet to control how many rows are fetched from DB a time by the driver, how big the blocks it retrieves at once.

那么，加载了多少数据呢？它是可配置的。可以在 ResultSet 上使用 java API setFetchSize() 方法来控制驱动程序一次从数据库中获取多少行，一次检索的块有多大。

For example assume the total result is 1000. If fetch size is 100, fetching the 1st row will load 100 rows from DB and 2nd to 100th row will be loaded from local memory.to query 101st row another 100 rows will be load into memory.

例如，假设总结果为 1000。如果 fetch size 为 100，则获取第一行将从 DB 加载 100 行，将从本地内存加载第 2 到第 100 行。查询第 101 行将另外 100 行加载到内存中。

From JavaDoc

来自 JavaDoc

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

Note the word "hint" - it can be override by driver specific implementation.

注意“提示”这个词——它可以被驱动程序特定的实现覆盖。

This is also what the "Limit Rows to 100" feature in client like SQL developer based on.

这也是SQL开发者等客户端的“Limit Rows to 100”特性的基础。

Completing the whole solution, to scroll results, one need to consider the ResultSet Types and ScrollableCursor in API

完成整个解决方案，要滚动结果，需要考虑 API 中的 ResultSet Types 和 ScrollableCursor

One can find an example implementation from this post in oracle

可以在 oracle 中从这篇文章中找到示例实现

which is from the book Oracle Toplink Developer's Guide Example 112 JDBC Driver Fetch Size

这是来自 Oracle Toplink Developer's Guide Example 112 JDBC Driver Fetch Size 一书

ReadAllQuery query = new ReadAllQuery();

query.setReferenceClass(Employee.class);

query.setSelectionCriteria(new ExpressionBuilder.get("id").greaterThan(100));

// Set the JDBC fetch size

query.setFetchSize(50);

// Configure the query to return results as a ScrollableCursor

query.useScrollableCursor();

// Execute the query

ScrollableCursor cursor = (ScrollableCursor) session.executeQuery(query);

// Iterate over the results

while (cursor.hasNext()) {

System.out.println(cursor.next().toString());

}

cursor.close();

.....................

………………

After all, the questions boil to

毕竟，问题归结为

Which is the better way to do pagination?

哪个是更好的分页方式？

Note the SQL should be ORDER by to make sense in the SQL approach,

注意 SQL 应该是 ORDER by 以在 SQL 方法中有意义，

Otherwise it is possible to show some rows again in next page.

否则有可能在下一页再次显示一些行。

Below is some points from Postgresql's documentation on JDBC Driver and other SO answers

以下是 Postgresql 关于 JDBC 驱动程序和其他 SO 答案的文档中的一些要点

First off, the original query would need to have an ORDER BY clause in order to make the paging solution work reasonably. Otherwise, it would be perfectly valid for Oracle to return the same 500 rows for the first page, the second page, and the Nth page

首先，原始查询需要有一个 ORDER BY 子句才能使分页解决方案合理地工作。否则，Oracle 为第一页、第二页和第 N 页返回相同的 500 行将是完全有效的

The major difference is for the JDBC way, it is required to hold the connection during the fetching. This may not be suitable in stateless web application, for example.

主要区别在于JDBC方式，需要在获取期间保持连接。例如，这可能不适用于无状态 Web 应用程序。

For SQL way

对于 SQL 方式

the syntax is SQL specific and may not be easy to maintain. For JDBC way

语法是特定于 SQL 的，可能不容易维护。 对于JDBC方式

• The connection to the server must be using the V3 protocol. This is the default for (and is only supported by) server versions 7.4 and later.
• The Connection must not be in autocommit mode. The backend closes cursors at the end of transactions, so in autocommit mode the backend will have closed the cursor before anything can be fetched from it.
• The Statement must be created with a ResultSet type of ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need to be rewritten to take advantage of this, but it also means that you cannot scroll backwards or otherwise jump around in the ResultSet.
• The query given must be a single statement, not multiple statements strung together with semicolons.

• 与服务器的连接必须使用 V3 协议。这是（并且仅受支持）服务器版本 7.4 及更高版本的默认设置。
• 连接不得处于自动提交模式。后端在事务结束时关闭游标，因此在自动提交模式下，后端将在从中获取任何内容之前关闭游标。
• 该语句必须使用 ResultSet.TYPE_FORWARD_ONLY 的 ResultSet 类型创建。这是默认设置，因此无需重写代码即可利用此功能，但这也意味着您无法在 ResultSet 中向后滚动或以其他方式跳转。
• 给出的查询必须是单个语句，而不是用分号串在一起的多个语句。

Some Further reading

一些延伸阅读

This post is about performance tuning with optical fetch size

这篇文章是关于使用光学提取大小进行性能调整的

回答by SkateScout

Input:

输入：

1. Order Information example (A2 or D3) (A/D ascending/descending) + column
2. Order Information example (A2 or D3) (A/D ascending/descending) + column
3. Filter value
4. start row
5. start row
6. maximum nuber of rows

1. 订单信息示例（A2 或 D3）（A/D 升序/降序）+ 列
2. 订单信息示例（A2 或 D3）（A/D 升序/降序）+ 列
3. 过滤值
4. 开始行
5. 开始行
6. 最大行数

Result:

结果：

• Selected values
• Selected page
• Index of the row in this ordering
• Count off available data. (Save an second query)

• 选定值
• 所选页面
• 此排序中行的索引
• 计算可用数据。（保存第二个查询）

Advantage only Query for:

仅优势查询：

• sum of available columns with this filter
• only transfer the selected page from db
• correctly ordered without dynamic sql

• 使用此过滤器的可用列的总和
• 只从数据库传输选定的页面
• 在没有动态 sql 的情况下正确排序

Disadvantage:

坏处：

• Oracle Dependend

  select x.* from ( select c.pk_field,c.numeric_a, c.char_b, c.char_c ROW_NUMBER( ) over(ORDER BY decode(?,'A1',to_char(c.numeric_a,'FM00000000'),'A2',c.char_b,'A3',c.char_c,'A') asc , decode(?,'D1',to_char(c.numeric_a,'FM00000000'),'D2',c.char_b,'D3',c.char_c,'A') desc, c.pk_field asc
  ) AS "idx", COUNT (*) OVER (ORDER BY 1) "cnt" from myTable c where c.haystack=? ) x where x."idx" between greatest(nvl(?,1),1) and nvl(?,1)-1+?

• 甲骨文依赖

  select x.* from ( select c.pk_field,c.numeric_a, c.char_b, c.char_c ROW_NUMBER( ) over(ORDER BY decode(?,'A1',to_char(c.numeric_a,'FM00000000'),'A2 ',c.char_b,'A3',c.char_c,'A') asc , decode(?,'D1',to_char(c.numeric_a,'FM00000000'),'D2',c.char_b,'D3' ,c.char_c,'A') desc, c.pk_field asc
  ) AS "idx", COUNT (*) OVER (ORDER BY 1) "cnt" from myTable c where c.haystack=? ) x where x."idx " 介于最大 (nvl(?,1),1) 和 nvl(?,1)-1+?