带有偏移量和限制的java SQLite分页问题

Question

我正在做一个如下的查询，在一页中加载10个引号

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

而DBHelper.loadFav如下所示

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

在第一次调用中When CURRENT_PAGE=1, its loading 10 Quotes, WHEN CURRENT_PAGE= 2, its loading 9 Quotes and so on for next。。。然而，我的表中只有11个引号。。。我不知道这有什么问题，我怎么能解决它。让我知道这里是否有人能帮我解决这个问题。谢谢

Answer 1

当CURRENT_PAGE=1你想OFFSET成为0时，当CURRENT_PAGE=2你想OFFSET成为10时，依此类推
你要做的是把CURRENT_PAGE传递给loadFav()的参数offset，所以你使用CURRENT_PAGE作为offset
你应该将offset计算为：

(CURRENT_PAGE - 1) * Constant.ITEM_PER_PAGE

因此，将sql语句更改为：

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

但是正确的做法是将loadFav()的第一个参数的名称从offset更改为currentPage：

loadFav(int currentPage, int limit)

因此，很清楚该参数代表什么，并使用变量：

int offset = (currentPage - 1) * limit; 

然后，您可以像在代码中一样使用sql语句

Answer 2

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

查询样本

SELECT * FROM
TABLE_QUOTES_FAV  LIMIT 10 OFFSET 20