StackOverflow 中有很多這樣的問題,但大部分都不是關於房間數據庫的,所以我不得不問一個新問題。
我有一個使用房間數據庫的應用程序,它有近 4 個表,並且這些表之間有很大的關係,例如,當我刪除用戶列表片段中的用戶時,該用戶刪除(僅 userName 和一些個人信息)但用戶的 TRANSACTIONS並且 LOANS 沒有被刪除。有人告訴我我必須使用級聯刪除,但我沒有找到太多關於它的信息。
我的用戶類模型:
@Entity(tableName = "user_info")
data class UserInfo(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "user_id")
var userId: Long =0L,
@ColumnInfo(name = "full_name")
var fullName:String?,
@ColumnInfo(name= "account_id")
var accountId: String?,
@ColumnInfo(name = "mobile_number")
var mobileNumber:String?,
@ColumnInfo(name = "phone_number")
var phoneNumber:String?,
@ColumnInfo(name = "date_of_creation")
var dateOfCreation:String?,
@ColumnInfo(name = "address")
var address:String?,
)
交易模型類:
@Entity(tableName = "transactions")
data class Transactions(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "trans_id")
var transId: Long = 0L,
@ColumnInfo(name = "user_id")
var userId: Long?,
@ColumnInfo(name = "create_date")
var createDate: String?,
@ColumnInfo(name = "bank_id")
var bankId: Long?,
@ColumnInfo(name = "description")
var description: String?,
@ColumnInfo(name = "increase")
var increase: String?,
@ColumnInfo(name = "decrease")
var decrease: String?,
@ColumnInfo(name = "loan_number")
var loanNumber: String?,
@ColumnInfo(name = "total")
var total: Long?,
@ColumnInfo(name = "type")
var type: String?
)
用戶DAO:
@Insert
suspend fun insert(ui: UserInfo): Long
@Update
suspend fun update(ui: UserInfo)
@Insert
suspend fun insertList(ui: MutableList<UserInfo>)
@Delete
suspend fun deleteUser(ui: UserInfo)
@Query("DELETE FROM user_info")
fun deleteAllUser()
@Query("SELECT user_info.user_id, user_info.full_name, transactions.total From user_info JOIN transactions ")
fun joinTable(): LiveData<List<UserAndMoney>>?
@Query("SELECT * from user_info WHERE user_id = :key")
fun get(key: Long): LiveData<UserInfo>?
@Query("SELECT * FROM user_info ORDER BY full_name DESC")
fun getAllUserInfo(): LiveData<List<UserInfo>>
@Query("SELECT * FROM user_info where full_name like '%' || :fullName || '%' ORDER BY full_name ASC")
fun searchUserName(fullName: String): LiveData<List<UserInfo>>
如果到目前為止您還不清楚,讓我為您簡單介紹:
我需要級聯刪除來刪除有關用戶和記錄的所有內容。
CASCADE 是外鍵約束的一個選項。所以你需要定義外鍵約束。您可以通過 @Entity 註釋在 Room 中定義外鍵約束。
例如,由於交易似乎通過var userId: Long?,
(列名 user_id)與 UserInfo 相關,因此您可以:-
@Entity(tableName = "transactions",
foreignKeys = [
ForeignKey(
entity = UserInfo::class,
parentColumns = ["user_id"],
childColumns = ["user_id"],
onDelete = ForeignKey.CASCADE, //<<<<<
onUpdate = ForeignKey.CASCADE // Optional
)
]
)
data class Transactions(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "trans_id")
var transId: Long = 0L,
@ColumnInfo(name = "user_id", index = true) // <<<<< best to have an index on the column, not required
var userId: Long?,
@ColumnInfo(name = "create_date")
var createDate: String?,
@ColumnInfo(name = "bank_id")
var bankId: Long?,
@ColumnInfo(name = "description")
var description: String?,
@ColumnInfo(name = "increase")
var increase: String?,
@ColumnInfo(name = "decrease")
var decrease: String?,
@ColumnInfo(name = "loan_number")
var loanNumber: String?,
@ColumnInfo(name = "total")
var total: Long?,
@ColumnInfo(name = "type")
var type: String?
)
筆記
該約束強制執行參照完整性,即如果 user_id 值不是 user_info 表的 user_id 列中存在的值,則無法插入/更新事務。
CASCADE onUpdate 將對 user_info 表中的 user_id 值的更改級聯到相應的事務。
額外的
有人告訴我我必須使用級聯刪除,但我沒有找到太多關於它的信息。
你被告知的是不正確的。您可以在沒有ON DELETE CASCADE
或沒有外鍵約束的情況下複製功能。
你可以用
@Query("DELETE FROM transaction WHERE user_id=:userId")
fun cascadeDeletionsFromUser(userId: Long)
cascadeDeletionsFromUser
必須在刪除 user_info 行之前運行該函數。否則無法刪除 user_info 行,因為 FK 約束會禁止刪除。如果你有一個抽像類而不是接口,那麼你可以有:-
@Query("DELETE FROM user_info WHERE user_id=:userId")
abstract fun deleteUserById(userId: Long)
@Query("DELETE FROM transactions WHERE user_id=:userId")
abstract fun cascadeDeletionsFromUser(userId: Long)
@Transaction
@Query("")
fun deleteUserWithCascade(userId: Long) {
cascadeDeletionsFromUser(userId)
deleteUserById(userId)
}
並使用deleteUserWithCascade函數一次性刪除交易和用戶。
使用起來更方便ON DELETE CASCADE
,尤其是如果您有多個深度的關係(當確定孩子變得更複雜時)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句