Android Room use Type Converter insert success but can't query

Question

Room 2.4.2

I need store the List<Int> data to database , so I convert the numbers to a String,this is convert code , I add it to my database and i insert data success :

class Converter {

    /** list<Int> -> [1][2] */
    @TypeConverter
    fun numberToStr(numbers: List<Int>): String {
        val sb = StringBuffer()
        numbers.forEach {
            sb.append("[$it]")
        }
        return sb.toString()
    }

    /** [1][2] -> list<Int> */
    @TypeConverter
    fun strToNumber(str: String): List<Int> {
        val numbers = mutableListOf<Int>()
        val regex = Regex("""\d+""")
        val numMatchResult = regex.findAll(str).toList()
        numMatchResult.forEach {
            numbers.add(it.value.toInt())
        }
        return numbers
    }
} 

The data in database like this: [15145][15145][15145][15145]... as a TEXT type, and this Entity

@Entity(tableName = "detectAd")
data class DetectAd(
    val sampleNo: String,
    @PrimaryKey(autoGenerate = true)
    val adNo: Int,
    var Ad1: List<Int>,
    var Ad2: List<Int>,
    var Ad3: List<Int>,
    var Ad4: List<Int>,
    var Ad5: List<Int>,
    var Ad6: List<Int>,
    var Ad7: List<Int>,
    var Ad8: List<Int>
)

Then this is Dao and Query function , getAd3Numbers tip error!

// could work
    @Query("SELECT Ad3 FROM detectAd WHERE sampleNo = :sampleNo")
    fun getAd3Str(sampleNo: String): String
// error ,and can't encoding!
    @Query("SELECT Ad3 FROM detectAd WHERE sampleNo = :sampleNo")
    fun getAd3Numbers(sampleNo: String): List<Int>

error message:

The columns returned by the query does not have the fields [value] in java.lang.Integer even though they are annotated as non-null or primitive. Columns returned by the query: [Ad3]
    public abstract java.util.List<java.lang.Integer> getAd3Numbers(@org.jetbrains.annotations.NotNull()

So, it's my code wrong?

Answer 1

What room is saying is that there is no column in the output (result of the query) called value, it has only found a column called ad3.

To overcome that you could give the column an alias/different name by using:-

@Query("SELECT Ad3 AS value FROM detectAd WHERE sampleNo = :sampleNo")
    fun getAd3Numbers(sampleNo: String): List<Int>

BUT you will not get the result you expect. As Room will take the List to be that you expect a number of rows each with a single value of Type Int.

You may then expect List<List<Int>> to work but then Room isn't aware of the TypeConverter and you get

Not sure how to convert a Cursor to this method's return type (java.util.List<java.util.List<java.lang.Integer>>).
public abstract java.util.List<java.util.List<java.lang.Integer>> getAd3NumbersV2(@org.jetbrains.annotations.NotNull()

What you can do, is have a POJO such as :-

data class AdPojo(
    val Ad3: List<Int>
)

• note that using the above POJO means Room will expect the column to be named Ad3 and nothing but. However you could use SELECT Ad4 AS Ad3 FROM detectAd .... to get the Ad4 column (or likewise for Ad1-Ad8)

and then use

@Query("SELECT Ad3 FROM detectAd WHERE sampleNo=:sampleNo")
fun getAd3NumberV3(sampleNo: String): List<AdPojo>

As an example. Data in the database is:-

When run with a breakpoint immediately after val ad3 = adDao.getAd3NumberV3("S001") then when the breakpoint is reached the following is displayed:-