SQLite Select Query returning null

Question

Following SQLite query (with room database library) returning null result. Which is incorrect, as you can see in the attached image.

@Query("SELECT * FROM REPOSITORY_DATABASE_TABLE WHERE item_category LIKE :key")
fun getItemByCategory(key: String) : LiveData<List<Item>>?

@Query("SELECT * FROM REPOSITORY_DATABASE_TABLE WHERE item_storage_type = :key ORDER BY item_name")
fun getItemByCategories(key: String) : LiveData<List<Item>>?

where other queries as follow returning correct result in running application as well as in Android Debug Database

@Query("SELECT * FROM REPOSITORY_DATABASE_TABLE ORDER BY item_name")
fun getAllItems() : LiveData<List<Item>>

Item Data structure:

@Entity(tableName = ApplicationConstants.REPOSITORY_DATABASE_TABLE_NAME)
data class Item(
@PrimaryKey(autoGenerate = false) @SerializedName("item_id")
var itemID: Long = 0L,

@ColumnInfo(name = "item_guid") @SerializedName("item_guid")
var itemGUID: String = "0",

@ColumnInfo(name = "item_name") @SerializedName("item_name")
var itemName: String = "Mango",

@ColumnInfo(name = "item_category") @SerializedName("item_category")
var itemCategory: String = "Fruit",

@ColumnInfo(name = "item_weight") @SerializedName("item_weight")
var itemWeight: Int = 0,

@ColumnInfo(name = "item_count") @SerializedName("item_count")
var itemCount: Int = 1,

@ColumnInfo(name = "item_image") @SerializedName("item_image")
var itemImage: String = "https://stackoverflow.com/questions/62131564/a-failure-occurred-while- 
executing-org-jetbrains-kotlin-gradle-internal-kaptexec",

@ColumnInfo(name = "item_notes") @SerializedName("item_notes")
var itemNotes: String = "Sweet Yellow Mango",

@ColumnInfo(name = "item_display_quantity") @SerializedName("item_display_quantity")
var itemDisplayQuantity: String = "0",

@ColumnInfo(name = "item_storage_type") @SerializedName("item_storage_type")
var itemStorageType: String = "0",

@ColumnInfo(name = "item_creation_date") @SerializedName("item_creation_date")
var itemCreationDate: String = "0",

@ColumnInfo(name = "item_is_checked") @SerializedName("item_is_checked")
var itemIsChecked: String = "0",

@ColumnInfo(name = "item_local_status") @SerializedName("item_local_status")
var itemLocalStatus: String = "0",

@ColumnInfo(name = "item_last_added") @SerializedName("item_last_added")
var itemLastAdded: String = "0",

@ColumnInfo(name = "item_notification_status") @SerializedName("item_notification_status")
var itemNotificationStatus: String = "0",

@ColumnInfo(name = "item_priority") @SerializedName("item_priority")
var itemPriority: String = "0",

@ColumnInfo(name = "item_notification_days") @SerializedName("item_notification_days")
var itemNotificationDays: Int = 1,

@ColumnInfo(name = "item_expiry") @SerializedName("item_expiry")
var itemExpiry: String = "0",

@ColumnInfo(name = "item_synonyms") @SerializedName("item_synonyms")
@TypeConverters(Converters::class)
var itemSynonyms: List<String> = listOf("0","0")
)

Also while debugging the database with Android Debug Database I am getting correct response as shown in following image

Android Debug Database Screenshot showing correct response

Answer 1

Try removing the null safety operator from the return type of the query like this:

fun getItemByCategory(key: String) : LiveData<List<Item>>

And you don't have to use @SerializedName() if the SerializedName name is going to be the same as the variable itself.

And also it is very recommended if you use @PrimaryKey(autoGenerate = True) instead of false. Otherwise, you have to manually update the PrimaryKey every time you modify your table.