JDBC 分页 [英] JDBC Pagination

问题描述

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

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"

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

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

对于第 1 页，我得到 50 条记录，对于第 2 页，我想得到接下来的 50 条记录.如何在JDBC中高效实现?

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?

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

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 rownum and limit + offset in query because these are not good to use in query, I dont know why, still I don't want to use it in query.

任何人都可以帮助我如何获取有限的 ResultSet 进行分页，或者 JDBC 有没有给我们提供任何方法?

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

推荐答案

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

There is no efficient way of doing this by simply using JDBC. You have to formulate the limit to n rows and start from i-th item clauses 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 分页教程:http://java.avdiel.com/Tutorials/JDBCPaging.html

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

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