How to dynamically query the room database at runtime?

Question

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.

Answer 1

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

Answer 2

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 do 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 at the moment.