子表中的更新查詢不會反映在父表AndroidRoomKotlin中-有解無憂

父表：

    @Entity(tableName = "Product")
    data class Products (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int = 0,

    @ColumnInfo(name = "name")
    var name  : String? = null,

    @ColumnInfo(name = "variants")
    var variants : MutableList<Variants> = mutableListOf()
)

子表：

    @Entity(tableName = "Variant")
    data class Variants (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int  = 0,

    @ColumnInfo(name = "product_id", index = true)
    var product_id : Int?  = null,

    @ColumnInfo(name = "measurement")
    var measurement : String?  = null,

    @ColumnInfo(name = "discounted_price")
    var discounted_price : String?  = null,

    @ColumnInfo(name = "cart_count")
    var cart_count : Int?  = null
)

我想更新變體中的購物車計數，它也應該反映在產品表中，也作為變體更新..這個查詢是什么？

當我使用此更新查詢時..我更新 Variant Table 中的值但是當我得到 getallProducts 時，變體表顯示舊值而不是新的更新值

我的更新查詢：

@Query("UPDATE Variant SET cart_count= :cart_count, is_notify_me= :is_Notify,product_id= :product_id WHERE id = :id")
    fun updateVariant(id: Int,is_Notify:Boolean, cart_count: String,product_id: Int) : Int

當我使用此查詢獲取產品時它不起作用：

@Transaction
@Query("SELECT * FROM Product WHERE subcategory_id=:subcatid")
fun getAllProducts(subcatid:Int): Flow<MutableList<Products>>

實際上 Get Query 是正確的，但 Update 查詢是錯誤的

uj5u.com熱心網友回復：

創建如下資料類

data class ProductWithVariants(
  @Embedded val product: Product,
  @Relation(
    parentColumn = "id",
    entityColumn = "productId"
  )
  var variamts: List<Variant>? = null,
)

而在道

@Transaction
@Query("SELECT * FROM product WHERE id:id")
suspend fun getProduct(id: Int): List<ProductWithVariants>

就是這樣，在更新變體之后，選擇查詢將從兩個表中獲取資料并將其組合起來。

您可以為參考完整性添加外鍵。

uj5u.com熱心網友回復：

我相信你誤解了這種關系。

也就是說，您在 Product 表中存盤了一個串列 (MutableList)，但正在更新變體表中 Variant 的行（如果有的話），然后您似乎要提取 Product 并因此提取存盤在 Product 中的重構 Variant表，而不是作為 Variant 表中的子項（如果存在）的更新 Variant。

可能沒有理由讓產品包括：-

@ColumnInfo(name = "variants")
var variants : MutableList<Variants> = mutableListOf()

我的猜測是這就是讓你失望的原因。

示例/演示

也許考慮一下這個可能會讓你感到困惑的演示。該演示基于您的代碼，盡管有一些更改（通常已注釋）。該演示是故意錯誤的，因為它既包括作為產品的一部分存盤的資料，也包括存盤在 Variant 表中的完全相同的核心資料（后者更適合您更新 cart_count 的情況）。

Products 資料類：-

@Entity(tableName = "Product")
data class Products (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int? = null, /* allows generation of the id, if need be (should really be Long though )*/

    @ColumnInfo(name = "name")
    var name  : String? = null,

    @ColumnInfo(name = "variants")
    //var variants : MutableList<Variants> = mutableListOf()
    /* Note changed to suit com.google.code.Gson */
    /* Note probably not a required column anyway */
    var variantsMutableListHolder: VariantsMutableListHolder
)

主要的變化只是為了適應我對 JSON 知之甚少的東西。但是，由于建議不需要 variablesMutableListHolder，請忽略這一點。

VariantsMutableListHolder（建議的解決方案不需要）：-

class VariantsMutableListHolder(
    val variantsMutableList: MutableList<Variants>
)

轉換器（對于 VariantsMutableListHolder，建議的解決方案也不需要）：-

class Converters {
    @TypeConverter
    fun fromVariantsMutableListToJSONString(variantsMutableListHolder: VariantsMutableListHolder): String = Gson().toJson(variantsMutableListHolder)
    @TypeConverter
    fun fromJSONStringToVariantsMutableListHolder(jsonString: String): VariantsMutableListHolder=Gson().fromJson(jsonString,VariantsMutableListHolder::class.java)
}

變體（主要是對參照完整性的建議更改（防止孤兒））：-

@Entity(
    tableName = "Variant",
    /* You may wish to consider adding Foreign Key constraints */
    /*  FK constraints enforce referential integrity*/
    foreignKeys = [
        ForeignKey(
            entity = Products::class, /* The Parent Class */
            parentColumns = ["id"], /* The column or columns (if composite key) that map to the parent */
            childColumns = ["product_id"], /* the column or columns in the child that reference the parent */
            /* Optional but assists in maintaining referential integrity automatically */
            onDelete = ForeignKey.CASCADE, /* if a parent is deleted then so are the children */
            onUpdate = ForeignKey.CASCADE /* if the reference column in the parent is changed then the value in the children is changed */
        )
    ]
)
data class Variants (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int?  = null, /* allows generation of id, if null passed */

    @ColumnInfo(name = "product_id", index = true)
    var product_id : Int?  = null,

    @ColumnInfo(name = "measurement")
    var measurement : String?  = null,

    @ColumnInfo(name = "discounted_price")
    var discounted_price : String?  = null,

    @ColumnInfo(name = "cart_count")
    var cart_count : Int?  = null
)

ProductsWithRelatedVariants新的重要類：-

/* Class for retrieving the Product with the children from the Variant table */
data class ProductsWithRelatedVariants(
    @Embedded
    var products: Products,
    @Relation(
        entity = Variants::class, /* The class of the Children */
        parentColumn = "id", /* the column in the parent table that is referenced */
        entityColumn = "product_id" /* The column in the child that references the parent*/
    )
    var variantsList: List<Variants>
)

AllDao所有 dao 函式（注意沒有 Flows/Suspends 作為用于演示的主執行緒）：-

@Dao
interface AllDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(products: Products): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(variants: Variants): Long
    /* adjusted to suit code in question */
    @Query("UPDATE Variant SET cart_count= :cart_count /*, is_notify_me= :is_Notify*/ ,product_id= :product_id WHERE id = :id")
    fun updateVariant(id: Int/*,is_Notify:Boolean*/, cart_count: String,product_id: Int) : Int
    @Query("SELECT * FROM Variant WHERE id=:id")
    fun getVariantsById(id: Int): Variants
    /* adjusted to suit code in question */
    @Transaction
    @Query("SELECT * FROM Product /*WHERE subcategory_id=:subcatid*/")
    fun getAllProducts(/*subcatid:Int*/): /*Flow<*/MutableList<Products>/*>*/ /*As run on main thread no flow needed */
    @Transaction
    @Query("SELECT * FROM Product")
    fun getAllProductsWithTheRelatedVariants(): MutableList<ProductsWithRelatedVariants>
}

TheDatabase @Database 注釋類，因此可以運行演示：-

@TypeConverters(Converters::class)
@Database(entities = [Products::class,Variants::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries() /* for convenience brevity run on the main thread */
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

MainActivity將上述內容付諸行動：-

const val TAG = "DBINFO"
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 productId=100
        var vm1 = mutableListOf<Variants>(
            Variants(measurement = "10 inches", discounted_price = "11.99", cart_count = 10, product_id = 100),
            Variants(measurement = "10 ounces", discounted_price = "2.50", cart_count = 5, product_id = 100),
            Variants(measurement = "100 grams", discounted_price = "1.75", cart_count = 3, product_id = 100)
        )
        dao.insert(Products(100, name = "Product1",VariantsMutableListHolder(vm1)))
        /* Insert the related variants */
        val insertedVariantIdList: ArrayList<Long> = ArrayList(0)
        for (v in vm1) {
            insertedVariantIdList.add(dao.insert(v))
        }
        /* Update the 2nd Variants */
        dao.updateVariant(insertedVariantIdList[1].toInt(),"99",productId)

        /* Used for building output data (both)*/
        val sb = StringBuilder()

        /*STG001*/
        /* Extract data just stored in the Product table */
        for(p in dao.getAllProducts()) {
            sb.clear()
            for (v in p.variantsMutableListHolder.variantsMutableList) {
                sb.append("\n\tMSR=${v.measurement} DP=${v.discounted_price} CC=${v.cart_count}")
            }
            Log.d(TAG " STG001","PRODUCT NAME IS ${p.name} it has ${p.variantsMutableListHolder.variantsMutableList.size} variants; they are:-$sb")

        }
        /*STG002*/
        /* Extract the data from the Product Table along with the related variants i.e. ProductsWithRelatedVariants */
        for(pwrv in dao.getAllProductsWithTheRelatedVariants()) {
            sb.clear()
            for (v in pwrv.variantsList) {
                sb.append("\n\tMSR=${v.measurement} DP=${v.discounted_price} CC=${v.cart_count}")
            }
            Log.d(TAG " STG002","PRODUCT NAME IS ${pwrv.products.name} it has ${pwrv.products.variantsMutableListHolder.variantsMutableList.size} variants; they are:-$sb")
        }

    }
}

所以當運行時（注意只意味著運行一次演示）： -

將要使用的產品ID設定為100
1. 只需要演示一種產品
2. 100 可以是任何值，只有 100 很簡單，并且演示了設定特定的 id。
構建一個 MutableList 作為 Variants 資料，既可以存盤為 Product 的一部分（建議跳過的位），也可以存盤為 Variant 表中的行。
1. 如果按照建議將變體存盤在變體表中，則注意 id 在這里無關緊要
在 Product 表中插入 Product 和 Variants。
回圈通過 MutableList 將每個 Variants 插入 Variant 表，生成 id 并將其存盤在 ArrayList 中。product_id 設定為插入的 Product 行。
1. 理想情況下，應該檢查每個插入，就好像回傳的 id 是 -1 那樣，由于沖突而沒有插入該行（盡管超出了這個問題的范圍）。
  1. 注意如果 ForeignKey 沖突（例如 99 作為 product_id 而不是 100），則 App 將失敗（超出處理該情況的問題范圍）。
更新其中一個 cart_counts（第二個變體更改為 99）
檢索所有產品并輸出產品詳細資訊和存盤在產品表詳細資訊中的變體（即 cart_count 未更改）
從變體表中檢索具有相關變體的所有產品作為ProductsWithRelatedVariants串列（可以是流）。

結果（作為日志的輸出）：-

D/DBINFO STG001: PRODUCT NAME IS Product1 it has 3 variants; they are:-
        MSR=10 inches DP=11.99 CC=10
        MSR=10 ounces DP=2.50 CC=5
        MSR=100 grams DP=1.75 CC=3


D/DBINFO STG002: PRODUCT NAME IS Product1 it has 3 variants; they are:-
        MSR=10 inches DP=11.99 CC=10
        MSR=10 ounces DP=2.50 CC=99
        MSR=100 grams DP=1.75 CC=3

可以看出，提取了幾乎完全相同的資料，但CC=5 仍存盤在 Product 表中，而在變體中第二種和建議的方式中，CC=99。

此外，查看資料庫中的資料：-

子表中的更新查詢不會反映在父表Android Room Kotlin中

注意突出顯示的臃腫（不必要的資料），而不是 Variant 表中的相同資料：-

子表中的更新查詢不會反映在父表Android Room Kotlin中

替代

另一種方法是更新產品表，這是一個危險的例子： -

UPDATE Product SET variants = substr(variants,1,instr(variants,'"cart_count":5,"'))||'"cart_count":99,"'||substr(variants,instr(variants,'"cart_count":5,"')   length('"cart_count":5,"')) WHERE id=100 AND instr(variants,'"cart_count":5,"');

注意 App Inspection 對 SQLite 的函式進行了例外處理子表中的更新查詢不會反映在父表Android Room Kotlin中

哎呀看起來我在某處有太多的雙引號（這個方法是多么脆弱的一個很好的例子）。

建議的解決方案

這是建議的解決方案，其中將代碼精簡為所需的內容。

@Entity(tableName = "Product")
data class Products (
    @PrimaryKey
    var id : Int? = null, /* allows generation of the id, if need be (should really be Long though )*/
    var name  : String? = null,
)

@Entity(
    tableName = "Variant",
    /* You may wish to consider adding Foreign Key constraints */
    /*  FK constraints enforce referential integrity*/
    foreignKeys = [
        ForeignKey(
            entity = Products::class, /* The Parent Class */
            parentColumns = ["id"], /* The column or columns (if composite key) that map to the parent */
            childColumns = ["product_id"], /* the column or columns in the child that reference the parent */
            /* Optional but assists in maintaining referential integrity automatically */
            onDelete = ForeignKey.CASCADE, /* if a parent is deleted then so are the children */
            onUpdate = ForeignKey.CASCADE /* if the reference column in the parent is changed then the value in the children is changed */
        )
    ]
)
data class Variants (

    @PrimaryKey
    var id : Int?  = null, /* allows generation of id, if null passed */
    @ColumnInfo(index = true)
    var product_id : Int?  = null,
    var measurement : String?  = null,
    var discounted_price : String?  = null,
    var cart_count : Int?  = null
)
/* Class for retrieving the Product with the children from the Variant table */
data class ProductsWithRelatedVariants(
    @Embedded
    var products: Products,
    @Relation(
        entity = Variants::class, /* The class of the Children */
        parentColumn = "id", /* the column in the parent table that is referenced */
        entityColumn = "product_id" /* The column in the child that references the parent*/
    )
    var variantsList: List<Variants>
)
@Dao
interface AllDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(products: Products): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(variants: Variants): Long
    /* adjusted to suit code in question */
    @Query("UPDATE Variant SET cart_count= :cart_count WHERE id = :id")
    fun updateVariant(id: Int, cart_count: String) : Int
    @Query("SELECT * FROM Variant WHERE id=:id")
    fun getVariantsById(id: Int): Variants
    /* adjusted to suit code in question */
    @Transaction
    @Query("SELECT * FROM Product /*WHERE subcategory_id=:subcatid*/")
    fun getAllProducts(): /*Flow<*/MutableList<Products>/*>*/ /*As run on main thread no flow needed */
    @Transaction
    @Query("SELECT * FROM Product")
    fun getAllProductsWithTheRelatedVariants(): MutableList<ProductsWithRelatedVariants>
}
@Database(entities = [Products::class,Variants::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries() /* for convenience brevity run on the main thread */
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

并測驗：-

const val TAG = "DBINFO"
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 productId=100
        var vm1 = mutableListOf<Variants>(
            Variants(measurement = "10 inches", discounted_price = "11.99", cart_count = 10, product_id = 100),
            Variants(measurement = "10 ounces", discounted_price = "2.50", cart_count = 5, product_id = 100),
            Variants(measurement = "100 grams", discounted_price = "1.75", cart_count = 3, product_id = 100)
        )
        dao.insert(Products(100, name = "Product1"))
        /* Insert the related variants */
        val insertedVariantIdList: ArrayList<Long> = ArrayList(0)
        for (v in vm1) {
            v.product_id = productId
            insertedVariantIdList.add(dao.insert(v))
        }
        /* Update the 2nd Variants */
        dao.updateVariant(insertedVariantIdList[1].toInt(),"99")

        /* Used for building output data (both)*/
        val sb = StringBuilder()
        /*STG002*/
        /* Extract the data from the Product Table along with the related variants i.e. ProductsWithRelatedVariants */
        for(pwrv in dao.getAllProductsWithTheRelatedVariants()) {
            sb.clear()
            for (v in pwrv.variantsList) {
                sb.append("\n\tMSR=${v.measurement} DP=${v.discounted_price} CC=${v.cart_count}")
            }
            Log.d(TAG " STG002","PRODUCT NAME IS ${pwrv.products.name} it has ${pwrv.variantsList.size} variants; they are:-$sb")
        }
    }
}

日志中的結果（使用修剪后的代碼）：-

D/DBINFO STG002: PRODUCT NAME IS Product1 it has 3 variants; they are:-
        MSR=10 inches DP=11.99 CC=10
        MSR=10 ounces DP=2.50 CC=99
        MSR=100 grams DP=1.75 CC=3

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

標籤：安卓数据库科特林机器人房间 android-房间关系

上一篇：如何根據另一列的排序順序在MySQL表中添加AutoIncrementId？

下一篇：來自excel資料的Python圖-更改x間隔，但資料來自excel