How to merge data with Room Database in Android

I have two different class which one of these class includes list of other class.

There is a NewInvoice class below and it contains a list of invoice items.

@Entity(tableName = "invoices")
public class NewInvoice implements Parcelable {

@NonNull
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "transaction_id")
public int transaction_id;
@ColumnInfo(name = "invoiceNo")
public String invoiceNo;
@ColumnInfo(name = "contactId")
public String contactId;
@ColumnInfo(name = "currencyCode")
public String currencyCode;
@ColumnInfo(name = "description")
public String description;
@ColumnInfo(name = "creationDate")
public long creationDate;
@ColumnInfo(name = "invoiceDate")
public long invoiceDate;
@ColumnInfo(name = "dueDate")
public long dueDate;
@ColumnInfo(name = "shipmentDate")
public long shipmentDate;
@ColumnInfo(name = "invoiceDiscount")
public double invoiceDiscount;
@ColumnInfo(name = "discountType")
public byte discountType;
@ColumnInfo(name = "invoiceAmount")
public double invoiceAmount;
@ColumnInfo(name = "balance")
public double balance;
@ColumnInfo(name = "taxIncluded")
public boolean taxIncluded;
@ColumnInfo(name = "status")
public int status;
@ColumnInfo(name = "type")
public int type;

@Ignore
public List<InvoiceItem> invoiceItems;


public NewInvoice() {
}

Below is the InvoiceItem class

@Entity(tableName = "invoice_items")
public class InvoiceItem implements Parcelable {
@NonNull
@PrimaryKey()
@ColumnInfo(name = "itemId")
private String itemId;
@ColumnInfo(name = "invoiceId")
private String invoiceId;
@ColumnInfo(name = "unitPrice")
private double unitPrice;
@ColumnInfo(name = "quantity")
private double quantity;
@ColumnInfo(name = "inventoryId")
private String inventoryId;
@ColumnInfo(name = "taxIncluded")
private int taxIncluded;
@ColumnInfo(name = "taxRate")
private double taxRate;
@ColumnInfo(name = "discountRate")
private double discountRate;
@ColumnInfo(name = "discountType")
private int discountType;

public InvoiceItem() {
}

I am using Room database. Currently, I query NewInvoice records firstly and then InvoiceItem records related with NewInvoice record and adding InvoiceItem objects to the NewInvoice obcejt’s invoiceItems array list.

Is there a way to query NewInvoice records that includes InvoiceItem records.

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

Typically, this would be achieved by having the two entities with NewInvoice (the list of invoiceItems isn’t then needed but as it is ignored can be left) and then a third POJO class that embeds the NewInvoice with the @Embedded annotation and has the list of InvoiceItems annotated with @Relation.

e.g.

class NewInvoiceWithListOfRelatedInvoiceItems {
    @Embedded
    private NewInvoice newInvoice;
    @Relation(
            entity = InvoiceItem.class,
            parentColumn = "invoiceNo",
            entityColumn = "invoiceId"
    )
    private List<InvoiceItem> invoiceItemList;

    public NewInvoice getNewInvoice() {
        return newInvoice;
    }

    public void setNewInvoice(NewInvoice newInvoice) {
        this.newInvoice = newInvoice;
    }

    public List<InvoiceItem> getInvoiceItemList() {
        return invoiceItemList;
    }

    public void setInvoiceItemList(List<InvoiceItem> invoiceItemList) {
        this.invoiceItemList = invoiceItemList;
    }
}

Then you can have an @Query such as (for a single NewInvoice)

@Transaction
@Query("SELECT * FROM invoices WHERE invoiceNo=:invoiceNo")
NewInvoiceWithListOfRelatedInvoiceItems getInvoiceWithItems(String invoiceNo);

or for all NewInvoices then :-

@Transaction
@Query("SELECT * FROM invoices")
List<NewInvoiceWithListOfRelatedInvoiceItems> getAllInvoicesWithItems();

Alternatively you could, if you make the @Dao an abstract class, use just the classes you have (the POJO is then not required). By having the following Dao’s e.g. :-

@Dao
abstract class NewInvoiceDao {

    @Query("SELECT * FROM invoice_items WHERE invoiceId=:invoiceNo")
    abstract List<InvoiceItem> getInvoiceItemsForANewInvoice(String invoiceNo);

    @Query("SELECT * FROM invoices WHERE invoiceNo=:invoiceNo")
    abstract NewInvoice getNewInvoiceByInvoiceNo(String invoiceNo);

    @Transaction
    @Query("")
    NewInvoice getNewInvoiceWithInvoiceItems(String invoiceNo) {
        NewInvoice rv = getNewInvoiceByInvoiceNo(invoiceNo);
        rv.setInvoiceItems(getInvoiceItemsForANewInvoice(invoiceNo));
        return rv;
    }
}

Additional

In either case it would be advisable to have an index on the invoiceNo and the invoiceId columns (assuming that this is the column used for the relationship).

So for NewInvoice you could have the @Entity annotation as :-

@Entity(tableName = "invoices", indices = {@Index(value = "invoiceNo", unique = true)})

And for InvoiceItem have the @ColumnInfo for the invoiceId column as :-

@ColumnInfo(name = "invoiceId", index = true)
private String invoiceId;

Solution 2

If you want to get NewInvoice records that include InvoiceItem records, you need this query:

SELECT invoiceNo
  FROM invoices
 WHERE invoiceNo IN (
           SELECT invoiceId
             FROM invoice_items
       );

If ‘invoiceNo’ in ‘invoices’ equals ‘invoiceId’ in ‘invoice_items’.

In case you are new to subqueries: https://www.sqlitetutorial.net/sqlite-subquery/

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply