How to dynamically query the room database at runtime?

user_3802122 0
Viewed 54402

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.

android-room android
10 Answers

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
user_75949610

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

user_4363410

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

user_56974740

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

user_88602580
user_73708370
Related