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?

RealSkeptic · Accepted Answer · 2016-03-30 19:45:21Z

3

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

answered Mar 30, 2016 at 19:45

RealSkeptic

34.8k7 gold badges56 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Peter Penzov Over a year ago

I get org.postgresql.util.PSQLException: ERROR: OFFSET must not be negative

RealSkeptic Over a year ago

@Peter So check if the firstRow really starts from 1. If it starts from zero, you don't need to subtract the 1.

Peter Penzov Over a year ago

it's 1. For some reason it's not working properly. Next button and back are not working properly.

RealSkeptic Over a year ago

That may be a different issue - perhaps the back button is doing what the forward button is supposed to do, or you allow the back button to be used in the first page. You'll have to use a debugger to solve that or at least get enough information for a new question on SO.

Peter Penzov Over a year ago

Well, Oracle query it's working fine, but PostgreSQL query is not working well. Looks like the query is the problem.

|

RubioRic · Accepted Answer · 2016-03-30 21:49:45Z

0

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

edited Mar 30, 2016 at 21:49

answered Mar 30, 2016 at 19:56

RubioRic

2,4684 gold badges32 silver badges38 bronze badges

4 Comments

Peter Penzov Over a year ago

Can you give me example how I need to refactor the code because it's not very clear?

Peter Penzov Over a year ago

I'm using this code balusc.omnifaces.org/2008/10/… Looks like the problem is the query. It's designed for this query: "SELECT id, name, value FROM mydata ORDER BY %s %s LIMIT ?, ?"; Right now next button is not working fine. After the second page it's not working.

RubioRic Over a year ago

@PeterPenzov In that page public void pageFirst() { page(0); }. You said that firstRow in first page was 1.

Peter Penzov Over a year ago

Yes. For some reason after page 2 the next button it's not working.

Collectives™ on Stack Overflow

Select range of rows from PostgreSQL table

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related