代码之家 › 专栏 › 技术社区 › Asim

房间里的一对多关系没有关系

android-room sqlite android java

Asim · 技术社区 · 3 年前

以下是我努力实现的目标:

A. User 实体(应用程序可以有多个用户)

每个用户都有多个 University 对象
每所大学都有多所大学 Semester 对象
每学期都有一份 Course 对象

事件实体(一个用户可以有多个事件)

我希望能够:

插入用户
用户插入一所大学
用户插入学期、课程等
用户可以在需要时访问上述所有内容的列表 (只有他自己的数据,没有其他用户的数据)

我如何实现上述目标?我从用户对象开始,它有一个 @Embedded 字段 大学 ,这反过来又有一个 @嵌入 领域 学期 但如何将用户数据彼此分离?所有这些类都需要一个单独的Dao吗?或者一个UserDao可以处理所有的事情吗(因为它们都是嵌套的,并且使用 @嵌入 ?

0 回复 | 直到 3 年前

MikeT 3 年前

如果你是说没有 @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的UserEvents是在Fred登录时提取的。

所以除了 用户事件 POJO有@EntityClass:-

使用者 :-

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

用户课程地图 注意到提供的关系是多方面的,但这可以促进1-many。

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

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

只有用户注册的课程,因此学期,因此大学是特定于用户的。

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

奥尔道 :-

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

}

如果你想拥有多个@dao,由你决定

如何将用户数据彼此分离?

见上图 用户课程地图 还有 getUserEvents 刀

如果对以上内容感到满意,我建议考虑定义外键约束,以加强引用完整性,但为了简洁和降低理解的复杂性,我省略了它们。

所以用一个非常典型的@数据库 数据库 :-

@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 已被利用。

最后,在一个演示中(产生了上面的输出)将其全部放在一起 主要活动 :-

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