How to Dynamically Query the Room Database at Runtime

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

Dynamically build query in Room

I think what you should do is use RawQuery.

Since your query is complicated and you are not sure how long it is, you should probably construct it in runtime and execute it as well.
Room check the validity of the @Query in compile time so I think you'll have a hard time to implement it that way.

This example is taken from the documentation:

@Dao
interface RawDao {
 @RawQuery
 User getUserViaQuery(SupportSQLiteQuery query);
}
 SimpleSQLiteQuery query = new SimpleSQLiteQuery("SELECT * FROM User WHERE id = ?   LIMIT 1", new Object[]{userId});
 User user2 = rawDao.getUserViaQuery(query);

Is there a way to dynamically query the database

I'm sorry to say that this is not currently possible in the way you want.

I have managed to do it by using the db.query(query, values) method. Generate your your query string at runtime (with placeholders '?'), and pass an array of runtime generated values. Note that this will return a Cursor, not an instance of the specific object you want, so you will have to define a method for mapping the Cursor to a POJO.

I've attached some links to my Cursor2Pojo Mapper and an project implementing it. Feel free to use it, as it should solve your problem in a somewhatgraceful manner. It supports list and single instance returns, although requires you add more annotations to your class (Room annotations are class bound so you cannot get them through reflection at runtime)

Cursor2Pojo custom lib

Project Implementation at line 66 - 72

Doesn't the dynamically query the room database support LiveData?

it does support it, but you have to specify the observed entity, as described here
https://developer.android.com/reference/androidx/room/RawQuery example:

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

Room database full dynamic query

You can't use bind variables (parameters) to reference columns in the ORDER BY clause. However, you can use the bind variable in an expression like so:

@Query("select * from coin ORDER BY
CASE :order
WHEN 'percent_change_24h' THEN percent_change_24h
WHEN 'other_column_name' THEN other_column_name
END asc limit :numberOfCoins")
fun getAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>

You will need to add a separate WHEN clause to the CASE statement for each column/expression you want to sort by, and you may need or want to add an ELSE clause for the situations where the :order bind variable doesn't match any of your standard cases.

The restriction on bind variables also holds true for the where clause and the projection (select list). Bind variable have their own values in your examples either String or Int for :order and :numberOfCoins respectively.

Generating a query at runtime using Room Persistance

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"

How to create a new table in Room dynamically?

Is there a way?

There is BUT the dynamically added tables would probably have to be used via traditional (pre-room) methods via a SupportSQLiteDatabase instance (this being Room's equivalent of SQLiteDatabase).

So effectively you are defeating some of the core reasons to utilise Room, such as an Object Orientated approach and the reduction of Boiler-Plate code.

Example

The following simple example creates and populates (if new) a room generated/managed table and then dynamically creates and populates (if new) another table BUT outside of the OO side of room via a SupportSQLiteDatabase instance. Finally all data is extracted from the tables into a Cursor and the data is dumped (to prove the concept).

The App is run twice, to show that the existence of the non-room table does not result in room detecting a changed schema and the resultant exception.

• Note that the above does not take into consideration the management of a variable amount of dynamic tables such as storing/obtaining the table names of the dynamically added tables, this would further complicate matters.

The code is :-

BaseEntity.java

@Entity(tableName = "base")
public class BaseEntity {

    public static final String BASETABLE_NAME = "base";
    public static final String BASETABLE_COL_ID = BaseColumns._ID;
    public static final String BASETABLE_COL_VALUE = "value";
    public static final String BASETABLE_NAME_PLACEHOLDER = ":tablename:";
    public static final String BASETABLE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS "
            + BASETABLE_NAME_PLACEHOLDER +
            "(" +
            BASETABLE_COL_ID + " INTEGER PRIMARY KEY," +
            BASETABLE_COL_VALUE + " TEXT)";
    @PrimaryKey
    @ColumnInfo(name = BASETABLE_COL_ID)
    Long id;
    @ColumnInfo(name = BASETABLE_COL_VALUE)
    String value;

    public BaseEntity() {}

    @Ignore
    public BaseEntity(String value) {
        this.value = value;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    @Ignore
    public static Long insertRow(SupportSQLiteDatabase sdb, String tableName, String value) {
        ContentValues cv = new ContentValues();
        cv.put(BASETABLE_COL_VALUE,value);
        return sdb.insert(tableName, OnConflictStrategy.IGNORE,cv);
    }

    @Ignore
    public static int getTableRowCount(SupportSQLiteDatabase sdb,String tableName) {
        int rv = 0;
        Cursor csr = sdb.query("SELECT count() FROM " + tableName,null);
        if (csr.moveToFirst()) {
            rv = csr.getInt(0);
        }
        csr.close();
        return rv;
    }
}

• As can be seen this is a mixture of Room code and non-room code

BaseEntityDao.java

@Dao
interface BaseEntityDao {

    @Insert
    long insertRow(BaseEntity baseEntity);

    @Query("INSERT INTO base (value) VALUES(:the_value)")
    void insertRow(String the_value);

    @Query("SELECT count() FROM base")
    Integer getRowCount();

}

• The room annotation processor requires the SQLite identifiers (table names, column names) to be as they are, they cannot be variables and hence these can only be used to access the Room defined table (hence the need for equivalents (in this example statically defined in the BaseEntity class)).

Database.java

@androidx.room.Database(version = 1,entities = {BaseEntity.class})
public abstract class Database extends RoomDatabase {

    public abstract BaseEntityDao baseEntityDao();
}

MainActivity.java

public class MainActivity extends AppCompatActivity {

    Database mDB;
    BaseEntityDao mDao;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDB = Room.databaseBuilder(this,Database.class,"basedb")
                .allowMainThreadQueries()
                .build();
        mDao = mDB.baseEntityDao();
        addSomeDataViaRoom();
        String dynamicTableName = "testing";
        addTable(dynamicTableName);
        addSomeDataOutsideOfRoom(dynamicTableName);
        SupportSQLiteDatabase sdb = mDB.getOpenHelper().getWritableDatabase();
        Cursor csr = sdb.query("SELECT * FROM " + BaseEntity.BASETABLE_NAME);
        DatabaseUtils.dumpCursor(csr);
        csr = sdb.query("SELECT * FROM " + dynamicTableName);
        DatabaseUtils.dumpCursor(csr);
        mDB.close();
    }

    private boolean addTable(String tableName) {

        SupportSQLiteDatabase sdb = mDB.getOpenHelper().getWritableDatabase();
        try {
            sdb.execSQL(BaseEntity.BASETABLE_CREATE_SQL.replace(BaseEntity.BASETABLE_NAME_PLACEHOLDER, tableName));
        } catch (SQLiteException e) {
            return false;
        }
        return true;
    }

    private void addSomeDataViaRoom() {
        if (mDao.getRowCount() > 0) return;
        mDao.insertRow("A");
        mDao.insertRow("B");
        mDao.insertRow("C");
    }

    private void addSomeDataOutsideOfRoom(String tableName) {
        SupportSQLiteDatabase sdb = mDB.getOpenHelper().getWritableDatabase();
        if (BaseEntity.getTableRowCount(sdb,tableName) > 0) return;
        BaseEntity.insertRow(sdb,tableName,"X");
        BaseEntity.insertRow(sdb,tableName,"Y");
        BaseEntity.insertRow(sdb,tableName,"Z");
    }
}

Result (2nd run)

2019-10-26 08:04:28.650 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5322d6
2019-10-26 08:04:28.651 I/System.out: 0 {
2019-10-26 08:04:28.651 I/System.out:    _id=1
2019-10-26 08:04:28.651 I/System.out:    value=A
2019-10-26 08:04:28.651 I/System.out: }
2019-10-26 08:04:28.651 I/System.out: 1 {
2019-10-26 08:04:28.651 I/System.out:    _id=2
2019-10-26 08:04:28.651 I/System.out:    value=B
2019-10-26 08:04:28.651 I/System.out: }
2019-10-26 08:04:28.651 I/System.out: 2 {
2019-10-26 08:04:28.651 I/System.out:    _id=3
2019-10-26 08:04:28.651 I/System.out:    value=C
2019-10-26 08:04:28.651 I/System.out: }
2019-10-26 08:04:28.651 I/System.out: <<<<<
2019-10-26 08:04:28.651 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f873957
2019-10-26 08:04:28.652 I/System.out: 0 {
2019-10-26 08:04:28.652 I/System.out:    _id=1
2019-10-26 08:04:28.652 I/System.out:    value=X
2019-10-26 08:04:28.652 I/System.out: }
2019-10-26 08:04:28.652 I/System.out: 1 {
2019-10-26 08:04:28.652 I/System.out:    _id=2
2019-10-26 08:04:28.652 I/System.out:    value=Y
2019-10-26 08:04:28.652 I/System.out: }
2019-10-26 08:04:28.652 I/System.out: 2 {
2019-10-26 08:04:28.652 I/System.out:    _id=3
2019-10-26 08:04:28.652 I/System.out:    value=Z
2019-10-26 08:04:28.652 I/System.out: }
2019-10-26 08:04:28.652 I/System.out: <<<<<

---

Related Topics