構建資料庫表和@Relation以從資料庫物體創建問卷？（科特林，房間）-有解無憂

在潛入一個新專案之前，我正在尋求一些指導。

我的目標是在應用程式中生成一個表單/問卷，該應用程式根據問題的Group及其Category提取問題資料。其中每個類別包含許多組，每個組包含許多問題。

為了支持 UI 和嵌套的回收器視圖，目的是為視圖模型提供一個包含嵌套串列的物件。即具有類別串列的物件，包含組串列，包含問題串列。

在設定房間、物體和 DAO 以及它們之間的關系方面，我的理解是，實作這一目標的最佳方法是：

創建一個 Questions 物體（包含文本、選項等）
為問題和組之間的關系創建一個參考表（多對多）
為 Groups 和 Categories 之間的關系創建一個父/子表（一對多）

在此之后，應該使用一組基于關系的資料類來組合每一對。

GroupWithQuestions 資料類（使用@Relation 使用參考表列出每組中的問題）
CategoryWithGroupsWithQuestions 資料類（使用@Relation 使用父/子表列出每個類別中的組）
QuestionaireWithCategoriesWith...Questions 資料類（包含 CategoryWithGroupsWithQuestions 串列）

這很復雜，需要通過多個表來跟蹤關系，因此很難更新并且解決錯誤也很耗時。我覺得我在考慮這個方法（或者我錯過了一些東西）。

有沒有更簡單/更聰明的方法？

（單物件方法是問題的一部分嗎？）

在此先感謝您的建議和意見。

uj5u.com熱心網友回復：

創建問題物體（包含文本、選項等）為問題和組之間的關系（多對多）創建一個參考表為組和類別之間的關系（一對多）創建一個父/子表許多）

一對多只需要在子級中為父級添加一列。

這很復雜，需要通過多個表來跟蹤關系，因此很難更新并且解決錯誤也很耗時。我覺得我在考慮這個方法（或者我錯過了一些東西）。

這并不是那么復雜：我相信以下內容與您似乎要求的非常接近：-

物體

類別：-

@Entity(
    indices = [
        Index(value = ["categoryName"],unique = true) /* Assume that a category name should be unique */
    ]
)
data class Category(
    @PrimaryKey
    val categoryId: Long? = null,
    @ColumnInfo
    val categoryName: String
)

組:-

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Category::class,
            parentColumns = ["categoryId"],
            childColumns = ["categoryIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)

data class Group(
    @PrimaryKey
    val groupId: Long? = null,
    @ColumnInfo(index = true)
    val categoryIdMap: Long,
    val groupName: String
)

外鍵約束不是必需的，但它們有助于強制執行參照完整性。
- onDelete 和 onUpdate 不是必需的，但可能會有所幫助

題

@Entity(
)
data class Question(
    @PrimaryKey
    val questionId: Long? = null,
    @ColumnInfo(index = true)
    val questionText: String,
    val questionOption: Int
)

QuestiongroupMap（可以是GroupQuestionMap）：-

@Entity(
    primaryKeys = ["questionIdMap","groupIdMap"],
    foreignKeys = [
        ForeignKey(
            entity = Question::class,
            parentColumns = ["questionId"],
            childColumns = ["questionIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE),
        ForeignKey(
            entity = Group::class,
            parentColumns = ["groupId"],
            childColumns = ["groupIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class QuestionGroupMap(
    val questionIdMap: Long,
    @ColumnInfo(index = true)
    val groupIdMap: Long
)

POJO的

分組提問

data class GroupWithQuestions(
    @Embedded
    val group: Group,
    @Relation(
        entity = Question::class,
        entityColumn = "questionId",
        parentColumn = "groupId",
        associateBy = Junction(
            QuestionGroupMap::class,
            parentColumn = "groupIdMap",
            entityColumn = "questionIdMap"
        )
    )
    val questionList: List<Question>
)

通過 QuestiongroupMap 以及關聯和 Junction

CategoryWithGroupsWithQuestions

data class CategoryWithGroupWithQuestions(
    @Embedded
    val category: Category,
    @Relation(entity = Group::class,entityColumn = "categoryIdMap",parentColumn = "categoryId")
    val groupWithQuestionsList: List<GroupWithQuestions>
)

NOTE even though you are getting a List of GroupWithQuestions, it is the Group entity that is specified.

Some extras that may be of use :-

data class CategoryWithGroup(
    @Embedded
    val category: Category,
    @Relation(entity = Group::class,entityColumn = "categoryIdMap",parentColumn = "categoryId")
    val group: Group
)

data class GroupWithCategory(
    @Embedded
    val group: Group,
    @Relation(entity = Category::class,entityColumn = "categoryId",parentColumn = "categoryIdMap")
    val category: Category
)

Dao's

AllDao (i.e. all in one place for brevity/convenience) :-

@Dao
abstract class AllDao {

    @Insert
    abstract fun insert(category: Category): Long
    @Insert
    abstract fun insert(group: Group): Long
    @Insert
    abstract fun insert(question: Question): Long
    @Insert
    abstract fun insert(questionGroupMap: QuestionGroupMap): Long
    @Transaction
    @Query("SELECT * FROM `group`")
    abstract fun getAllGroupsWithCategory(): List<GroupWithCategory>
    @Transaction
    @Query("SELECT * FROM category")
    abstract fun getAllCategoriesWithGroups(): List<CategoryWithGroup>
    @Transaction
    @Query("SELECT * FROM `group`")
    abstract fun getAllGroupsWithQuestions(): List<GroupWithQuestions>

    @Transaction
    @Query("SELECT * FROM category")
    abstract fun getAllCategoriesWithGroupsWithQuestions(): List<CategoryWithGroupWithQuestions>

}

an @Database class TheDatabase :-

@Database(entities = [Category::class,Group::class,Question::class,QuestionGroupMap::class],exportSchema = false,version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        @Volatile
        private var instance: TheDatabase? = null

        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    TheDatabase::class.java,
                    "thedatabase.db"
                )
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

allowMainThreadQueries for brevity/convenience

Finally putting the above into action in an activity, result in a list of CategoreiesWithgroupsWithQuestions being extracted and output to the log :-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val TAG = "DBINFO"

        val cat1 = dao.insert(Category(categoryName = "Cat1"))
        val cat2 = dao.insert(Category(categoryName = "Cat2"))
        val cat3 = dao.insert(Category(categoryName = "Cat3"))

        val grp1 = dao.insert(Group(groupName = "Grp1",categoryIdMap = cat1))
        val grp11 = dao.insert(Group(groupName = "Grp11",categoryIdMap = cat1))
        val grp111 = dao.insert(Group(groupName = "Grp111",categoryIdMap = cat1))
        val grp1111 = dao.insert(Group(groupName = "Grp1111",categoryIdMap = cat1))
        val grp2 = dao.insert(Group(groupName = "Grp2",categoryIdMap = cat2))
        val grp22 = dao.insert(Group(groupName = "Grp22",categoryIdMap = cat2))
        val grp3 = dao.insert(Group(groupName = "Grp3",categoryIdMap = cat3))

        val q1 = dao.insert(Question(questionText = "Q1 ....", questionOption = 11110000))
        val q2 = dao.insert(Question(questionText = "Q2....", questionOption = 11010101))
        val q3 = dao.insert(Question(questionText = "Q3....", questionOption = 10000001))
        val q4 = dao.insert(Question(questionText = "Q4....",questionOption = 11000001))
        val q5 = dao.insert(Question(questionText = "Q5....",questionOption = 11100011))

        dao.insert(QuestionGroupMap(q1,grp1))
        dao.insert(QuestionGroupMap(q1,grp2))
        dao.insert(QuestionGroupMap(q1,grp3))
        dao.insert(QuestionGroupMap(q2,grp2))
        dao.insert(QuestionGroupMap(q2,grp22))
        dao.insert(QuestionGroupMap(q3,grp3))
        dao.insert(QuestionGroupMap(q4,grp11))
        dao.insert(QuestionGroupMap(q4,grp111))
        dao.insert(QuestionGroupMap(q4,grp1111))
        dao.insert(QuestionGroupMap(q5,grp22))

        /* extract the data via the geAllCategoriesWithGroupsWithQuestions query*/
        for (cwgwq: CategoryWithGroupWithQuestions in dao.getAllCategoriesWithGroupsWithQuestions()) {
            Log.d(TAG,"Category is ${cwgwq.category.categoryName} ID is ${cwgwq.category.categoryId}, it has ${cwgwq.groupWithQuestionsList.size} groups, which are:-")
            for(gwq: GroupWithQuestions in cwgwq.groupWithQuestionsList) {
                Log.d(TAG,"\tGroup is ${gwq.group.groupName} ID is ${gwq.group.groupId}, it has ${gwq.questionList.size} questions, which are:-")
                for(q: Question in gwq.questionList) {
                    Log.d(TAG,"\t\tQuestion is ${q.questionText} options are ${q.questionOption} ID is ${q.questionId}")
                }
            }
        }

    }
}

result :-

D/DBINFO: Category is Cat1 ID is 1, it has 4 groups, which are:-
D/DBINFO:   Group is Grp1 ID is 1, it has 1 questions, which are:-
D/DBINFO:       Question is Q1 .... options are 11110000 ID is 1
D/DBINFO:   Group is Grp11 ID is 2, it has 1 questions, which are:-
D/DBINFO:       Question is Q4.... options are 11000001 ID is 4
D/DBINFO:   Group is Grp111 ID is 3, it has 1 questions, which are:-
D/DBINFO:       Question is Q4.... options are 11000001 ID is 4
D/DBINFO:   Group is Grp1111 ID is 4, it has 1 questions, which are:-
D/DBINFO:       Question is Q4.... options are 11000001 ID is 4
D/DBINFO: Category is Cat2 ID is 2, it has 2 groups, which are:-
D/DBINFO:   Group is Grp2 ID is 5, it has 2 questions, which are:-
D/DBINFO:       Question is Q1 .... options are 11110000 ID is 1
D/DBINFO:       Question is Q2.... options are 11010101 ID is 2
D/DBINFO:   Group is Grp22 ID is 6, it has 2 questions, which are:-
D/DBINFO:       Question is Q2.... options are 11010101 ID is 2
D/DBINFO:       Question is Q5.... options are 11100011 ID is 5
D/DBINFO: Category is Cat3 ID is 3, it has 1 groups, which are:-
D/DBINFO:   Group is Grp3 ID is 7, it has 2 questions, which are:-
D/DBINFO:       Question is Q1 .... options are 11110000 ID is 1
D/DBINFO:       Question is Q3.... options are 10000001 ID is 3

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/312845.html

標籤：科特林 android-room

上一篇：確定Flow何時不回傳任何資料

下一篇：Kotlin：如果未找到匹配項，過濾串列將回傳原始串列