Most modern applications today use databases for offline storage. This interaction is very easy using Room Persistence Library.In this tutorial, we’ll learn how to work with multiple tables that have relationships with each other. First, we will go over some core concepts, and then will begin working with JOIN queries in SQL. There are several types of database relationships. Today we are going to cover the following:

  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships

When selecting data from multiple tables with relationships, we will be using the JOIN query.

Room: One-To-One Relationships

In this example you will learn how to map one-to-one relationship using Room. Consider the following relationship between Customer and Address entity. Room: One-To-One mappingTo create this relationship you need to have a CUSTOMER and ADDRESS table. The relational model is shown below. Room: One-To-One mapping

Use a primary key

Each entity must define at least 1 field as a primary key. Even when there is only 1 field, you still need to annotate the field with the @PrimaryKey annotation. Also, if you want Room to assign automatic IDs to entities, you can set the @PrimaryKey‘s autoGenerate property.

@Entity(tableName = "address")
public class Address {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    public long addressId;
    public String street;
    public String city;
    public String state;

Define relationships between objects

You need to specify relationships between customer and address objects. The Room allows you to define Foreign Key constraints between entities. For example, if there’s Customer entity, you can define its relationship to the Address entity using the @ForeignKeyannotation, as shown in the following code snippet.

@Entity(tableName = "customers" ,foreignKeys =
@ForeignKey(entity = Address.class,parentColumns = "id",childColumns = "address_id"))
public class Customer {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    public long customerId;
    @ColumnInfo(name = "customer_name")
    public String customerName;
    @ColumnInfo(name = "address_id")
    public long addressId;

Now we have a relationship between the Customers table and the Addresses table. If each address can belong to only one customer, this relationship is “One to One”. Keep in mind that this kind of relationship is not very common. Our initial table that included the address along with the customer could have worked fine in most cases.Notice that now there is a field named “address_id” in the Customers table, that refers to the matching record in the Address table.

Room: One-To-Many Relationships

This is the most commonly used type of relationship. Consider an e-commerce app, with the following:

  • Customers can make many orders
  • Orders can contain many items
  • Items can have descriptions in many languages

In these cases, we would need to create “One to Many” relationships. Here is an example: In the following example, you will learn how to map one-to-many relationship using Room. Consider the following relationship between Customer and Order entity. Room: One-To-ManyAccording to the relationship, a Customer can have any number of Orders. To create this relationship you need to have a Customer and Order table. The relational model is shown below. Room: One-To-Many Each customer may have zero, one or multiple orders. But an order can belong to only one customer. To create Order table you need to create the following Java bean class.

@Entity(tableName = "orders",foreignKeys =
@ForeignKey(entity=Customer.class,parentColumns = "id",childColumns = "customer_id"))
public class Order {
    @PrimaryKey(autoGenerate = true)
    public long id;
    @ColumnInfo(name = "customer_id")
    public long customerId;
    @ColumnInfo(name = "order_date")
    public Date orderDate;
    public float amount;

Room: Many to Many Relationships

In some cases, you may need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders. Room: Many to Many RelationshipsFor these relationships, we need to create an extra table: Room: Many to Many Relationships The item_order table has only one purpose, and that is to create a “Many to Many” relationship between the items and the orders. To create the items and item_order tables you need to create the following Java Bean class.

@Entity(tableName = "items")
public class Item {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    public long id;
    @ColumnInfo(name = "item_name")
    public String itemName;
    @ColumnInfo(name = "item_price")
    public float itemPrice;

Annotate indices and uniqueness

Sometimes, certain fields or groups of fields in a database must be unique. You can enforce this uniqueness property by setting the unique property of an @Index annotation to true. The following code sample prevents a table from having two rows that contain the same set of values for the order_id and item_id columns:

@Entity(tableName = "items_orders", indices = {@Index("name"),
        @Index(value = {"order_id", "item_id"})},
foreignKeys = {@ForeignKey(entity = Order.class, parentColumns = "id", childColumns = "order_id"),
               @ForeignKey(entity = Item.class, parentColumns = "id", childColumns = "item_id")})
public class ItemOrder {
    @ColumnInfo(name = "order_id")
    public long orderId;
    @ColumnInfo(name = "item_id")
    public long itemId;
    @ColumnInfo(name = "item_count")
    public int itemCount;

Join Queries

Some of your queries might require join tables to calculate the result. Room allows you to write any query. Furthermore, if the response is an observable data type, such as Flowable or LiveData, Room watches all tables referenced in the query for invalidation. The following code snippet shows how to perform a table join to consolidate information between a table.

@Query("SELECT * FROM orders INNER JOIN items_orders
ON items_orders.order_id = INNER JOIN items
ON = items_orders.item_id INNER JOIN customers
LiveData<List<OrderItem>> fetchAllOrderWithItems();

Related Post

Room Persistence Library

How to use DateTime datatype in SQLite Using Room

Room database Migrating