How to use Paging with SQLite?

Question

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.

Answer 1

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.