How to dynamically query the room database at runtime?

android room query
raw query room android
android room database crud example
android-room like query
search in room database android
android-room synchronous query
room database where condition
room query returns null
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.

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.

How to dynamically query the room database at runtime?, The problem Is it possible construct a query at runtime? Use case #Query("​SELECT * FROM playlist " + "WHERE playlist_title LIKE '% :playlistTitle %' " +  @Anderson K & @Juanky Soriano, I'm agree with @CommonsWare, There some Limitation in Room Library, then also We can write fully dynamic query on Room Database by using the @query() of Support SQLite Database

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.

RawQuery, Room will generate the code based on the return type of the function and failure to pass a proper query will result in a runtime failure or an  Tables are generally crucial to a user's interaction with the database. Consequently the absence of tables is fatal. From this it follows, that creating tables on the fly, at runtime, is a bad practice because it means there is no guarantee of the user's experience. If a CREATE TABLE statement fails, for whatever reason, the user is stuffed.

How to dynamically query the room database at runtime?, 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  Hi, The simplest approach is likely to use DbContext.Database.SqlQuery. Else more complex option would be to use the type for this DbSet and creating dynamically a select expression.

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

Using Room Database, They will help you to Room is now considered as a better approach for data… Compile-time verification of SQL queries. each @Query and @Entity is checked at the that does not exist in real database then it will give exception during run time and you can not capture Android Dynamic Feature Modules : The Future. Usually we design the table to have x number of rows and y number of columns in a report. But how can we create a report which adds the rows and columns dynamically at run time based on the result of the source query? For example I want to list stdentId, StudentName and any course each student has enrolled in.

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

7 Pro-tips for Room - Android Developers, Pre-populate your database via RoomDatabase#Callback; Use DAO's inheritance capability; Execute queries in transactions with minimal boilerplate code via  Hello everyone! Last week at Google IO 2017, Google introduced Room persistence library which allows fluent database access while harnessing the full power of SQLite. Basically, it is abstraction…

How to Make an Android Notes App With Room Database , Learn how to build an Android note app with room database. Schema, Fixed schema, dynamic, records can be added on the fly The result of SQLite queries are composed into cursor object, DAO methods abstract the Room dependencies compile 'android.arch.persistence.room:runtime:1.0.0'  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.

Room Guide · codepath/android_guides Wiki · GitHub, Room is Google's new persistence library designed to make it easier to build offline apps. an ORM implementation "androidx.room:room-runtime:$r​oomVersion" fields within our model class that will map to columns in the database table: The query needs to be defined as DAO objects, and the data  1.You want to write a query in your code (C#) that can work as if you are writing and executing query on your (SQL server). Accordingly i am writing a simple code that creates a table (base on the data-table created by your code) in SQL server and inserts records in it from a data-table.

How to fix ORM limitations on Android using Room Architecture , You can use Room Architecture Component together with it and enjoy the advantages of both! Even if N+1 query problem is solved by ORM, you can't get an optimal Step 5 — Dynamically update database hash. In this Hibernate tutorial, I will share with you how to write code that changes database (or schema) dynamically at runtime for a Java application that uses Hibernate framework. Sometimes you have to develop an application that work with multiple databases and it allows the users to switch among different databases smoothly, without restarting

Comments
  • What do you mean by "optional parameter"? Ignoring Room for the moment, how would you implement an optional parameter using traditional Android SQLite APIs?
  • @CommonsWare I mean that i would like dynamically, set or not the limit parameter for the query.
  • OK. Ignoring Room for the moment, how would you implement this using SQLiteDatabase? If the answer is "I would have two different query strings", then why not use two different @Query annotations? Since an annotation can refer to a static field (AFAIK), in principle, you can even reduce redundancy by defining the core part of the query string once and referring to it from each annotation.
  • I understand you, I'm doing a refactoring and as the room is very new, I'm trying to see if there is a better way to do this, but otherwise I'll create two methods.
  • Hello, it's not working in my case. Particular this line "WHERE playlist_title LIKE '% :playlistTitle %' "
  • When i using this method then "playlistTitle" not used error accured
  • This was a great answer. But it is outdated since Room introduced @RawQuery annotation.
  • Or make the Dao an abstract class and delegate the second method to the first with limit parameter set to -1.
  • Is @RawQuery subject to risk of SQL injection attack and therefore a security risk?
  • No. It is not plain text query as the name make us think. It uses bind variables internally. SimpleSQLiteQuery, to be more precise.