Dynamic Queries

Sometimes, you do not know the query at compile time.

One scenario for this is when you want to expose a Room-managed database via a ContentProvider to third-party apps. You could document that you support a limited set of options in your provider’s query() function, ones that you can map to @Query functions on your DAO. Alternatively, you could generate a SQL statement using SQLiteQueryBuilder that supports what your table offers, but then you need to somehow execute that statement and get a Cursor back.

You have a few options for handling this sort of situation.

query()

RoomDatabase has a query() function that is analogous to rawQuery() on a SQLiteDatabase. Pass it the SQL statement and an Object array of position parameters, and RoomDatabase will give you a Cursor back.

The benefit is that this is quick and easy. The downside is that you wind up with a Cursor, which is less convenient than the model objects that you get back from @Query functions on your @Dao.

@RawQuery

Another option is @RawQuery. Like @Query, this is an annotation that you can add to a function on your @Dao. And, like @Query, you can have that function return instances of an @Entity or other POJO.

However, rather than supplying a fixed SQL statement in the annotation, you provide a SupportSQLiteQuery object as a parameter to the @RawQuery function:

@RawQuery
fun _findMeSomething(query: SupportSQLiteQuery): List<Foo>

A SupportSQLiteQuery comes from the support database API, which is how Room interacts with your SQLite database. Fortunately, for the purposes of using @RawQuery, the only thing that you need from that API is SimpleSQLiteQuery. Its constructor takes the same two parameters as does rawQuery() on a SQLiteDatabase:

• The SQL statement to execute, and
• An Object array of values to use to replace positional placeholders

So, you can wrap your query and placeholder values in a SimpleSQLiteQuery, pass that to your @RawQuery-annotated function, and Room will take care of the rest.