Android RoomDB dynamic select parameter

Question

in order to cut down on repeated code in my Dao I'm trying to consolidate 4 similar calls into one. I have 4 different queries that are exactly the same except they SELECT a different value from the row. To determine what value I select, I want to pass in a variable which is a string, which corresponds to the entity attribute value:

@Query("""
        SELECT :infoType
        FROM entry_log 
        WHERE date >= date(:startDate) 
        AND date < date(:endDate)
        ORDER BY datetime(date) DESC
        """)
    fun getEntryInfoInDateRange(startDate: String, endDate: String, infoType: String): Observable<List<Int?>>

When I call this, I get back a list with the correct number of elements, but all of them are 0 (which is incorrect)

However, if I have:

        SELECT infotypeone
        FROM entry_log 
        WHERE date >= date(:startDate) 
        AND date < date(:endDate)
        ORDER BY datetime(date) DESC
        """)
    fun getEntryInfoInDateRange(startDate: String, endDate: String, infoType: String): Observable<List<Int?>>

I will be given a list with the correct data (they aren't all 0)

Anyone know if there's some limitation to using parameters in the SELECT field? The parameters in the WHERE/AND fields work just fine

@CôngHải I pass "infotypeone" as a string in the first case — Mackalester
– Mackalester, Commented Jun 10, 2020 at 3:06
Parameters can only be used to replace literal values, such as quoted strings or numeric values. Parameters cannot be used in place of identifiers, such as table names or column names. See this related question. — Bob Snyder
– Bob Snyder, Commented Jun 10, 2020 at 4:45
@BobSnyder Ah I see, that makes sense. Thanks for clearing that up and linking the python sql post — Mackalester
– Mackalester, Commented Jun 11, 2020 at 2:47

Community · Accepted Answer · 2020-06-20 09:12:55Z

In theory there is one way to do something like you want with @RawQuery. This method has some limitations (for example, you shouldn't expect for type converters to work, and I'm not sure it works seamless with RxJava/LiveData, may be there are others?), but still you can try.

From documentation:

If you know the query at compile time, you should always prefer Query since it validates the query at compile time and also generates more efficient code since Room can compute the query result at compile time

On the other hand, RawQuery serves as an escape hatch where you can build your own SQL query at runtime but still use Room to convert it into objects.

In your Dao there would be two methods (you can play with Observable, but I'm not sure it can digest that):

@RawQuery
fun getEntryInfoInDateRangeTemplate(query: SupportSQLiteQuery): Observable<List<Int>>

fun getEntryInfoInDateRange(startDate: String, endDate: String, infoType: String):Observable<List<Int?>> {
    val query = 
    """
        SELECT infoType
        FROM entry_log 
        WHERE date >= date(?startDate) 
        AND date < date(?endDate)
        ORDER BY datetime(date) DESC
        """.replace("infoType", infoType)
   return getEntryInfoInDateRangeTemplate(SimpleSQLiteQuery(query, arrayOf(startDate, endDate)))
}

And as a result you can call the second method from Repository/ViewModel.

Collectives™ on Stack Overflow

Android RoomDB dynamic select parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related