房间数据库一对多关系DAO错误

Question

我正在制作一个笔记应用程序，每个笔记都有一个标签，每个标签都分配了许多笔记。因此，这是数据库中的一对多关系
我按照本教程创建了实体类。但当我构建项目时，它在DAO接口中给了我一个错误。错误为

任务"：app:kaptDebugKotlin"的执行失败。

但在构建输出中，它表示getAllNotes()函数有一个问题：

NoteDao.java:17:错误：查询返回的列在com.hh.kotemessenger.data.LabelWithNotes中没有字段[labelId，labelName]，即使它们被注释为非null或基元。查询返回的列：[noteId，noteText，dateCreated，isArchived，noteLabelId]

注意类：

@Entity(
tableName = "note_table",
foreignKeys = [ForeignKey(
entity = Label::class,
parentColumns = ["labelId"],
childColumns = ["noteLabelId"]
)]
)
@Parcelize
data class Note(
@PrimaryKey(autoGenerate = true) val noteId: Int = 0,
val noteText: String,
val dateCreated: Long = System.currentTimeMillis(),
val isArchived: Boolean = false,
val noteLabelId: Int
) : Parcelable {
val createdDateFormatted: String
get() = DateFormat.getDateTimeInstance().format(dateCreated)
}  

标签类别：

@Entity(tableName = "label_table")
@Parcelize
data class Label(
@PrimaryKey(autoGenerate = true)
val labelId: Int = 0,
val labelName: String,
val dateCreated: Long = System.currentTimeMillis()
) : Parcelable  

LabelWithNotes类别：

data class LabelWithNotes(
@Embedded val label: Label,
@Relation(parentColumn = "labelId", entityColumn = "noteLabelId")
val note: List<Note>
)  

NoteDao接口：

@Dao
interface NoteDao {
fun getNotes(searchQuery: String, labelId: Int): Flow<List<LabelWithNotes>> =
when (labelId) {
1 -> getAllNotes(searchQuery)
else -> getNotesByLabel(searchQuery, labelId)
}
@Transaction
@Query("SELECT * FROM note_table WHERE isArchived = 0 AND noteText LIKE '%' || :searchQuery || '%' ")
fun getAllNotes(searchQuery: String): Flow<List<LabelWithNotes>>
@Transaction
@Query("SELECT * FROM note_table WHERE isArchived = 0 AND noteText LIKE '%' || :searchQuery || '%' AND noteLabelId =:labelId")
fun getNotesByLabel(searchQuery: String, labelId: Int): Flow<List<LabelWithNotes>>

Answer 1

问题问题是您试图从note_table中检索Labels。

The columns returned by the query does not have the fields [labelId,labelName] in com.hh.knotemessenger.data.LabelWithNotes even though they are annotated as non-null or primitive. 
Columns returned by the query: [noteId,noteText,dateCreated,isArchived,noteLabelId]

这意味着所需的列labelId和labelName是必需的，但不可用，并且noteId。。。。列可用

简而言之，要找到每个标签的Notes，您需要遍历label_table，而不是note_table

分辨率(有点(如果您使用：-

@Transaction
@Query("SELECT * FROM label_table")
fun getAllNotes(searchQuery: String): Flow<List<LabelWithNotes>>
@Transaction
@Query("SELECT * FROM label_table")
fun getNotesByLabel(searchQuery: String, labelId: Int): Flow<List<LabelWithNotes>>

那么我相信你不会犯错误的。

但是添加引用note_table中列的WHERE子句将导致编译失败，因为不包括note_table。

您可以包含一个JOIN以允许包含note_table列，如：-

@Transaction
@Query("SELECT * FROM label_table JOIN note_table ON labelId = noteLabelId  WHERE isArchived = 0 AND noteText LIKE '%' || :searchQuery || '%' ")
fun getAllNotes(searchQuery: String): Flow<List<LabelWithNotes>>
@Transaction
@Query("SELECT * FROM label_table JOIN note_table ON labelId = noteLabelId WHERE isArchived = 0 AND noteText LIKE '%' || :searchQuery || '%' AND noteLabelId =:labelId")
fun getNotesByLabel(searchQuery: String, labelId: Int): Flow<List<LabelWithNotes>>

但是这不会产生所需的结果。因为room处理@Relation的方式是，它为提取的每个标签检索ALL注释，而不考虑明显限制注释的WHERE子句。

按照

一个方便的注释，可以在POJO中用于自动获取关系实体。从查询返回POJO时，其所有关系也将由Room提取。

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

您还会收到以下警告：-

E:AndroidStudioAppsSO67804070KotlinRoomappbuildtmpkapt3stubsdebugaaso67804070kotlinroomNoteDao.java:27: warning: The query returns some columns [noteId, noteText, isArchived, noteLabelId] which are not used by a.a.so67804070kotlinroom.LabelWithNotes. You can use @ColumnInfo annotation on the fields to specify the mapping. You can annotate the method with @RewriteQueriesToDropUnusedColumns to direct Room to rewrite your query to avoid fetching unused columns.  You can suppress this warning by annotating the method with @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH). Columns returned by the query: labelId, labelName, dateCreated, noteId, noteText, dateCreated, isArchived, noteLabelId. Fields in a.a.so67804070kotlinroom.LabelWithNotes: labelId, labelName, dateCreated.

您还可能最终检索到带有注释的重复标签(相同的注释(。

我相信你可能想要的是带标签的Notes，而不是带标签的LabelsWithNotes。