9

I want to integrate Paging with SQLite in existing application. There are around 90 tables in my database so its time consuming to convert to Room. I also tried LIMIT...OFFSET but that takes time to process data every time.

Thanks.

Sagar Zala
  • 4,361
  • 9
  • 29
  • 58
  • You will have to post what you have tried in order to get help. Currently your question is to broad. – HB. Jul 05 '18 at 05:11
  • Hi @HB. Thanks for interest. I just want to know how to integrate `Paging` with `SQLite`. I seen many sites and stack overflow questions but can't get tutorial for `SQLite`. There are some demos for `Room` DB. – Sagar Zala Jul 05 '18 at 05:45
  • Does this answer your question? [Efficient paging in SQLite with millions of records](https://stackoverflow.com/questions/14468586/efficient-paging-in-sqlite-with-millions-of-records) – ggorlen Dec 19 '20 at 18:31

1 Answers1

16

You can use limit and offset . And your query will be like this.

An example:

A) We have an table with 4 record of id column, values are 1 | 2 | 3 | 4 in respective 4 rows.

B) IF perform select * from TABLE_NAME: it returned all record from table in order 1 | 2 | 3 | 4 .

C) If we need total(e.g. limit) 1 record and it should start from offset index 2, then use

select * from TABLE_NAME limit 1 offset 2

This will return record index | 3 | as request limit = 1 & offset index = 2.

Same can be achieved using select * from company limit 2, 1;

Note, here 2 is offset & 1 is limit(order reverse).

Since Android SDK do not provide separate attribute to set offset, in that case last 'limit' attribute of SQLiteDatabase.query(..., limit) can be used as limit 2, 1.

Anisuzzaman Babla
  • 6,066
  • 4
  • 34
  • 58
  • I also tried LIMIT...OFFSET but that takes time to process data every time. – Sagar Zala Jul 17 '18 at 12:04
  • 2
    Also looking at this - found another approach with sql but checking if possible to be done in sqlite - https://dzone.com/articles/why-most-programmers-get-pagination-wrong – user305883 Jan 30 '19 at 13:21
  • 3
    Hi. I downvoted your answer because this solution has bad performance characteristics, even if you have proper indexes. There is an answer with better performance characteristics here: https://stackoverflow.com/questions/14468586/efficient-paging-in-sqlite-with-millions-of-records/14468878 – Noé Rubinstein Feb 26 '20 at 10:36