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 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.

Answer 3

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.

Answer 4

Use SupportSQLiteQuery.

https://developer.android.com/reference/android/arch/persistence/db/SupportSQLiteQuery

Latest release 1.1.1 now uses SupportSQLiteQuery.

A query with typed bindings. It is better to use this API instead of rawQuery(String, String[]) because it allows binding type safe parameters.

@Dao
     interface RawDao {
         @RawQuery(observedEntities = User.class)
         LiveData<List<User>> getUsers(SupportSQLiteQuery query);
     }

Usage:

     LiveData<List<User>> liveUsers = rawDao.getUsers( new 
SimpleSQLiteQuery("SELECT * FROM User ORDER BY name DESC"));

Update your gradle to 1.1.1

implementation 'android.arch.persistence.room:runtime:1.1.1'
implementation 'android.arch.lifecycle:extensions:1.1.1'
annotationProcessor "android.arch.persistence.room:compiler:1.1.1"

Note: if you upgrade to 1.1.1, and are using String instead of SupportSQLiteQuery,

you will get the error:

RawQuery does not allow passing a string anymore. Please use android.arch.persistence.db.SupportSQLiteQuery.

Using SupportSQLiteQuery as above will solve the problem.

Note: Make sure you pass in SupportSQLiteQuery query parameter or you will get this error:

RawQuery methods should have 1 and only 1 parameter with type String or SupportSQLiteQuery

Answer 5

There is no something like optional parameter in Room, but there is a @RawQuery annotation where you can pass query as a String so you can build your SQL query in the runtime. I think this will work for you.

Here is the example from the Offical documentation:

@Dao
 interface RawDao {
     @RawQuery
     User getUser(String query);
 }

And here is how you can use it:

User user = rawDao.getUser("SELECT * FROM User WHERE id = 3 LIMIT 1");

Important: RawQuery methods must return a non-void type

Important: This is available in Room 1.1.0-alpha3

Answer 6

Make it more simple. I'll show you example using where clause using two variable. Do it like this:

@Query("SELECT * FROM Student WHERE stdName1= :myname AND stdId1=:myid")
List<Student> fetchAllData(String myname,int myid);

stdName1 and stdId1 are column names

Answer 7

For Kotlin-Room-ViewModel

@Query("SELECT * FROM schedule_info_table where schedule_month = :monthValue ORDER BY schedule_date_time ASC")
fun getThisMonthSchedules(monthValue: Int): Flow<List<SchedulesInformation>>

Answer 8

@Query("select * from task where state = :states and sentdate between :fromdate and :todate")
  List<Task> getFilterAll(String states, String fromdate, String todate);

Here we need to use column name state. Whenever the need to achieve a custom query just pass the value through the parameter from the activity or fragment will get into the interface we will apply inside the query. like the example in the above code (:fromdate, :todate)

Colon is a must. Which parameter are you will going to use inside the query we will mention before starting with the : symbol.

Answer 9

@Anderson K & @Juanky Soriano, I agree with @CommonsWare,

There are some Limitations in Room Library, then also We can write a fully dynamic query on Room Database by using the @query() of Support SQLite Database

String mQuery = "SELECT * FROM foobar WHERE columnName1 IN ('value_1','value_2') and columnName2 In('value_3','value_4')";

AppDatabase appDatabase = Room.databaseBuilder(getApplicationContext(),
        AppDatabase.class, "database-name").build();

Cursor mCursor = AppDatabase.getAppDatabase(context).getOpenHelper().getReadableDatabase().query(myQuery);

Now you can convert cursor row-wise data to your POJO class.

Answer 10

Raw query is deprecated as per the documentation