How to dynamically query the room database at runtime?

Room supports @RawQuery annotation to construct queries at run-time.

Step 1 : Make DAO method

Mark the DAO method with @RawQuery annotation instead of normal @Query.

@Dao
interface BooksDao{
    @RawQuery
    List<Book> getBooks(SupportSQLiteQuery query);
}

Step 2 : Construct the query

Room uses prepared statements for security and compile time verification. Therefore, while constructing queries, we need to store query string and bind parameters separately.

In this example, I use the variable queryString for query string and args for bind parameters.

^{(Please note that I used text editor to write code. Therefore there may be typo or simple syntax errors. If you find anything please let me know in the comments or edit the post.)}

// Query string
String queryString = new String();

// List of bind parameters
List<Object> args = new ArrayList();

boolean containsCondition = false;

// Beginning of query string
queryString += "SELECT * FROM BOOKS";

// Optional parts are added to query string and to args upon here

if(!authorName.isEmpty()){
    queryString += " WHERE";
    queryString += " author_name LIKE ?%";
    args.add(authorName);
    containsCondition = true;
}

if(fromDate!=null){
    
    if (containsCondition) {
        queryString += " AND";
    } else {
        queryString += " WHERE";
        containsCondition = true;
    }

    queryString += " publication_date AFTER ?";
    args.add(fromDate.getTime());
}

if(toDate!=null){
    
    if (containsCondition) {
        queryString += " AND";
    } else {
        queryString += " WHERE";
        containsCondition = true;
    }

    queryString += " publication_date BEFORE ?";
    args.add(toDate.getTime());
}

// End of query string
queryString += ";";

Step 3 : Perform query

SimpleSQLiteQuery query = new SimpleSQLiteQuery(queryString, args.toArray());
List<Book> result = booksDao.getBooks(query);

---

Notes

• Like normal Query, RawQuery supports returning raw cursors, entities, POJOs and POJOs with embedded fields
• RawQuery supports relations

Instead of writing multiple query i refer pass negative value to limit clause. Because if there is change in query i have to update the both query which is more error prone.

Official doc -> If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned. you can find it here https://sqlite.org/lang_select.html and read the limit clause section.

So I would do somthing like this,

@Query("SELECT * FROM playlist " +
    "WHERE playlist_title LIKE '% :playlistTitle %' " +
    "GROUP BY playlist_title " +
    "ORDER BY playlist_title " +
    "LIMIT :limit")
List<IPlaylist> searchPlaylists(String playlistTitle, int limit);

and pass negative when you don't want to apply filter.

return playlistDao.searchPlaylists(title, limit.isPresent() ? limit.get() : -1)

It's working in my case.

Updated [21 Dec 2018]

In case If you are using kotlin use default value.

@JvmOverloads
@Query("SELECT * FROM playlist " +
        "WHERE playlist_title LIKE '% :playlistTitle %' " +
        "GROUP BY playlist_title " +
        "ORDER BY playlist_title " +
        "LIMIT :limit")
fun searchPlaylists(playlistTitle: String, limit: Int = -1): List<IPlaylist>

@JvmOverloads to make it compatiable with Java. It generate two separate methods for Java.

In my experience (short) using Room that's not possible, and not because of being a Room limitation but, as implicitly commented by @CommonsWare, a limitation on SQLite. You need two queries, and therefore two methods in your DAO.

I would have something like:

@Query("SELECT * FROM playlist " +
    "WHERE playlist_title LIKE '% :playlistTitle %' " +
    "GROUP BY playlist_title " +
    "ORDER BY playlist_title " +
    "LIMIT :limit")
List<IPlaylist> searchPlaylists(String playlistTitle, int limit);

@Query("SELECT * FROM playlist " +
    "WHERE playlist_title LIKE '% :playlistTitle %' " +
    "GROUP BY playlist_title " +
    "ORDER BY playlist_title ")
List<IPlaylist> searchPlaylists(String playlistTitle);

Then somewhere else you do the bypass:

if (limit.isPresent()) {
   return playlistDao.searchPlaylists(title, limit.get());
} else {
   return playlistDao.searchPlaylists(title);
}

That's the best option I can think of at the moment.