Android ROOM - LiveData not triggered when using custom insert query

Question

I'm trying to store values of some variable that my application regulary obtains from API. I whant to add new row to the database table only when variable changes its value to be able to show user some kind of "history of changes". I'm using ROOM for storing data.

I've created an entity:

@Entity(tableName = "balance_history",
        indices = {@Index("received_at")})
public class BalanceResponse {
    //region getters & setters
    ...
    //endregion

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    private long mId;

    @ColumnInfo(name = "money")
    private double mMoney;

    @ColumnInfo(name = "received_at")
    private DateTime mReceivedAt;
}

Dao:

@Dao
public abstract class DatabaseDao {
    @Query("SELECT * FROM balance_history ORDER BY received_at DESC LIMIT 1")
    public abstract LiveData<BalanceResponse> selectLatestBalanceResponse();

    public void insertNewBalanceResponse(BalanceResponse balanceResponse) {
        String sqlRequest = "INSERT INTO balance_history(money, received_at) " +
                "SELECT ?, ? " +
                "WHERE NOT EXISTS(SELECT 1 FROM (SELECT * FROM balance_history ORDER BY received_at DESC LIMIT 1) WHERE money = ?);";

        SupportSQLiteDatabase database = DatabaseStorage.getInstance().getAppDatabase().getOpenHelper().getWritableDatabase();
        database.execSQL(sqlRequest,
                new Object[]{balanceResponse.getMoney(), balanceResponse.getReceivedAt().getMillis(), balanceResponse.getMoney()});
    }
}

Database object:

@Database(entities = {BalanceResponse.class}, version = 1)
@TypeConverters(DateTimeConverter.class)
public abstract class AppDatabase
        extends RoomDatabase {

    public abstract DatabaseDao getDatabseDao();
}

Singleton for storing single database object:

public class DatabaseStorage {
    //region singleton
    private static final DatabaseStorage ourInstance = new DatabaseStorage();

    public static DatabaseStorage getInstance() {
        return ourInstance;
    }
    //endregion

    @NonNull
    public AppDatabase getAppDatabase() {
        return mAppDatabase;
    }

    @NonNull
    private AppDatabase mAppDatabase;

    private DatabaseStorage() {
        mAppDatabase =
                Room.databaseBuilder(MyApp.getAppContext(), AppDatabase.class, "app-database")
                .build();
    }

}

And viewmodel that I instantiate in my Activity's onCreate():

public class BalanceView implements Observer<BalanceResponse> {

    private LiveData<BalanceResponse> mLatestBalanceResponse;

    public BalanceView(LifecycleOwner lifecycleOwner){
        mLatestBalanceResponse = DatabaseStorage.getInstance().getAppDatabase().getDatabseDao()
                .selectLatestBalanceResponse();
        mLatestBalanceResponse.observe(lifecycleOwner, this);
        //finding views here
    }

    @Override
    public void onChanged(@Nullable BalanceResponse balanceResponse) {
        //displaying changes here
    }
}

I've expected triggering of BalanceView.onChanges() method each time when method DatabaseDao.insertNewBalanceResponse() inserts a row.

Actually BalanceView.onChanges() method never gets fired. Why is that so? How can I accomplish this?

p.s. However, If I replace method DatabaseDao.insertNewBalanceResponse() with original:

@Insert
public abstract Long insertBalanceResponse(BalanceResponse balanceResponse);

Everithing works fine and method onChange() gets invoked. But this kind of insert statement doesn't fit my needs.

Answer 1

I have the same issue and here I got a hint to solve this issue.

@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);

For More details, check https://developer.android.com/reference/android/arch/persistence/room/RawQuery.