构建DB表和@Relation以从db实体创建问卷？（kotlin， Room）

Question

在开始一个新项目之前，我正在寻求一些指导。

我的目标是在应用程序中生成一个表单/问题，该表单/问题器根据问题的组及其类别提取问题数据。其中，每个类别包含许多组，每个组包含许多问题。

为了支持 UI 和嵌套回收器视图，目的是为视图模型提供包含嵌套列表的单个对象。即具有类别列表的对象，其中包含组列表，包含问题列表。

在建立空间、实体和DAO及其关系方面，我的理解是，实现这一目标的最佳方法是：

< li >创建问题实体(标题文本，选项..等等。) < li >为问题和组之间的关系创建一个参照表(多对多) < li >为组和类别之间的关系创建父/子表(一对多)

接下来，应该使用一组基于关系的数据类来匹配每一对。

• GroupSusyah数据类（使用@Relation使用引用表列出每个组中的问题）
• 类别有组有问题数据类（使用@Relation使用父/子表列出每个类别中的组）
• 问卷有类别有...问题数据类（包含类别有组有问题的列表）

这很复杂，需要通过多个表跟踪关系，因此很难更新，解决错误也很耗时。我觉得我对方法考虑得太多了(或者我错过了什么)。

有没有更简单/更智能的方法？

(单一对象方法是问题的一部分吗？)

提前感谢您的建议和意见。

Answer 1

创建问题实体（内容文本、选项。等）为问题和组之间的关系创建参考表（多对多） 为组和类别之间的关系创建父/子表（一对多）

一对多只需要在父对象的子对象中有一列。

这很复杂，需要通过多个表跟踪关系，因此很难更新，解决错误也很耗时。我觉得我对方法考虑得太多了(或者我错过了什么)。

这并不是那么复杂：我相信以下内容非常接近您似乎要求的内容：-

实体

类别：-

@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
)

• Foriegn键约束不是必需的，但它们有助于强制引用完整性。
  • onDelete和onUpdate不是必需的，但会很有帮助

  问题

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

  问题组地图（可以是Group问卷地图）：-

  @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>
  )
  

  • 通过问卷组映射，从而关联和连接

  类别与组与问题

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

  • 请注意，即使您得到的是GroupWithQuestions列表，但指定的是组实体

  一些可能有用的额外功能：-

  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
  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>
  
  }
  

  @Database类数据库：-

  @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
          }
      }
  }
  

  < li >为了简洁/方便起见，允许使用线程查询

  最后，在一个活动中将上述内容付诸实施，结果是一个categoreiswithgroupswithsquestions列表被提取出来并输出到日志中:-

  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}")
                  }
              }
          }
  
      }
  }
  

  结果:-

  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