Room - Is it possible to use OFFSET and FETCH NEXT in a query?

Question

I'm trying to implement a pagination build in Room database android, and I need to have the clause OFFSET and FETCH NEXT which is not alowing me to use, what's the way to implement a query using these clauses. Did some research but no results gained. The SQL query I'm trying to implement on ROOM dao looks like this:

SELECT * FROM Persons ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Answer 1

This is how I managed to achieve and it's working as expected:

(Kotlin sample)

SELECT * FROM Persons WHERE name LIKE :param LIMIT :pageSize OFFSET :pageIndex

---

where:

• PARAM = search criteria,

• PAGESIZE = number of rows to return per page,

• PAGEINDEX = what page to return