How can I write a query which require parameter for SQLite with Room in Android Studio?

Question

I know I can use Code A to a accept a parameter for id

I hope to accept a order by parameter in Code B, how can I do?

You know there are four methods for order by , there are :

SELECT * FROM info_table ORDER BY createdDate desc
SELECT * FROM info_table ORDER BY createdDate asc
SELECT * FROM info_table ORDER BY title desc
SELECT * FROM info_table ORDER BY title asc

Code A

@Query("SELECT * FROM info_table where id=:id")
suspend fun getByID(id:Int): RecordEntity

Code B

@Query("SELECT * FROM info_table ORDER BY createdDate desc")
fun listAll(): Flow<List<RecordEntity>>

Answer 1

There are basically 3 ways that you can do this.

• a fourth way, using CTE's, can be found at How can I add the sencond sort item when I use CASE to require parameter for SQLite with Room in Android Studio?

Option 1.

Have 4 @Query annotated functions for each option and then have a 5th function that invokes the respective function of the 4 based upon parameters. (option 3 uses an alternative numeric representation for the 4 options)

e.g.

@Query("SELECT * FROM info_table ORDER BY createdDate desc")
fun selectOption1(): List<RecordEntity>
@Query("SELECT * FROM info_table ORDER BY createdDate asc")
fun selectOption2(): List<RecordEntity>
@Query("SELECT * FROM info_table ORDER BY createdDate desc")
fun selectOption3(): List<RecordEntity>
fun selectOption4(): Flow<List<RecordEntity>>
fun selectAppropriateOption(orderColumn: String, ifASC: Boolean): Flow<List<RecordEntity>> {
    if (orderColumn.equals("createdDate"))
        if (ifASC) {
            return selectOption2()
        } else {
            return selectOption1()
        }
    if (orderColumn.equals("title"))
        if (ifASC) {
            return selectOption4()
        } else {
            return selectOption3()
        }
    /* handle neither orderColumn????? */
    return selectOption1() /* e.g. default */
}

Option 2. using @RawQuery

Have something along the lines of:-

@RawQuery
fun rq(query: SimpleSQLiteQuery): List<RecordEntity>
fun selectViaRawQuery(orderColumn: String, ifASC: Boolean): Flow<List<RecordEntity>> {
    var orderType = " ASC "
    if (!ifASC) orderType = " DESC "
    return flow { emit(rq(SimpleSQLiteQuery("SELECT * FROM info_table ORDER BY $orderColumn $orderType"))) }
}

• Noting that Room cannot check the validity of the query at compile time and thus there is the greater chance of a run time error.

Option 3.

The last option is to utilise the CASE WHE THEN ELSE END construct. This answer is very much the same as for your question (2 columns and both order types).

e.g.

@Query(
    "SELECT * FROM info_table ORDER BY " +
            "CASE WHEN :parameter = 0 THEN createdDate END DESC, " +
            "CASE WHEN :parameter = 1 THEN createdDate END ASC, " +
            "CASE WHEN :parameter = 2 THEN title END DESC, " +
            "CASE WHEN :parameter = 3 THEN title END ASC"
)
fun selectViaCase(/*childId: Int,*/ parameter: Int): Flow<List<RecordEntity>>

All the above have been tested (compiled and run), but on the main thread and hence no suspend or equivalent.