Need to put a query in a loop to return result set one by one

Question

I have SQLite DB query which returns me one record at a time. This is the query:

rawQuery("SELECT id, category_id, title, text FROM customer WHERE (reads = (SELECT MIN(minReads) FROM categories)) AND status = 'a' ORDER BY rating DESC, rand_index DESC LIMIT 1 ",null);

If I want to fetch next record as we are applying limit in my SQLite query above I'm unable to get next record. Limit = 1 is necessary as I have thousands of record in my db.

How can I fetch next record without modifying the existing query. I cannot apply a loop on this as I have lots of records and I don't want all the records at the same time. I want it to be fetched dynamically one by one. I want to be something like where I can store result in an array as I might need old records as well while getting the next record.

Any input will be a great help.

Answer 1

You should obtain all records and then loop over them in your local class object form, its a really bad idea to do looping sql query's, thats really slow.

Answer 2

You may just get some reasonable amount of records with one query, for example 100 records with LIMIT 100, then get the additional record portions with LIMIT 100 OFFSET (n-1)*100 for each n-th records' portion.

Basically, it's a simple pagination implementation.