Paged Room Queries

Sometimes, we simply have too much data.

It is very easy to write a DAO that returns all rows from a particular table. Whether doing that is sensible or not will depend on:

• The number of rows in that table
• The number, types, and contents of columns in that table
• The impacts of any JOINs or other constructs that might expand the result set

If we know that we might have a ridiculous amount of data, we can use Room’s support for the Jetpack Paging library. This teaches Room to retrieve rows a “page” at a time from the underlying table(s), instead of the full result set all at once. This can greatly reduce the amount of memory that is consumed at once, if we can organize our UI to only need a page’s worth of data at once. The downside is that the only easy way to consume this paged data is via a RecyclerView — anything else is terribly complicated.

In this chapter, we will explore a bit about the Paging library in general and show how Room and Paging can populate a RecyclerView as the user scrolls.

The Problem: Too Much Data

One of the little-known issues with Android’s SQLite API is how the Cursor works. We tend to just use that Cursor and ignore exactly how it is getting its data. The behavior of our database Cursor is normal for smaller data sets but possibly problematic for really large ones.

Cursor is an interface. The real Java class that we get back from SQLite is a SQLiteCursor. The Cursor API, and SQLiteCursor in particular, was developed well before Android 1.0 was released, and therefore has a fair share of “features” that seemed like good ideas at the time but did not hold up well as the years progressed. The one that everybody encounters is the fact that when you get a Cursor back from methods like query() or rawQuery() on a SQLiteDatabase, the query has not actually been done yet. Instead, it is lazy-executed when you ask the Cursor for something where the data is needed, such as getCount(). This is a pain, as we want to do the database I/O on a background thread, so we have to specifically do something while on that background thread (e.g., call getCount()) to ensure that the query really does get executed when we expect it to.

Another quirk with Cursor is that when the query is executed, it really populates a CursorWindow. For small queries, this will represent the entire result set. For larger queries, it is a portion of that result set. As we move through the Cursor, SQLiteCursor will load more relevant rows into the CursorWindow, around the new position. This exacerbates the threading problem, as we might wind up doing disk I/O at any point while working with the Cursor, if the window’s contents need to be adjusted.

Ideally, your queries are small, within the CursorWindow limits. And for apps where the data comes from the user, usually you can keep your queries small. Users are only going to enter in so much data on a small screen. Even if the user records some form of multimedia — such as taking a picture with the camera — large queries can be avoided by not storing the media in the database itself, but rather storing it in plain files referenced by the database.

However, in cases where the data comes from some server, sticking with small queries can get tricky.