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)

[Call `query()` on your `RoomDatabase`](https://developer.android.com/reference/android/arch/persistence/room/RoomDatabase.html#query(java.lang.String,%20java.lang.Object[])), which is largely a pass-through to `rawQuery()` of the underlying `SQLiteDatabase`. If you were not using `LIKE` (and instead were comparing `textValue` based on equality), you could use `IN`, which Room has direct support for. — CommonsWare, Jun 19 '17 at 19:21
Possible duplicate of [How to dynamically query the room database at runtime?](https://stackoverflow.com/questions/44287465/how-to-dynamically-query-the-room-database-at-runtime) — Bertram Gilfoyle, Aug 18 '18 at 17:58

live-love · Accepted Answer · 2019-07-04T19:13:44.927

15

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"

edited Jul 04 '19 at 19:13

answered Mar 17 '18 at 02:19

live-love

48,840
22
240
204

This solution throws ** error: RawQuery does not allow passing a string anymore. Please use android.arch.persistence.db.SupportSQLiteQuery.** – mohammed nathar Oct 26 '18 at 11:10
@mohammednathar I just went through this and got this error with string but this answer is updated to address that (obviously, which is how i stumbled on this page after seeing that error :) ) – nAndroid Mar 03 '21 at 14:58

score 0 · Answer 2 · answered Sep 08 '17 at 01:17

The problem is you want to pass a part of SQL statement, but Room treats it like a query parameter.

If you want you can try to use Kripton Persistence Library, an open source library written (by me :) ) that drastically simplify SQLite's management code for Android platform and support situations like this.

Kripton works with DAO pattern too, so concept are quite similar. Just to write an example that fit your needs:

Given a model class:

@BindType
public class User {
    public long id;
    public String name;
    public String username;
    public String email;
    public Address address;
    public String phone;
    public String website;
    public Company company;
}

a DAO definition:

@BindDao(User.class)
public interface UserDao {
    @BindSqlInsert
    void insert(User bean);

    @BindSqlSelect
    List<User> selectDynamic(@BindSqlDynamicWhere String where, @BindSqlDynamicWhereParams String[] args);
}

and a data source definition:

@BindDataSource(daoSet={UserDao.class}, fileName = "kripton.quickstart.db", generateAsyncTask = true)
public interface QuickStartDataSource {
}

Kripton will generate at compile time all code is need to work with database. So to accomplish your task with Kripton you have to write a code similar to:

BindQuickStartDataSource ds = BindQuickStartDataSource.instance();
// execute operation in a transaction
ds.execute(new BindQuickStartDataSource.SimpleTransaction() {
  @Override
  public boolean onExecute(BindQuickStartDaoFactory daoFactory) throws Throwable 
  {
    UserDaoImpl dao = daoFactory.getUserDao();
    String[] p={"hello"};
    dao.selectDynamic("name=?",p);
    return true;
  }
});

In logcat when code above is executed you will see the generated log:

database OPEN READ_AND_WRITE_OPENED (connections: 1)
UserDaoImpl, selectDynamic (line 352): SELECT id, name, username, email, address, phone, website, company FROM user WHERE name=?
selectDynamic (line 357): ==> param0: 'hello'
Rows found: 0
database CLOSED (READ_AND_WRITE_OPENED) (connections: 0)

Kripton obviously supports static where conditions too and many other features (i start to develop it in 2015).

For more information about Kripton Persistence Library:

Generating a query at runtime using Room Persistance

2 Answers2