Skip to content Skip to sidebar Skip to footer
Home / Android / Android Room / Database

Which Approach Should I Go For Regarding Relations In Room Db?

Post a Comment
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 o

Solution 1:

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 :-

@EntitypublicclassCustomer {

    @PrimaryKey()
    Long customerId;
    String customerName;

    publicCustomer(){}

    @IgnorepublicCustomer(String customerName) {
        this.customerName = customerName;
    }

    publicLonggetCustomerId() {
        return customerId;
    }

    publicvoidsetCustomerId(Long customerId) {
        this.customerId = customerId;
    }

    publicStringgetCustomerName() {
        return customerName;
    }

    publicvoidsetCustomerName(String customerName) {
        this.customerName = customerName;
    }
}

Product.java

@EntitypublicclassProduct {

    @PrimaryKeyLong productId;
    String productName;

    publicProduct(){}

    @IgnorepublicProduct(String productName) {
        this.productName = productName;
    }

    publicLonggetProductId() {
        return productId;
    }

    publicvoidsetProductId(Long productId) {
        this.productId = productId;
    }

    publicStringgetProductName() {
        return productName;
    }

    publicvoidsetProductName(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")}
)
publicclassOrder{

    @PrimaryKeyLong orderId;
    Long customerReference;

    public Order(){}

    @Ignorepublic Order(long customerReference) {
        this.customerReference = customerReference;
    }

    publicLong getOrderId() {
        return orderId;
    }

    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }

    publicLong 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")}
        )
publicclassOrderProductReference {

    long orderIdReference;
    long productIdReference;

    publicOrderProductReference(){}

    @Ignore
    publicOrderProductReference(long customerIdReference, long productIdReference) {
        this.orderIdReference = customerIdReference;
        this.productIdReference = productIdReference;
    }

    publiclonggetCustomerIdReference() {
        return orderIdReference;
    }

    publicvoidsetCustomerIdReference(long customerIdReference) {
        this.orderIdReference = customerIdReference;
    }

    publiclonggetProductIdReference() {
        return productIdReference;
    }

    publicvoidsetProductIdReference(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 classOrderWithProduct {

    @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)
publicabstractclassDatabaseextendsRoomDatabase {

    abstract AllDao allDao();
}

And to tie it all together and demonstrate

MainActivity.java

publicclassMainActivityextendsAppCompatActivity {
    Database database;
    AllDao allDao;

    @OverrideprotectedvoidonCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        database = Room.databaseBuilder(this,Database.class,"mydatabase")
                .allowMainThreadQueries()
                .build();
        allDao = database.allDao();
        longcustid_fred= allDao.insertCustomer(newCustomer("Fred"));
        longcustid_mary= allDao.insertCustomer(newCustomer("Mary"));

        longprod_x= allDao.insertProduct(newProduct("X"));
        longprod_y= allDao.insertProduct(newProduct("Y"));
        longprod_z= allDao.insertProduct(newProduct("Z"));

        longorder1_fred= allDao.insertOrder(newOrder(custid_fred));
        longorder2_fred= allDao.insertOrder(newOrder(custid_fred));
        longorder1_mary= allDao.insertOrder(newOrder(custid_mary));

        longopr_ord1_prdx_fred= allDao.insertProductInOrder(newOrderProductReference(order1_fred,prod_x));
        longopr_ord1_prdz_fred= allDao.insertProductInOrder(newOrderProductReference(order1_fred,prod_z));
        longopr_ord1_prdy_mary= allDao.insertProductInOrder(newOrderProductReference(order1_mary,prod_y));
        longopr_ord2_prdy_fred= allDao.insertProductInOrder(newOrderProductReference(order2_fred,prod_y));

        List<OrderWithProduct> orderWithProducts = allDao.getAllOrdersWithProducts();
        for (OrderWithProduct owp: orderWithProducts) {
            CustomercurrentCustomer= allDao.getCustomerById(owp.order.getCustomerReference());
            OrdercurrentOrder= owp.order;
            ProductcurrentProduct= owp.product;
            Log.d("DBINFO",
                    "Customer = " + currentCustomer.getCustomerName() +
                            " Order = " + currentOrder.getOrderId() +
                            " Product = " + currentProduct.getProductName()
            );
        }

        /*##### INSERT INVALID FOREIGN KEY #####*/longooops= allDao.insertOrder(newOrder(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-3123:51:56.715 D/DBINFO: Customer = Fred Order = 1 Product = X
2019-12-3123:51:56.716 D/DBINFO: Customer = Fred Order = 1 Product = Z
2019-12-3123:51:56.717 D/DBINFO: Customer = Mary Order = 3 Product = Y
2019-12-3123:51:56.718 D/DBINFO: Customer = Fred Order = 2 Product = Y




2019-12-3123:51:56.719 D/AndroidRuntime: Shutting down VM
2019-12-3123: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-3123:51:56.742 I/Process: Sending signal. PID: 28703 SIG: 9

Post a Comment for "Which Approach Should I Go For Regarding Relations In Room Db?"