沒有關系的房間中的一對多關系-有解無憂

這是我想要實作的目標：

一個User物體（應用可以有多個用戶）

每個用戶有多個University物件
每所大學都有多個Semester物件
每個學期都有一個Course物件串列

一個事件物體（一個用戶可以有多個事件）

我希望能夠：

插入用戶
用戶插入大學
用戶插入學期、課程等
用戶可以根據需要訪問上述所有內容的串列（僅限他自己的資料，而不是其他用戶的）

我如何實作上述目標？我從具有@Embedded欄位的 User 物件開始，該物件University又具有一個@Embedded欄位，Semester但是如何將用戶資料彼此分開？所有這些類都需要一個單獨的 Dao 還是一個 UserDao 可以處理所有事情（因為它們都是嵌套的并且使用@Embedded?

uj5u.com熱心網友回復：

如果您的意思是@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 添加牛津大學 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

I would suggest, if happy with the above, considering defining foreign key constraints to enforce referential integrity but have omitted them for brevity and to reduce the complexity of understanding.

So with a pretty typical @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
        }
    }
}

an exception to being typical is that for brevity .allowMainThreadQueries has been utilised.

Finally putting it all together in a demonstration (that produced the output above) 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}")
        }
    }
}

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

標籤：爪哇安卓 sqlite android-room

上一篇：使用PHP更新SQLite資料庫

下一篇：SQLite.Swift-查詢現有的Db表