时间戳

首页 > 解决方案 > 没有关系的房间中的一对多关系

java - 没有关系的房间中的一对多关系

问题描述

这是我想要实现的目标：

一个User实体（应用可以有多个用户）

一个事件实体（一个用户可以有多个事件）

我希望能够：

我如何实现上述目标？我从具有@Embedded字段的 User 对象开始，该字段University又具有@Embedded字段Semester，但是如何将 User 数据彼此分开？所有这些类都需要一个单独的 Dao 还是一个 UserDao 可以为所有事情工作（因为它们都是嵌套的并使用@Embedded?

标签： javaandroidsqliteandroid-room

解决方案

如果您的意思是@Relation没有关系而不是没有关系，那么也许考虑以下允许：-

data class UserEvents(
    @Embedded
    val user: User,
    @Embedded
    val university: University,
    @Embedded
    val semester: Semester,
    @Embedded
    val course: Course
)

并且从下面的演示/示例中将产生以下内容：-

2021-11-06 13:25:28.960 D/EVENTS_1: Event is English part1: Semester is Semester 1 2022, from 2022-01-17 to 2022-03-10:Univeristy is Cambridge:User is Jane
2021-11-06 13:25:28.960 D/EVENTS_1: Event is English part2: Semester is Semester 2 2022, from 2022-03-24 to 2022-06-14:Univeristy is Cambridge:User is Jane
2021-11-06 13:25:28.960 D/EVENTS_1: Event is English part 1: Semester is Semester 1, from 2022-01-15 to 2022-03-31:Univeristy is Oxford:User is Jane

2021-11-06 13:25:28.965 D/EVENTS_1: Event is Mathematcis part2: Semester is Semester 3 2022, from 2022-06-24 to 2022-09-15:Univeristy is Cambridge:User is Fred
2021-11-06 13:25:28.965 D/EVENTS_1: Event is Mathematcis part2: Semester is Semester 4 2022, from 2022-10-03 to 2022-12-15:Univeristy is Cambridge:User is Fred
  • 注意到 Jane 的 UserEvents 是在 Jane 登录时提取的，而 Fred 的则是在 Fred 登录时提取的。

因此，除了UserEvents POJO 之外，还有 @Entityclasses ：-

用户：-

@Entity
data class User (
    @PrimaryKey
    val userId: Long? = null,
    val userName: String,
    val userPassword: String,
    val userOtherData: String
    )

大学

@Entity(
    indices = [
        Index(value = ["universityName"], unique = true)
    ]
)
data class University(
    @PrimaryKey
    val universityId: Long?=null,
    val universityName: String,
    val universityOtherData: String
)

学期

@Entity
data class Semester(
    @PrimaryKey
    val semesterId: Long?=null,
    val semesterName: String,
    val semesterStartDate: String,
    val semesterEndDate: String,
    val semesterUniversityMap: Long
)

课程

@Entity
data class Course(
    @PrimaryKey
    val courseId: Long?=null,
    val courseName: String,
    val courseSemesterMap: Long
)

UserCourseMap指出提供的关系是多对多，但可以促进一对多。

@Entity(
    primaryKeys = ["userCourseMapUserId","userCourseMapCourseId"],
    indices = [
        Index(value = ["userCourseMapCourseId"]
        )
    ])
data class UserCourseMap(
    val userCourseMapUserId: Long,
    val userCourseMapCourseId: Long
)

上述设计并未将大学、学期或课程指定给特定用户。它们是共享的，例如，用户 1 添加 Oxford uni，然后用户 2 尝试它不重复但已经存在，依此类推。

只有用户在课程中的注册，因此学期和大学是特定于用户的。

所有这些类都需要一个单独的 Dao 还是一个 UserDao

AllDao :-

@Dao
abstract class AllDao {

    @Insert(onConflict = IGNORE)
    abstract fun insert(user: User): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(university: University): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(semester: Semester): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(course: Course): Long
    @Insert(onConflict = IGNORE)
    abstract fun insert(userCourseMap: UserCourseMap): Long

    @Query("SELECT universityId FROM University WHERE universityName=:universityName")
    abstract fun getUniversityIdByName(universityName: String): Long
    @Query("SELECT semesterId FROM semester WHERE semesterName=:semesterName AND semesterUniversityMap=:universityId")
    abstract fun getSemesterByNameAndUniversityId(semesterName: String, universityId: Long): Long
    @Query("SELECT courseId FROM course WHERE courseName=:courseName AND courseSemesterMap=:semesterId")
    abstract fun getCourseByCourseNameAndSemesterId(courseName: String, semesterId: Long): Long

    @Query("SELECT coalesce(max(userid),-1) FROM user WHERE userName=:userName AND userPassword =:userPassword")
    abstract fun userLogin(userName: String, userPassword: String): Long

    @Query("SELECT * FROM usercoursemap " +
            "JOIN User on userCourseMapUserId = userId " +
            "JOIN course on userCourseMapCourseId = courseId " +
            "JOIN semester ON courseSemesterMap = semesterId " +
            "JOIN university ON semesterUniversityMap = universityId " +
            "WHERE userId=:userId")
    abstract fun getUserEvents(userId: Long): List<UserEvents>

}
  • 如果您想拥有多个@Daos，取决于您

如何将用户数据彼此分开？

参见上面关于 UserCourseMap和getUserEvents dao

  • 我建议，如果对上述内容感到满意，请考虑定义外键约束以强制引用完整性，但为简洁起见并降低理解的复杂性而省略了它们。

因此，使用非常典型的@Database TheDatabase：-

@Database(entities = [
    User::class,University::class,Semester::class,Course::class,UserCourseMap::class,
    version = 1)
@TypeConverters(DateTimeConverter::class)
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。

最后把它们放在一个演示中（产生上面的输出）MainActivity： -

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()
        var currentUserId: Long = -1 /* no user not logged in */

        /* Add a couple of users */
        dao.insert(User(userName = "Fred",userPassword = "passwordforfred", userOtherData = "blah"))
        dao.insert(User(userName = "Jane", userPassword = "passwordforjane", userOtherData = "blah"))

        /* add some universities, semesters and courses all 3 are globally accessible */
        val yaleid = dao.insert(University(universityName = "Yale", universityOtherData = "blah"))
        val cambridgeid = dao.insert(University(universityName = "Cambridge", universityOtherData = "blah"))
        val semester1yale = dao.insert(Semester(semesterName = "Semester 1 2022", semesterStartDate = "2022-01-23", semesterEndDate = "2022-04-07", semesterUniversityMap = yaleid))
        val semester2yale = dao.insert(Semester(semesterName = "Semester 2 2022", semesterStartDate = "2022-04-17", semesterEndDate = "2022-07-01", semesterUniversityMap = yaleid))
        val semester3yale = dao.insert(Semester(semesterName = "Semester 3 2022", semesterStartDate = "2022-07-28", semesterEndDate = "2022-10-01", semesterUniversityMap = yaleid))
        val semester4yale = dao.insert(Semester(semesterName = "Semester 4 2022", semesterStartDate = "2022-10-25", semesterEndDate = "2022-12-18", semesterUniversityMap = yaleid))
        val semester1camb = dao.insert(Semester(semesterName = "Semester 1 2022", semesterStartDate = "2022-01-17", semesterEndDate = "2022-03-10", semesterUniversityMap = cambridgeid))
        val semester2camb = dao.insert(Semester(semesterName = "Semester 2 2022", semesterStartDate = "2022-03-24", semesterEndDate = "2022-06-14", semesterUniversityMap = cambridgeid))
        val semester3camb = dao.insert(Semester(semesterName = "Semester 3 2022", semesterStartDate = "2022-06-24", semesterEndDate = "2022-09-15", semesterUniversityMap = cambridgeid))
        val semester4camb = dao.insert(Semester(semesterName = "Semester 4 2022", semesterStartDate = "2022-10-03", semesterEndDate = "2022-12-15", semesterUniversityMap = cambridgeid))

        val coursecambengp1 = dao.insert(Course(courseName = "English part1",courseSemesterMap = semester1camb))
        val coursecambengp2 = dao.insert(Course(courseName = "English part2",courseSemesterMap = semester2camb))
        val coursecambengp3 = dao.insert(Course(courseName = "English part2",courseSemesterMap = semester3camb))
        val coursecambengp4 = dao.insert(Course(courseName = "English part2",courseSemesterMap = semester4camb))

        val coursecambmthp1 = dao.insert(Course(courseName = "Mathematics part1",courseSemesterMap = semester1camb))
        val coursecambmthp2 = dao.insert(Course(courseName = "Mathematics part2",courseSemesterMap = semester2camb))
        val coursecambmthp3 = dao.insert(Course(courseName = "Mathematcis part2",courseSemesterMap = semester3camb))
        val coursecambmthp4 = dao.insert(Course(courseName = "Mathematcis part2",courseSemesterMap = semester4camb))

        /* Logon in eventually to Jane, after 2 failed login attempts */
        currentUserId = dao.userLogin("Not a known user","obviously not a valid password")
        if (currentUserId < 1) {
            /* try again */
            currentUserId = dao.userLogin("Fred","wrongpassword")
            if (currentUserId < 1) {
                currentUserId = dao.userLogin("Jane","passwordforjane")
            }
        }
        if (currentUserId > 0) {

            /* all in one add of English part 1 - semster 1 at Oxford (i.e. bar the user all are added in one go) */
            dao.insert(
                UserCourseMap(
                    userCourseMapUserId = currentUserId,
                    userCourseMapCourseId =
                    dao.insert(
                        Course(
                            courseName = "English part 1",
                            courseSemesterMap = dao.insert(
                                Semester(
                                    semesterName = "Semester 1",
                                    semesterStartDate =  "2022-01-15",
                                    semesterEndDate = "2022-03-31",
                                    semesterUniversityMap = dao.insert(
                                        University(
                                            universityName = "Oxford",
                                            universityOtherData = "blah"
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )

            /* add event (mapping course to user and this implicitly adding semester and uni) to pre-existing */
            dao.insert(UserCourseMap(userCourseMapUserId = currentUserId,userCourseMapCourseId = coursecambengp1))
            dao.insert(UserCourseMap(userCourseMapCourseId = currentUserId,userCourseMapUserId = coursecambengp2))
        }

        /* get the events for Jane */
        for(ue: UserEvents in dao.getUserEvents(currentUserId)) {
            Log.d("EVENTS_1",
                "Event is ${ue.course.courseName}: " +
                        "Semester is ${ue.semester.semesterName}, from ${ue.semester.semesterStartDate} to ${ue.semester.semesterEndDate}:" +
                        "Univeristy is ${ue.university.universityName}:" +
                        "User is ${ue.user.userName}")
        }


        /* SWITCH TO USER FRED */
        currentUserId = dao.userLogin("Fred","passwordforfred")

        if (currentUserId > 0) {
            dao.insert(UserCourseMap(userCourseMapUserId = currentUserId,userCourseMapCourseId = coursecambmthp3))
            dao.insert(UserCourseMap(userCourseMapUserId = currentUserId,userCourseMapCourseId = coursecambmthp4))
        }

        /* Get the events for Fred */
        for(ue: UserEvents in dao.getUserEvents(currentUserId)) {
            Log.d("EVENTS_1",
                "Event is ${ue.course.courseName}: " +
                        "Semester is ${ue.semester.semesterName}, from ${ue.semester.semesterStartDate} to ${ue.semester.semesterEndDate}:" +
                        "Univeristy is ${ue.university.universityName}:" +
                        "User is ${ue.user.userName}")
        }
    }
}

推荐阅读