Room查询返回空列表：无法读取SQLite数据库数据求助

我尝试从Room数据库读取数据，SQLite表entertainment内有2行数据，但每次查询返回的列表长度始终为0。已通过onOpen和onCreate方法验证代码可访问asset文件夹，日志输出正常，也核对过表名与列名。

数据库信息 #

数据库截图 #

SQLite建表语句 #

CREATE TABLE "entertainment" (
    "id"    INTEGER,
    "categoryId"    INTEGER,
    "difficulty"    INTEGER,
    "title1"    TEXT,
    "title2"    TEXT,
    "title3"    TEXT,
    "title4"    TEXT,
    "title5"    TEXT,
    "imageUrl1" TEXT,
    "imageUrl2" TEXT,
    "imageUrl3" TEXT,
    "imageUrl4" TEXT,
    "imageUrl5" TEXT,
    PRIMARY KEY("id" AUTOINCREMENT)
);

相关代码 #

数据类 #

@Entity(tableName = "entertainment")
data class Entertainment(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") val id: Int,
    @ColumnInfo(name = "categoryId") val categoryId : Int,
    @ColumnInfo(name = "difficulty") val difficulty: Int,
    @ColumnInfo(name = "title1") val title1: String,
    @ColumnInfo(name = "title2") val title2: String,
    @ColumnInfo(name = "title3") val title3: String,
    @ColumnInfo(name = "title4") val title4: String,
    @ColumnInfo(name = "title5") val title5: String,
    @ColumnInfo(name = "imageUrl1") val imageUrl1: String,
    @ColumnInfo(name = "imageUrl2") val imageUrl2: String,
    @ColumnInfo(name = "imageUrl3") val imageUrl3: String,
    @ColumnInfo(name = "imageUrl4") val imageUrl4: String,
    @ColumnInfo(name = "imageUrl5") val imageUrl5: String
)

AppDatabase #

@Database(entities = [Entertainment::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun entertainmentDao(): EntertainmentDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "funiverse.sqlite"
                )
                    .createFromAsset("funiverse.sqlite")
                    .addCallback(object : RoomDatabase.Callback() {
                        override fun onCreate(db: SupportSQLiteDatabase) {
                            super.onCreate(db)
                            Log.d("Database", "Database created")
                        }
                        override fun onOpen(db: SupportSQLiteDatabase) {
                            super.onOpen(db)
                            Log.d("Database", "Database opened")
                        }
                    })
                    .build()
                INSTANCE = instance
                instance
            }
        }
    }
}

Dao #

@Dao
interface EntertainmentDao {
    @Query("SELECT * FROM entertainment")
    fun getAllEntertainment(): List<Entertainment>

    @Query("SELECT * FROM entertainment WHERE categoryId = :categoryId")
    fun getEntertainmentByCategoryId(categoryId: Int): List<Entertainment>
}

Activity（日志tag输出结果为0） #

class EntertainmentActivity : AppCompatActivity() {
    private lateinit var binding: ActivityEntertainmentBinding
    private lateinit var db: AppDatabase
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)

        binding = ActivityEntertainmentBinding.inflate(layoutInflater)
        setContentView(binding.root)

        db = AppDatabase.getInstance(this)
        lifecycleScope.launch {
            val entertainmentList = withContext(Dispatchers.IO) {
                db.entertainmentDao().getAllEntertainment()
            }

            updateUIWithEntertainmentList(entertainmentList)
            Log.e("tag",entertainmentList.size.toString())
        }
    }
    private fun updateUIWithEntertainmentList(entertainmentList: List<Entertainment>) {
        val adapter = EntertainmentAdapter(this, entertainmentList, object : ItemClickListener {
            override fun onImageClick(position: Int, image: String, text: String) {
                val bottomSheetFragment = BottomSheetFragment()
                val bundle = Bundle().apply {
                    putString("image_resource", image)
                    putString("text", text)
                }
                bottomSheetFragment.arguments = bundle
                bottomSheetFragment.show(supportFragmentManager, bottomSheetFragment.tag)
            }
        })

        binding.recyclerEasy.adapter = adapter
        binding.recyclerHard.adapter = adapter
        binding.recyclerMedium.adapter = adapter
    }
}

解决方案 #

1. 修复字段可空性 #

数据类中所有字段均为非空类型，但如果asset数据库内存在NULL值字段，Room无法将NULL映射到非空类型，会直接过滤该条数据。将允许为空的字段改为可空类型：

@Entity(tableName = "entertainment")
data class Entertainment(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") val id: Int?,
    @ColumnInfo(name = "categoryId") val categoryId : Int?,
    @ColumnInfo(name = "difficulty") val difficulty: Int?,
    @ColumnInfo(name = "title1") val title1: String?,
    @ColumnInfo(name = "title2") val title2: String?,
    @ColumnInfo(name = "title3") val title3: String?,
    @ColumnInfo(name = "title4") val title4: String?,
    @ColumnInfo(name = "title5") val title5: String?,
    @ColumnInfo(name = "imageUrl1") val imageUrl1: String?,
    @ColumnInfo(name = "imageUrl2") val imageUrl2: String?,
    @ColumnInfo(name = "imageUrl3") val imageUrl3: String?,
    @ColumnInfo(name = "imageUrl4") val imageUrl4: String?,
    @ColumnInfo(name = "imageUrl5") val imageUrl5: String?
)

2. 验证Schema一致性 #

Room会严格校验数据库结构与实体类定义是否匹配：

• 在build.gradle中添加配置导出Room的Schema文件，与asset数据库结构对比：

  android {
      defaultConfig {
          javaCompileOptions {
              annotationProcessorOptions {
                  arguments += ["room.schemaLocation": "$projectDir/schemas".toString()]
              }
          }
      }
  }
  

• 使用SQLite工具打开asset中的funiverse.sqlite，确认字段类型、名称（含大小写）与实体类完全一致。

3. 处理数据库缓存 #

若之前安装过应用，Room会保留本地数据库文件，修改asset数据库后不会自动覆盖：

• 卸载应用后重新安装；
• 开发阶段可添加fallbackToDestructiveMigration()强制重建数据库：

  val instance = Room.databaseBuilder(
      context.applicationContext,
      AppDatabase::class.java,
      "funiverse.sqlite"
  )
      .createFromAsset("funiverse.sqlite")
      .fallbackToDestructiveMigration()
      .addCallback(...)
      .build()
  

4. 直接验证数据库数据 #

在onOpen回调中执行原生SQL查询，确认数据库内是否存在数据：

override fun onOpen(db: SupportSQLiteDatabase) {
    super.onOpen(db)
    Log.d("Database", "Database opened")
    val cursor = db.query("SELECT COUNT(*) FROM entertainment")
    if (cursor.moveToFirst()) {
        val count = cursor.getInt(0)
        Log.d("Database", "Total rows: $count")
    }
    cursor.close()
}

若输出count为0，说明asset数据库未被正确复制；若为2，说明实体类映射存在问题。

内容的提问来源于stack exchange，提问作者Murad Axundov