从PostgreSQL表中选择一系列行

Question

从PostgreSQL表中选择一系列行

4

我想从PostgreSQL表中选择一定范围的行。我尝试了以下代码：

 public List<CustomersObj> list(int firstRow, int rowCount, String sortField, boolean sortAscending) throws SQLException
        {

            String SqlStatement = null;

            if (ds == null)
            {
                throw new SQLException();
            }

            Connection conn = ds.getConnection();
            if (conn == null)
            {
                throw new SQLException();
            }

            int countrow = firstRow + rowCount;
            String sortDirection = sortAscending ? "ASC" : "DESC";

// Oracle
    //        SqlStatement = "SELECT A.* "
    //            + " FROM (SELECT B.*, ROWNUM RN "
    //            + " FROM (SELECT Y.COMPONENTSTATSID, Y.NAME, Y.SERIALNUMBER, Y.WEIGHTKG, Y.ZONECAGE, Y.POWERWATT, Y.MANIFACTURECOMPANY, Y.UFORM, "
    //            + " Y.STATUS, Y.LOCATION, Y.HEATEMISIONSBTU, Y.PRODUCTIONENVIRONMENT, Y.STANDARTLIFETIME, Y.OPERATINGHAMIDITYRANGE, "
    //            + " Y.OPERATINGSYSTEM, Y.DATEDEPLOYED, Y.INTERFACETYPE, Y.TYPE, Y.COOLINGCAPACITYBTU, Y.DATEADDED, Y.DESCRIPTION "
    //            + " FROM COMPONENTWEIGHT X, COMPONENTSTATS Y WHERE X.COMPONENTSTATSID = Y.COMPONENTSTATSID AND Y.COMPONENTTYPEID = 3300 "
    //            + " ORDER BY %S %S) B "
    //            + " WHERE ROWNUM <= ?) A "
    //            + " WHERE RN > ?";
// postgresql
            SqlStatement = "SELECT * FROM CUSTOMERS ORDER BY %S %S offset ? limit ? ";

            String sql = String.format(SqlStatement, sortField, sortDirection);

            PreparedStatement ps = null;
            ResultSet resultSet = null;
            List<CustomersObj> resultList = new ArrayList<>();

            try
            {
                conn.setAutoCommit(false);
                boolean committed = false;

                ps = conn.prepareStatement(sql);
                ps.setInt(1, countrow);
                ps.setInt(2, firstRow);

                resultSet = ps.executeQuery();
                resultList = ProcessorArrayList(resultSet);

                conn.commit();
                committed = true;

            }
            finally
            {
                ps.close();
                conn.close();
            }

            return resultList;
        }

但是，当我使用分页功能时，行数会有所不同。如果我使用来自Oracle的查询，则可以正常工作。但是，当我想要使用来自PostgreSQL的查询时，每个分页页面都会得到不同的结果。您能否提供一些解决方法？

- Peter Penzov

2个回答

0

让我们把关键句子放在一起

 int countrow = firstRow + rowCount;
 SqlStatement = "SELECT * FROM CUSTOMERS ORDER BY %S %S offset ? limit ? ";
 ps = conn.prepareStatement(sql);
 ps.setInt(1, countrow);
 ps.setInt(2, firstRow);

在PostgreSQL中，参数LIMIT用于指定要检索的最大行数，即每页的行数。

参数OFFSET确定要检索的第一行是哪一行。

假设您想要每页50行。

  To retrieve the first page -> `LIMIT 50 OFFSET 0` [rows 0 to 49]

  To retrieve the second page -> `LIMIT 50 OFFSET 50` [rows 50 to 99] 

  ...

  To retrieve the nth page -> `LIMIT 50 OFFSET (n - 1)*50`

如果您的rowCount包含要检索的总行数，则必须将其分配给LIMIT，并将firstRow分配给OFFSET。如果使用PostgreSQL，则可以丢弃变量countrow。

更新：只需更改

 ps.setInt(1, firstRow);  // Assign firstRow to OFFSET
 ps.setInt(2, rowCount);  // Assign rowCount to LIMIT

我很抱歉，我同意RealSkeptic的观点：“如果您期望第一页的firstRow为1，则应将firstRow-1用作偏移量。” 如果您的第一个参数是负数，唯一的原因是因为您将0用作起始行。

如果您考虑Oracle中的第一行具有ROWNUM = 1，那么这是有道理的。

https://docs.oracle.com/cd/B14117_01/server.101/b10759/pseudocolumns008.htm

针对Oracle的查询有效，RN > 0适用于第一页。

在PostgreSQL中，第一行是通过OFFSET = 0获得的，因此...

  YES ->  ps.setInt(1, firstRow);
  NO  ->  ps.setInt(1, firstRow - 1);

- RubioRic

你能给我一个例子，说明我需要如何重构代码，因为它不是很清晰吗？ - Peter Penzov

我正在使用这段代码http://balusc.omnifaces.org/2008/10/effective-datatable-paging-and-sorting.html。看起来问题出在查询上。它是为此查询设计的：“SELECT id，name，value FROM mydata ORDER BY %s %s LIMIT？，？”；现在下一页按钮无法正常工作。在第二页之后就无法正常工作了。 - Peter Penzov

@PeterPenzov 在那个页面中 public void pageFirst() { page(0); }。你说第一页的第一行是1。 - RubioRic

是的。由于某种原因，在第二页之后，下一页按钮无法工作。 - Peter Penzov

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- RealSkeptic · Accepted Answer

在PostgreSQL中，OFFSET和LIMIT参数的含义在文档中描述如下：

The LIMIT clause consists of two independent sub-clauses:
LIMIT { count | ALL } OFFSET start
count specifies the maximum number of rows to return, while start specifies the number of rows to skip before starting to return rows. When both are specified, start rows are skipped before starting to count the count rows to be returned.

这意味着这行代码：

int countrow = firstRow + rowCount;

这可能是错误的。传递给LIMIT的参数应该是实际行数，而不是下一行。

第二个重要的事情是，在您的语句中，偏移量的问号是第一个，而限制的问号是第二个。但是你设置了参数如下：

            ps.setInt(1, countrow);
            ps.setInt(2, firstRow);

第一个是（糟糕的）“计数”，第二个是“偏移量”。这应该反过来。

最后，"OFFSET"值是要跳过的行数，而不是第一行的行数。因此，如果您期望第一页的firstRow为1，则应将firstRow-1用作偏移量。

因此，您应该将setInt行替换为：

            ps.setInt(1, firstRow - 1);
            ps.setInt(2, rowCount);