javajdbc中的oracle分页

Question

我有一张有数百万条记录的桌子。为了使系统更快，我需要在Java代码中实现分页概念。我一次只需要提取1000条记录并进行处理，然后再提取1000条记录并进行处理，以此类推。我已经尝试了一些方法，但没有一种有效。下面列出了我尝试过的一些事情-

1) String query = "select * from TABLENAME" +  " WHERE ROWNUM BETWEEN %d AND       %d";
sql = String.format(query, firstrow, firstrow + rowcount);

在上面的示例中，当查询为SELECT * from TABLENAME Where ROWNUM BETWEEN 0 and 10时，它会给我一个结果，但当查询为SELECT * from TABLENAME Where ROWNUM BETWEEN 10 and 20时，它会返回一个空的结果集。我甚至试着在数据库中运行它，它返回空的结果集（不知道为什么！！）

2）preparedStatement.setFetchSize(100);我的Java代码中有这个，但它仍然从表中获取所有记录。添加此语句无论如何都不会影响我的代码

请帮忙

Answer 1

在Oracle中实现分页的常规方法是使用分析窗口函数，例如^{}和定义行顺序的ORDER BY子句。然后，使用分析函数的查询被包装到一个内联视图（或“窗口”）中，从中可以查询所需的行号。下面是一个从my_table（按column_to_sort_by排序）查询前1000行的示例：

select rs.* from
  (select t.*,
    row_number() over (order by column_to_sort_by) as row_num
  from my_table t
  ) rs
where rs.row_num >= 1 and rs.row_num  < 1001
order by rs.row_num

JDBC实现可以如下所示：

public void queryWithPagination() throws SQLException {
    String query = "select rs.* from"
        + " (select t.*,"
        + " row_number() over (order by column_to_sort_by) as row_num"
        + " from my_table t"
        + " ) rs"
        + " where rs.row_num >= ? and rs.row_num  < ?"
        + " order by rs.row_num";

    final int pageSize = 1000;
    int rowIndex = 1;

    try (PreparedStatement ps = myConnection.prepareStatement(query)) {

      do {
         ps.setInt(1, rowIndex);
         ps.setInt(2, rowIndex + pageSize);
         rowIndex += pageSize;
      } while (handleResultSet(ps, pageSize));
    }
}

private boolean handleResultSet(PreparedStatement ps, final int pageSize)
        throws SQLException {
    int rows = 0;
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            /*
             * handle rows here
             */
            rows++;
        }
    }
    return rows == pageSize;
}

请注意，在读取该表时，该表应保持不变，以便分页在不同的查询执行中正确工作

如果表中的行太多，导致内存不足，可能需要在读取某些页面后清除/序列化列表

编辑：

如果行的顺序对您来说根本不重要，那么正如@bdrx在他的回答中提到的，您可能不需要分页，最快的解决方案是在SELECT中查询不带WHERE条件的表。正如建议的那样，您可以将语句的获取大小调整为更大的值，以提高吞吐量

Answer 2

听起来你实际上不需要对结果进行分页，只需要批量处理结果。如果是这种情况，那么您需要做的就是使用setFetchSize将fetchSize设置为1000，并像往常一样迭代resultset（使用resultset.next()），并在迭代时处理结果。有很多资源描述setFetchSize及其功能。做一些研究：

• What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?
• How JDBC Statement.SetFetchsize exaclty works
• What and when should I specify setFetchSize()?

对于oracle分页，有很多资源描述了如何实现这一点。只需进行网络搜索。以下是一些资源，它们描述了如何做到这一点：

• http://www.databasestar.com/limit-the-number-of-rows-in-oracle/
• http://ocptechnology.com/how-to-use-row-limiting-clause/

如果不定义一致的排序（ORDER BY子句），分页就不是很有用，因为不能依赖它们返回的顺序
这个答案解释了BETWEEN语句不起作用的原因：https://stackoverflow.com/a/10318244/908961

根据答案，如果使用早于12c的oracle，则需要进行子选择以获得结果。比如：

SELECT c.*
FROM (SELECT c.*, ROWNUM as rnum
      FROM (SELECT * FROM TABLENAME ORDER BY id) c) c
WHERE c.rnum BETWEEN %d AND %d

如果您使用的是Oracle 12c或更高版本，我建议您使用较新的OFFSET{}语法，而不是摆弄rownum。请参阅上面或下面的第一个链接 http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/01/23/oracle-12c-enhanced-syntax-for-row-limiting-a-k-a-top-n-queries

所以你的问题应该是

String query = "select * from TABLENAME OFFSET %d ROWS FETCH NEXT 1000 ONLY";
String.format(query, firstrow);

或者使用事先准备好的声明

PreparedStatement statement = con.prepareStatement("select * from TABLENAME OFFSET ? ROWS FETCH NEXT 1000 ONLY");
statement.setInt(1, firstrow);
ResultSet rs = statement.executeQuery();

或者，您也可以使用这里描述的limit关键字http://docs.oracle.com/javadb/10.10.1.2/ref/rrefjdbclimit.html，您的查询如下

String query = "select * from TABLENAME { LIMIT 1000 OFFSET %d }";
String.format(query, firstrow);