[FIXED] How to dynamically query the room database at runtime?

Issue

The problem

Is it possible construct a query at runtime?

Use case

@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);

The limit part is optional. That is, it should be able to perform the same query with or without limit.

A more complicated use case

In the previous case, it is possible to make two static queries with and without limit part and appropriate one can be used each time. But sometimes we may have to deal with more complex situations like building a filter.

In that case, unlike the previous example, there will be multiple number of optional parts. For a table of books, we may need to do filtering according to the category the book belongs to, author name, price range, publication date etc. It is almost impossible to make static queries with all combinations of these parts.

Solution

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.

Answered By - Juanky Soriano
Answer Checked By - Willingham (JavaFixing Volunteer)