SQLite Paging issue with Offset and Limit

user_14423729 0
Viewed 459

I am making a query like below to load 10 quotes in one page

arrayList.addAll(db.loadFav(CURRENT_PAGE, Constant.ITEM_PER_PAGE));

and DBHelper.loadFav is like below

 public ArrayList<ItemQuotes> loadFav(int offset, int limit) {
        ArrayList<ItemQuotes> arrayList = new ArrayList<>();
        try {
            //Cursor cursor = db.query(TABLE_QUOTES_FAV, columns_quotes, null, null, null, null, null);
            String query = "SELECT * FROM " + TABLE_QUOTES_FAV + " ORDER BY " + TAG_QUOTES_ID + " ASC "+ " LIMIT " + offset+","+limit;
            Cursor cursor = db.rawQuery(query, null);
            if (cursor != null && cursor.moveToFirst()) {
                Log.e("ABC", cursor.getCount() + "");
                for (int i = 0; i < cursor.getCount(); i++) {
    
                    String qid = cursor.getString(cursor.getColumnIndex(TAG_QUOTES_ID));
                    String quotes = cursor.getString(cursor.getColumnIndex(TAG_QUOTES));
                    String cid = cursor.getString(cursor.getColumnIndex(TAG_CAT_ID));
                    String cname = cursor.getString(cursor.getColumnIndex(TAG_CAT_NAME));
                    String quote_time = cursor.getString(cursor.getColumnIndex(TAG_QUOTE_TIME));
    
                    ItemQuotes itemQuotes = new ItemQuotes(qid, quotes, cid, cname, quote_time);
                    arrayList.add(itemQuotes);
    
                    cursor.moveToNext();
                }
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    
        return arrayList;
    }

in First Call When CURRENT_PAGE=1, its loading 10 Quotes, WHEN CURRENT_PAGE= 2, its loading 9 Quotes and so on for next...However I have only 11 Quotes in My Table...I don't know whats wrong with this and how I can fix it. Let me know if someone can here help me to get out of issue. Thanks!

android-sqlite sqlite android java
2 Answers

When CURRENT_PAGE=1 you want OFFSET to be 0, when CURRENT_PAGE=2 you want OFFSET to be 10 and so on.
What you are doing is that you pass CURRENT_PAGE to the argument offset of loadFav() and so you use CURRENT_PAGE as offset.
You should calculate offset as:

(CURRENT_PAGE - 1) * Constant.ITEM_PER_PAGE

So change your sql statement to this:

String query = 
    "SELECT * FROM " + TABLE_QUOTES_FAV + " ORDER BY " + TAG_QUOTES_ID + " ASC " + 
    "LIMIT " + ((offset - 1) * limit) + "," + limit;

But the correct thing to do is change the name of the 1st argument of loadFav() from offset to currentPage:

loadFav(int currentPage, int limit)

so that it is clear what that argument represents and use a variable:

int offset = (currentPage - 1) * limit;

Then you can use your sql statement as it is in your code.

user_104988280 
String query = "SELECT * FROM " + TABLE_QUOTES_FAV + " ORDER BY " + TAG_QUOTES_ID + " ASC "+ " LIMIT " + limit + " OFFSET "+ offset;

QUERY SAMPLE

SELECT * FROM
TABLE_QUOTES_FAV  LIMIT 10 OFFSET 20
user_45622490
Related