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() method, ones that you can map to @Query methods 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() method 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, and it works on all versions of Room. The downside is that you wind up with a Cursor, which is less convenient than the model objects that you get back from @Query methods on your @Dao.

@RawQuery

Room 1.1.0 added a new option for this: @RawQuery. Like @Query, this is an annotation that you can add to a method on your @Dao. And, like @Query, you can have that method 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 method:

@RawQuery
abstract List<Foo> _findMeSomething(SupportSQLiteQuery query);

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

@RawQuery
abstract List<Foo> _findMeSomething(SupportSQLiteQuery query);

List<Foo> findMeSomething(String value) {
  return _findMeSomething(new SimpleSQLiteQuery("SELECT some, columns FROM your_table WHERE something=?",
    new Object[] {value}));
}

Here, findMeSomething() looks like a regular query method on the @Dao. Instead, it creates a SimpleSQLiteQuery for a SQL statement and a supplied value, then uses _findMeSomething() to execute that query and return a List of Foo objects.

In this particular case, findMeSomething() could have been written using a regular @Query annotation, as the SQL statement is known at compile time… assuming that your_table is associated with an @Entity. One scenario where @RawQuery comes into play is when you want to query a table using Room where the table is not associated with an @Entity. We will see an example of that much later in the book, when we examine full-text searching with Room.