CODEWITHSUNDEEP
androiddatabaseandroid-room
Delice
Delice

Reputation: 856

Which approach should i go for regarding relations in 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.

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 enter image description here

Question: What is @Foreign key, @Embedded and @Relation here?

Upvotes: 1

Views: 1345

Answers (1)

MikeT
MikeT

Reputation: 56953

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.

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 defines a requirement that the column or columns used to reference/relate/associate must reference a value in the parent.

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.

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 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 allows related data (entities) to be extracted/included.

Example/Demo

Consider the following Entities that define the tables (as per the suggested schema) :-

Customer.java :-

@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 (with Foreign Key for the one Customer that an order can have)

@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 (reference table)

@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

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;
}
  • i.e. this is parts 2 and 3 of the question

AllDao.java (all Dao's combined for convenience)

@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();
}

And to tie it all together and demonstrate

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 */));
    }
}
  • 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.

The above results in :-

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

Upvotes: 2

Related Questions