Select range of rows from PostgreSQL table

Question

I want to select range or rows from PostgreSQL table. I tried this code:

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

But when I use the pagination I get different number or rows. If I use the query from Oracle it's working fine. But when I want to use the query from PostgreSQL I get different result on every paginated page. Can you give some result how I can fix this?

Answer 1

Let's put together the key sentences

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

The argument LIMIT in PostgreSQL is for specifiying the maximum number of rows to be retrieved, the number of rows per page.

The argument OFFSET determines which is the first row to be retrieved.

Let's assume that you want 50 rows per page.

  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`

If your rowCount contains the total number of rows to retrieve, it must be assigned to LIMIT and your firstRow to OFFSET. And you can discard variable countrow if you use PostgreSQL.

UPDATE: Just change

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

I'm afraid that I agree with RealSkeptic: "if you expect firstRow for the first page to be 1, you should use firstRow - 1 as your offset." If you're obtaining a negative number as the first argument, the only reason is because your are using 0 as the starting row.

It makes sense if you consider that first row in Oracle has ROWNUM = 1.

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

Your query valid for Oracle stands that RN > 0 for the first page.

First row in PostgreSQL is obtained with an OFFSET = 0, so ...

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

Answer 2

The parameters OFFSET and LIMIT in PostgreSQL are described in the documentation as:

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.

This means that this line:

int countrow = firstRow + rowCount;

is probably wrong. The parameter to pass to LIMIT is the actual row count, not the next row.

The second important thing is that in your statement, the question mark for the offset is first, and the question mark for the limit is second. But you set the parameters like this:

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

The first one is the (bad) "count", and the second one is the "offset". This should be the other way around.

Finally, the OFFSET value is the number of rows to skip, not the number of the first row. So if you expect firstRow for the first page to be 1, you should use firstRow - 1 as your offset.

So, you should replace the setInt lines with:

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