I recently used Android Room when building the Android app for my project Plan Itineraries (Planiti). Since Android Room was unveiled just about a year ago, there were not many tutorials that explained more complicated scenarios. Especially, with the introduction of @Relation , it was a bit confusing at first how this is different from foreign keys. Then, I got stuck at nested to-many and to-one relationships. Finally I figured out a working version, and thought it might help someone in similar situation. Heads up I am an Android newbie coming from iOS development, and any suggestions on my example are so welcome!

Android Room

Announced in May 2017, Room is now the official persistence abstraction layer for developers to leverage SQLite databases. I was still wrestling with iOS Core Data issues before using Room, but as far as I know there were a few third-party alternatives — Realm, ActiveAndroid, or creating your own SQLite wrapper. Personally I prefer staying away from third-party database solutions, since migration from one to another could be quite painful and risky. Like Core Data in iOS, despite its issues (probably worth its own post), it is relatively mature and officially supported. After figuring out how to implement nested relationships, Android Room does feel pretty simple and nice to use.

Entity, DAO, Database

The database setup is actually quite simple. There are three core players in Room:

Entity: it constitutes a table, and schema is defined here.

DAO (Data Access Object): it is like a bridge between database and app. Database creation, read, update, and delete are all accessed in DAO with custom methods and corresponding SQL queries.

Database: the top-level object where database is constructed and DAOs are connected.

My schema

Here is part of the schema in Planiti app, where Trip has many lists ( PlanitiList ), and each list has many places ( ListItem ) to travel to. I picked an example with has many relationships, but it works for other types of relationships as well.

Trip --(has many)--> List<PlanitiList> PlanitiList --(has many)--> List<ListItem>

(Bold texts are entities)

Here I have two kinds of relationships that are implemented with foreign keys:

Has many: the entity that is on the “has many” arrow end has a foreign key to the entity on the beginning end. (e.g. in relation Trip — (has many) → List< PlanitiList >, PlanitiList has a foreign key tripId that points to Trip ‘s ID.)

— (has many) → List< >, has a foreign key that points to ‘s ID.) Belongs to: the entity on the “belongs to” arrow beginning end has a foreign key to the entity on the arrow end. (e.g. in relation Trip — (belongs to)→ User, Trip has a foreign key creatorId that points to User ‘s ID.)

My entities, relations, and DAOs

In this Trip example, it has 3 direct related entities: many DayItem s (that each belongs to one ListItem ), many PlanitiList s (that each has many ListItem ), and one User .

Entities

Trip has one foreign key to its creator, a User entity.

@Entity(tableName = "trips", foreignKeys = [

ForeignKey(

entity = User::class,

parentColumns = arrayOf("remoteId"),

childColumns = arrayOf("creatorId"),

onDelete = ForeignKey.CASCADE

)], indices = [

Index(value = "creatorId"),

Index(value = "remoteId", unique = true)

])

class Trip(var remoteId: Long,

var creatorId: Long,

var name: String, var numDays: Int) {

@PrimaryKey(autoGenerate = true)

@ColumnInfo(name = "localId")

var id: Long = 0

}

PlanitiList belongs to a trip, hence the foreign key tripId .

@Entity(tableName = "lists", foreignKeys = [

ForeignKey(

entity = Trip::class,

parentColumns = arrayOf("remoteId"),

childColumns = arrayOf("tripId"),

onDelete = ForeignKey.CASCADE

)], indices = [

Index(value = "tripId"),

Index(value = "remoteId", unique = true)

])

class PlanitiList(var remoteId: Long, var name: String, var tripId: Long) {

@PrimaryKey(autoGenerate = true)

@ColumnInfo(name = "localId")

var id: Long = 0

}

ListItem has one foreign key to the list (PlanitiList) that it belongs to.

@Entity(tableName = "list_items", foreignKeys = [

ForeignKey(

entity = PlanitiList::class,

parentColumns = arrayOf("remoteId"),

childColumns = arrayOf("listId"),

onDelete = ForeignKey.CASCADE

)], indices = [

Index(value = "listId"),

Index(value = "remoteId", unique = true)

])

class ListItem(var remoteId: Long, var listId: Long, var name: String, var url: String?) {

@PrimaryKey(autoGenerate = true)

@ColumnInfo(name = "localId")

var id: Long = 0

}

User has NO relations, a simple entity.

@Entity(tableName = "users", indices = [

Index(value = "remoteId", unique = true)

])

class User(var remoteId: Long,

var username: String) {

@PrimaryKey(autoGenerate = true)

@ColumnInfo(name = "localId")

var id: Long = 0

}

Relation Classes

After defining the entities based on the schema, it is time to write classes (POJOs) that include entities with its relationships.

Because trip has two levels of relationships ( Trip → PlanitiList → ListItem ), we have to create two classes to describe each level of relationship.

First, the relationship where PlanitiList has many ListItem :

class ListAndListItems {

@Embedded

var list: PlanitiList? = null



@Relation(parentColumn = "remoteId", entityColumn = "listId", entity = ListItem::class)

var listItems: List<ListItem>? = null

}

A ListAndListItems object would then contain the base entity PlanitiList and its relation ListItem . You can have multiple @Relation annotations here to fetch different relationships in the same pass.

Similar to above, now we are ready to create the second level of relationship that includes two levels of relationships:

class TripAndListsAndListItems {

@Embedded

var trip: Trip? = null



@Relation(parentColumn = "creatorId", entityColumn = "remoteId", entity = User::class)

var user: List<User>? = null



@Relation(parentColumn = "remoteId", entityColumn = "tripId", entity = PlanitiList::class)

var lists: List<ListAndListItems>? = null

}

Because ListAndListItems also includes PlanitiList ‘s relation, this TripAndLists class now describes two levels of relationship.

DAO (Data Access Object)

Finally, after entities and relations are all defined, we can load the relation classes with DAO. It is as simple as:

@Dao

interface TripAndListsAndListItemsDao {

@Query("SELECT * FROM trips WHERE remoteId = :tripId")

fun loadTrip(tripId: Long): Single<TripAndListsAndListItems>

}

Room knows how to fetch TripAndListsAndListItems from the top level Trip down to ListItem because each relation class specifies the base entity and its relations.

Wrap up

The concept of the example in this post can be applied to many other kinds of nested relationships. Room also throws compile errors on incorrect @Relation parent and entity column specifications. As long as the schema is configured right, any level of relations can be described and fetched via Room.

References

Official Room Relation doc

Lastly, try Planiti (web, iOS and Android) when planning the next vacation 🗺