Generating a query at runtime using Room Persistance

Question

I want to run queries on my SQLite database that have been generated at runtime (instead of the standard compiletime queries in the @Dao). For example I might want to search a TEXT column in the SQLite db, to see if it contains all words in a list of N length. In raw SQLITE, a query where N is 3 would look like this :

SELECT * FROM table 
WHERE textValue LIKE %queryTerm1% 
AND textValue LIKE %queryTerm2%"
AND textValue LIKE %queryTerm3%"

I have tried generating, and passing the end of the query, instead of just passing variables. For example :

String generatedQuery = "textValue LIKE %queryTerm1% AND textValue LIKE %queryTerm2% AND textValue LIKE %queryTerm3%";
tableDao.find(generatedQuery);

and in the @Dao:

@Query("SELECT * FROM tableName WHERE :endQuery")
List<POJO> find(String endQuery);

This doesn't seem to work for me. Do you have any idea how to get runtime generated queries working with Room?

PS:

I have debugged the Dao implementation and looked at the statement it is running. This confirms that the generated query information, and the query are being passed correctly. I assume this is an issue with SQL injection prevention (aka more of an SQLITE problem, than a Room problem)

Answer 1

Update: latest release 1.1.1 of Room now uses SupportSQLiteQuery instead of String.

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

New Answer:

@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 (or whatever the current version is)

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"