关于Room DB中的关系，我应该采取哪种方法? [英] Which approach should i go for regarding relations in Room DB?
问题描述
我正在做一个简单的shop-app，以了解有关Android中的Room DB的更多信息，目前，我对于在关系和嵌套对象上哪种方法最合适感到困惑.
I'm doing a simple shop-app to learn more about Room DB in Android and currently I'm a bit confused on which approach is the best to go with when it comes to relations and nested objects.
场景:客户从商店中选择一种商品并订购.之后，数据库使用订单ID更新客户表，以便可以在数据库中搜索客户订单.订单表具有该特定订单的产品ID.在客户的帐户"页面(在应用程序内部)中，应显示包括产品在内的所有订单以及所有必要的信息(例如，订单ID，产品名称，价格，数量等).
Scenario: Customer choose an item from the shop and orders it. Afterward the database updates the customer-table with the order ID so the customers orders can be searched in the DB. The order-table have the products ID's in that specific order. In customers "account" page (inside the app), all the orders including the products should be displayed with all necessary informations (e.g order id, product name, price, quantity etc.).
我已制作出此草图来说明三个表:客户，订单和产品
I've made this sketch to illustrate the three tables: customer, order and product
问题:这里的@Foreign key，@Embedded和@Relation是什么?
Question: What is @Foreign key, @Embedded and @Relation here?
推荐答案
第一件事是可能缺少方案/结果模式.
The first thing is that your Scenario/result schema is probably lacking.
这就是订单与产品"的关系应该是多对多的关系.那就是许多产品可以被许多客户参考.这种关系通常由参考表处理.
That is the Order to Product relationship should probably be a many-many relationship. That is many products can be referenced by many odrers. Such a relationships is typically handled by a reference table.
因此，您将有一个Customer表，一个Order表，其中的一列引用一个Customer，一个Product表，该表不引用任何内容，以及一个引用表，该表具有两列，一列引用该产品，另一列引用该产品.
So you would have a Customer table, an Order table that has a column that references a Customer, a Product table that references nothing and a reference table that has two columns one to reference the product, the other to reference the product.
@ForeignKey 定义了一个要求，即用于引用/关联/关联的一个或多个列必须引用父级中的一个值.
@ForeignKey defines a requirement that the column or columns used to reference/relate/associate must reference a value in the parent.
因此，假设客户"有一个列(或一个可以唯一标识该客户的列，比如说1个代表这样的客户，2个代表另一个，依此类推.那么，订单将有一个列，该列引用要为其下订单的客户(假设一个订单是每个客户的.)然后，外键添加一个约束(规则)，该约束要求订单(子代)"中的值必须是一个值并且存在于客户"表(父代)的引用列中.如果导致此要求的插入(新订单)或(客户或订单的更新)或(客户)删除，则将产生冲突(错误).
So say the Customer has a column (or columns that uniqeuly identifies the customer let's say 1 for one such Customer, 2 for another and so on. Then an Order would have a column that references the Customer for who the order is made (assuming an Order is per a Customer). Then The foreign key adds a constraint (rule) that requires that the value in the Order (The child) must be a value and exists in the referenced column of the Customer table (the parent). If an insert (new Order) or an update (of the Customer or the Order) or a deletion (of the Customer) results in this requirement then a conflict (error) will result.
为了简化维护引用/关系完整性的方式，@ ForeignKey还可以包括在更新或删除Parent值(CASCADE进行更改或删除)时采取的ON UPDATE和ON DELETE操作(CASCADE可能是最常用的选项).给父母的孩子，即把改变归给孩子).
An @ForeignKey can, to simplify maintaining the referential/relationship integrity, also include ON UPDATE and ON DELETE actions (CASCADE perhaps the most typically used option) taken when the Parent value is updated or deleted (CASCADE makes the change or deletion to the Parent's Children i.e. cascading the change to the children).
这种关系不需要外键，但可以提供帮助.
A Foreign key isn't required for such relationships but can assist.
@Embedded 包含要包含在类或Entity中的实体(或非实体类)的字段(来自数据库的字段).
@Embedded includes the fields (columns from a database persepective) of an Entity (or a Non-Entity class) to be included in a class or Entity.
@Reltionship 允许提取/包含相关数据(实体).
@Reltionship allows related data (entities) to be extracted/included.
请考虑以下定义表的实体(根据建议的架构):-
Consider the following Entities that define the tables (as per the suggested schema) :-
@Entity
public class Customer {
@PrimaryKey()
Long customerId;
String customerName;
public Customer(){}
@Ignore
public Customer(String customerName) {
this.customerName = customerName;
}
public Long getCustomerId() {
return customerId;
}
public void setCustomerId(Long customerId) {
this.customerId = customerId;
}
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
}
Product.java
@Entity
public class Product {
@PrimaryKey
Long productId;
String productName;
public Product(){}
@Ignore
public Product(String productName) {
this.productName = productName;
}
public Long getProductId() {
return productId;
}
public void setProductId(Long productId) {
this.productId = productId;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
}
Order.java(对于一个订单可以拥有一个客户的外键)
@Entity(
foreignKeys = @ForeignKey(
entity = Customer.class,
parentColumns = "customerId",
childColumns = "customerReference",
onUpdate = ForeignKey.CASCADE,
onDelete = ForeignKey.CASCADE
),
indices = {@Index(value = "customerReference")}
)
public class Order {
@PrimaryKey
Long orderId;
Long customerReference;
public Order(){}
@Ignore
public Order(long customerReference) {
this.customerReference = customerReference;
}
public Long getOrderId() {
return orderId;
}
public void setOrderId(Long orderId) {
this.orderId = orderId;
}
public Long getCustomerReference() {
return customerReference;
}
public void setCustomerReference(Long customerReference) {
this.customerReference = customerReference;
}
}
OrderProductReference.java(参考表)
@Entity(
primaryKeys = {"orderIdReference","productIdReference"},
foreignKeys = {
@ForeignKey(
entity = Order.class,
parentColumns = {"orderId"},
childColumns = "orderIdReference",
onUpdate = ForeignKey.CASCADE,
onDelete = ForeignKey.CASCADE
),
@ForeignKey(
entity = Product.class,
parentColumns = {"productId"},
childColumns = "productIdReference",
onUpdate = ForeignKey.CASCADE,
onDelete = ForeignKey.CASCADE
)
},
indices = {@Index(value = "productIdReference")}
)
public class OrderProductReference {
long orderIdReference;
long productIdReference;
public OrderProductReference(){}
@Ignore
public OrderProductReference(long customerIdReference, long productIdReference) {
this.orderIdReference = customerIdReference;
this.productIdReference = productIdReference;
}
public long getCustomerIdReference() {
return orderIdReference;
}
public void setCustomerIdReference(long customerIdReference) {
this.orderIdReference = customerIdReference;
}
public long getProductIdReference() {
return productIdReference;
}
public void setProductIdReference(long productIdReference) {
this.productIdReference = productIdReference;
}
}
OrderWithProduct.java
这将嵌入OrderProductReference(表)，并包括要包含(例如嵌入)所引用的订单和产品的关系.
OrderWithProduct.java
This Embeds the OrderProductReference (table) and includes Relationships to include (like Emdedding) the Order and Product that are referenced.
public class OrderWithProduct {
@Embedded
OrderProductReference orderProductReference;
@Relation( entity = Order.class, parentColumn = "orderIdReference", entityColumn = "orderId")
Order order;
@Relation(entity = Product.class, parentColumn = "productIdReference", entityColumn = "productId")
Product product;
}
- 即这是问题的第二部分和第三部分
@Dao
public interface AllDao {
@Insert
long insertCustomer(Customer customer);
@Insert
long insertProduct(Product product);
@Insert
long insertOrder(Order order);
@Insert
long insertProductInOrder(OrderProductReference orderProductReference);
@Transaction
@Query("SELECT * FROM OrderProductReference")
List<OrderWithProduct> getAllOrdersWithProducts();
@Query("SELECT * FROM Customer WHERE customerId = :customerId")
Customer getCustomerById(long customerId);
}
Database.java
@androidx.room.Database(entities = {Customer.class,Product.class,Order.class, OrderProductReference.class}, version = 1)
public abstract class Database extends RoomDatabase {
abstract AllDao allDao();
}
并把它们绑在一起并进行演示
MainActivity.java
public class MainActivity extends AppCompatActivity {
Database database;
AllDao allDao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
database = Room.databaseBuilder(this,Database.class,"mydatabase")
.allowMainThreadQueries()
.build();
allDao = database.allDao();
long custid_fred = allDao.insertCustomer(new Customer("Fred"));
long custid_mary = allDao.insertCustomer(new Customer("Mary"));
long prod_x = allDao.insertProduct(new Product("X"));
long prod_y = allDao.insertProduct(new Product("Y"));
long prod_z = allDao.insertProduct(new Product("Z"));
long order1_fred = allDao.insertOrder(new Order(custid_fred));
long order2_fred = allDao.insertOrder(new Order(custid_fred));
long order1_mary = allDao.insertOrder(new Order(custid_mary));
long opr_ord1_prdx_fred = allDao.insertProductInOrder(new OrderProductReference(order1_fred,prod_x));
long opr_ord1_prdz_fred = allDao.insertProductInOrder(new OrderProductReference(order1_fred,prod_z));
long opr_ord1_prdy_mary = allDao.insertProductInOrder(new OrderProductReference(order1_mary,prod_y));
long opr_ord2_prdy_fred = allDao.insertProductInOrder(new OrderProductReference(order2_fred,prod_y));
List<OrderWithProduct> orderWithProducts = allDao.getAllOrdersWithProducts();
for (OrderWithProduct owp: orderWithProducts) {
Customer currentCustomer = allDao.getCustomerById(owp.order.getCustomerReference());
Order currentOrder = owp.order;
Product currentProduct = owp.product;
Log.d("DBINFO",
"Customer = " + currentCustomer.getCustomerName() +
" Order = " + currentOrder.getOrderId() +
" Product = " + currentProduct.getProductName()
);
}
/*##### INSERT INVALID FOREIGN KEY #####*/
long ooops = allDao.insertOrder(new Order(1000 /*<<<<<<<<<< NOT A CUSTOMER ID */));
}
}
- 请注意将打破外键规则的最后一行(但在添加数据和提取数据之后)
-
allowMainThreadQueries()并不是为了演示的方便/简洁起见. - Note the last line that will break the Foreign Key Rule (but after adding data and extracting the data)
- Also not that
allowMainThreadQueries()has been used for convenience/brevity of the demo.
以上结果为:-
2019-12-31 23:51:56.715 D/DBINFO: Customer = Fred Order = 1 Product = X
2019-12-31 23:51:56.716 D/DBINFO: Customer = Fred Order = 1 Product = Z
2019-12-31 23:51:56.717 D/DBINFO: Customer = Mary Order = 3 Product = Y
2019-12-31 23:51:56.718 D/DBINFO: Customer = Fred Order = 2 Product = Y
2019-12-31 23:51:56.719 D/AndroidRuntime: Shutting down VM
2019-12-31 23:51:56.721 E/AndroidRuntime: FATAL EXCEPTION: main
Process: a.a.so59542439roomcustomerorderproducts, PID: 28703
java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.....MainActivity}: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3270)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3409)
at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
at android.os.Handler.dispatchMessage(Handler.java:107)
at android.os.Looper.loop(Looper.java:214)
at android.app.ActivityThread.main(ActivityThread.java:7356)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)
Caused by: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:879)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.java:51)
at androidx.room.EntityInsertionAdapter.insertAndReturnId(EntityInsertionAdapter.java:114)
at a.a.so59542439roomcustomerorderproducts.AllDao_Impl.insertOrder(AllDao_Impl.java:139)
at a.a.so59542439roomcustomerorderproducts.MainActivity.onCreate(MainActivity.java:53)
at android.app.Activity.performCreate(Activity.java:7802)
at android.app.Activity.performCreate(Activity.java:7791)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1299)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3245)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3409)
at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
at android.os.Handler.dispatchMessage(Handler.java:107)
at android.os.Looper.loop(Looper.java:214)
at android.app.ActivityThread.main(ActivityThread.java:7356)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)
2019-12-31 23:51:56.742 I/Process: Sending signal. PID: 28703 SIG: 9
这篇关于关于Room DB中的关系，我应该采取哪种方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
