I need to dynamic to set the Select column , When I use this code ,it can't return the column values

@Query("SELECT :channel FROM detectAd WHERE sampleNo = :sampleNo ORDER BY adNo ASC")
    fun getSampleChannelAd(sampleNo: String, channel: String ="Ad1"): List<Int>

So ,how to dynamic set the column to query, I don't want to write a lot of code like this ,although this could work!

 @Query("SELECT Ad1 FROM detectAd WHERE sampleNo = :sampleNo ORDER BY adNo ASC")
    fun getSampleChannelAd(sampleNo: String): List<Int>

 @Query("SELECT Ad2 FROM detectAd WHERE sampleNo = :sampleNo ORDER BY adNo ASC")
    fun getSampleChannelAd2(sampleNo: String): List<Int>
 ...

CodePudding user response：

I got a answer from friends: need use the @RowQuery then write the custom SQL code

To my question the solution code:

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

val querySingleAd = "SELECT $channel FROM detectAd WHERE sampleNo = $sampleNo ORDER BY adNo ASC"
sqlQuery(SimpleSQLiteQuery(querySingleAd))

CodePudding user response：

When you code a parameter, Room uses SQLite binding to replace the parameter (e.g. :channel) with the supplied value. SQLite does not allow such substitution (parameter binding) for component names (table, columns, triggers, views, indexes).

As such you would have to build the query with the component name using a rawQuery or you could use the CASE WHEN THEN ELSE END construct

The issue with using a RawQuery is that Room cannot properly check the SQL, as per:-

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 (e.g. it does not need to account for possibly missing columns in the response).

• https://developer.android.com/reference/androidx/room/RawQuery

Here's an example of the CASE WHEN THEN ELSE END (else being optional)

@Query("SELECT CASE WHEN :channel = 'ad1' THEN ad1 WHEN :channel = 'ad2' THEN ad2 END FROM detectAd WHERE sampleNo = $sampleNo ORDER BY adNo ASC;")

• You may find this link of interest, it covers both parameter binding and also CASE WHEN THEN ELSE END.
• Note that you may wish to use ELSE n (where n is a number that indicates neither ad1 or ad1 was passed e.g. -1 but that depends upon what you are trying to achieve).
  • e.g. @Query("SELECT CASE WHEN :channel = 'ad1' THEN ad1 WHEN :channel = 'ad2' THEN ad2 ELSE -1 END FROM detectAd WHERE sampleNo = $sampleNo ORDER BY adNo ASC;")