Using limit () and offset () in QueryBuilder (ANDROID , ORMLITE)

fabiodresch picture fabiodresch · Apr 19, 2013 · Viewed 9.1k times · Source
@SuppressWarnings("deprecation")
public List<Picture> returnLimitedList(int offset, int end) {
    List<Picture> pictureList = new ArrayList<Picture>();
    int startRow = offset;
    int maxRows = end;
    try {
        QueryBuilder<Picture, Integer> queryBuilder = dao.queryBuilder();
        queryBuilder.offset(startRow).limit(maxRows);
        pictureList = dao.query(queryBuilder.prepare());
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return pictureList;
}
  • I have a table of Pictures in the database, and must return a limited list, 20 lines at a time.
  • But when I use ex: QueryBuilder.offset(11).limit(30);
  • I can not return the list limited to 20 lines.
  • The list only comes to me with the limit.
  • It's as if the offset remain always with value 0
  • ex: (0 - 30)

  • Is there any other way to return a limited list for initial index and end index?

  • Could anyone help me?

Answer

Rigotti picture Rigotti · Jun 20, 2013

This question was asked two months ago, but I'll answer if anyone stumbled on the same problem as I did.

There's misunderstanding about what offset means in this case, here follows what SQLite Documentations says about it

If an expression has an OFFSET clause, then the first M rows are omitted from the result set returned by the SELECT statement and the next N rows are returned, where M and N are the values that the OFFSET and LIMIT clauses evaluate to, respectively.

Source

Based on your query, you'll return 30 lines starting at the #11 line.

So the correct way is:

queryBuilder.offset(startRow).limit(20);

With limit being the number of rows that will return, not the ending row.

pictureList = dao.query(queryBuilder.prepare());

And the returned List with the first value starting on pictureList.get(0)

Edit: @Gray 's help on comments