Object Relational Mapping (ORM) libraries are common tools for back-end development for interacting with a relational database. As the name implies they help facilitate mapping your data that is stored in a relational database to objects used in your application code. Room is the standard ORM library that sits between your Android application and its SQLite database.
While not necessary, ORMs provide a lot of functionality out of the box to save the developer time. Using an ORM allows the developer to interact with the database with the same language used in the back end. Instead of having to concern themselves with creating complex SQL queries, interactions with the database can look as simple as a normal function call.
ORMs typically support object references between entities. Or more specifically, nested entity relationships. For example, consider the following Java code snippet for a Hibernate entity relationship (Hibernate is another popular ORM):
@Entity
public class StudentEntity {
@Id
@Column(name = "STUDENT_ID")
public Long id;
//....
@JoinColumn(name = "ADDRESS_ID")
public AddressEntity address;
// ...
}
@Entity
public class AddressEntity {
@Id
@Column(name = "ADDRESS_ID")
public Long id;
@Column(name = "STREET_NAME")
public String streetName;
// ...
}
The StudentEntity
object has an AddressEntity
object as a member of its class. This means that when a StudentEntity
is fetched from the database the AddressEntity
object will also be fetched, either eagerly or lazily, and be readily available to access from the StudentEntity
object.
someStudent.address.streetName
While Room provides a lot of features, it intentionally does not support this behavior. The focus of this article is to see how we can establish and manage object relationships in Room without relying on nested entities. As we'll see, there are a few different approaches, each valid in its own way depending on the use case.
💡
In case you are wondering why exactly Room doesn't allow this behavior, there is a section in the Android documentation explaining their reasoning. However, in short, nested models can either be loaded lazily, meaning they are loaded at the time of access, or eagerly, meaning all entities are loaded at once. On Android, lazy loading would occur on the UI thread, and even a fast database query could result in the system being unable to meet the 16 ms time limit to calculate and redraw a frame. Eager loading is the other option, but this could result in lots of data being loaded even if the UI doesn't require it, therefore wasting memory.
I will provide code snippets throughout the article, however, a full sample project can be found here on my GitHub. Since the focus of this article is on the database and not the UI, the UI is bare bones, but it provides enough functionality to demonstrate the different approaches.
@Embedded
Annotation
The easiest approach is to embed the nested object into the entity. This is done by annotating the nested object with @Embedded
which tells Room to create additional columns in the parent entity's table for all the fields in the nested object. Essentially, Room is destructuring the nested object and treating all its fields as if it were part of the parent entity.
The following shows an example model setup. Notice that the nested address object is a simple data class, not an entity. I did add @ColumnInfo
annotations to the fields in the nested object just for naming clarity, but it does not affect functionality.
@Entity(tableName = "studentWithEmbedded")
data class StudentWithEmbeddedEntity(
@PrimaryKey(autoGenerate = true)
val id: Long = 0,
@ColumnInfo(name = "first_name")
val firstName: String,
@ColumnInfo(name = "last_name")
val lastName: String,
@Embedded
val address: AddressForEmbedded,
)
data class AddressForEmbedded(
@ColumnInfo(name = "house_number_embedded")
val houseNumber: String,
@ColumnInfo(name = "street_name_embedded")
val streetName: String,
@ColumnInfo(name = "street_type_embedded")
val streetType: String,
@ColumnInfo(name = "city_embedded")
val city: String,
@ColumnInfo(name = "state_embedded")
val state: String,
@ColumnInfo(name = "postal_code_embedded")
val postalCode: String,
)
Now when Room creates the studentWithEmbedded
table in the database, every field in the nested AddressForEmbedded
object will be its own column. Therefore, a full list of columns in the studentWithEmbedded
table will be:
id
first_name
last_name
house_number_embedded
street_name_embedded
street_type_embedded
city_embedded
state_embedded
postal_code_embedded
Pros and Cons
Pros:
Probably the simplest of all the approaches
Can handle one-to-one relationships, one student has only one address
The address data is queryable
Cons:
Can not handle a one-to-many, or many-to-many relationship. This means a row in the
studentWithEmbedded
table can only have one and only one address association.If multiple students are associated with the same address, then multiple rows in the
studentWithEmbedded
table will have identical values in the associated address columns, i.e., duplicate data.
Type Converters
Another approach is to use a Type Converter to serialize the child object to a JSON string, and then store the whole object as a string in one of the table's columns for the parent entity. When the parent entity is fetched from the database the Type Converter will deserialize the string into the correct object.
The model setup is almost the same as before. The only real differences are the lack of the @Embedded
annotation, and the addition of an id
field in the address object. While an ID is not technically necessary for this to work, this approach can handle one-to-many and many-to-many relationships, so an ID is helpful.
@Entity(tableName = "studentWithJson")
data class StudentWithJsonEntity(
@PrimaryKey(autoGenerate = true)
val id: Long = 0,
@ColumnInfo(name = "first_name")
val firstName: String,
@ColumnInfo(name = "last_name")
val lastName: String,
@ColumnInfo(name = "address_json")
val address: AddressForJson,
)
data class AddressForJson(
val id: Long = 0,
val houseNumber: String,
val streetName: String,
val streetType: String,
val city: String,
val state: String,
val postalCode: String,
)
With the models set, we can then create a Type Converter to go back and forth between AddressForJson
and String
. For this example, I'm using the Moshi library to handle the JSON conversions.
@ProvidedTypeConverter
class MyRoomTypeConverters(private val moshi: Moshi) {
@TypeConverter
fun fromAddressJson(json: String): AddressForJson {
return moshi.adapter(AddressForJson::class.java).fromJson(json)!!
}
@TypeConverter
fun toAddressJson(address: AddressForJson): String {
return moshi.adapter(AddressForJson::class.java).toJson(address)
}
}
Room will create the StudentWithJsonEntity
database table with the following columns:
id
first_name
last_name
address_json
Pros and Cons
Pros:
Still relatively simple
Can handle a one-to-one relationship.
Can handle a one-to-many relationship, one student can have multiple addresses. In this case, the entity would instead have a field for
List<AddressForJson>
.
Cons:
The same duplicate data problem as before, if multiple students are associated with the same address, then multiple rows in the
studentWithJson
table will have identical values in theaddress_json
data.From the duplicate data issue in the first point, this approach technically can't handle a many-to-many relationship. While students can have multiple addresses and an address can be used by multiple students, since all addresses are stored in their separate JSON strings, there are no relationships between two identical addresses from different student rows.
The address data is not queryable.
Intermediate Models
The last approach is the most complex, but also the most powerful since it best leverages Room and SQLite. An important distinction between this approach and the previous two is that both objects we are trying to relate are entities with their own respective tables. We can define relationships between entities by using a few different components together:
Room's
@Relation
annotationRoom's
@ForeignKey
annotationRoom's
@Embedded
annotationIntermediate model
Unlike the previous two approaches where we try and have a nested relationship, this approach avoids that by using a third model called the intermediate model. The intermediate model contains two fields, one for the parent entity and one for the child entity(s).
The @Relation
annotation is used on the child entity in the intermediate model, and it is important because it defines how to relate both tables when retrieving data.
The @ForeignKey
annotation is important because it helps enforce the relational structure when inserting/modifying data. If you try and insert a row into the child table with a value for the foreign key column that doesn't match any primary key in the parent table an exception will be thrown.
android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
Ultimately, the power of this approach is that depending on how we use these components we can model a one-to-one, one-to-many, or many-to-many relationship.
One-to-One Relationship
Starting with the simplest relation type, consider again the student and address objects.
@Entity(tableName = "studentWithOneToOneRelation")
data class StudentWithOneToOneRelationEntity(
@PrimaryKey(autoGenerate = true)
val studentId: Long = 0,
val firstName: String,
val lastName: String
)
@Entity(tableName = "addressForOneToOneRelation")
data class AddressForOneToOneRelationEntity(
@PrimaryKey(autoGenerate = true)
val id: Long = 0,
val houseNumber: String,
val streetName: String,
val streetType: String,
val city: String,
val state: String,
@ColumnInfo(name = "postal_code")
val postalCode: String,
val studentOwnerId: Long
)
There are two things to note:
The
StudentWithOneToOneRelationEntity
does not have a nestedAddressForOneToOneRelationEntity
field.The
AddressForOneToOneRelationEntity
has a reference (studentOwnerId
) that points back to the primary key forStudentWithOneToOneRelationEntity
.
With our two entities defined, we now need to create the intermediate object:
data class StudentWithAddressOneToOneIntermediate(
@Embedded
val student: StudentWithOneToOneRelationEntity,
@Relation(
parentColumn = "studentId",
entityColumn = "studentOwnerId"
)
val address: AddressForOneToOneRelationEntity?
)
As discussed before, there are only two fields, one for student
and one for address
. The student
field is marked with @Embedded
since it is the parent, and the address field has the @Relation
annotation, since it is the child. The @Relation
annotation needs to be configured with two properties: parentColumn
, which is the name of the primary key column in the parent entity, and entityColumn
, which is the name of the column in the child entity that references the parent entity's primary key.
With the relationship configured, we can define a query function in the DAO interface for the student.
@Dao
interface StudentWithOneToOneRelationDao {
// ...
@Transaction
@Query("SELECT * from studentWithOneToOneRelation")
fun getAllStudentsWithAddresses(): List<StudentWithAddressOneToOneIntermediate>
}
The function is annotated with @Query
that selects from the student table as we would normally do. However, there are two important differences:
The list that is being returned contains intermediate objects (
StudentWithAddressOneToOneIntermediate
) instead of the table's entity (StudentWithOneToOneRelationEntity
)The query is marked as a transaction. This is because Room will query the fields in the intermediate object separately, therefore it needs to make sure that all separate query operations are complete before returning.
Foreign Key Constraint
What we've done so far is enforce a one-to-one relationship between StudentWithOneToOneRelationEntity
and AddressForOneToOneRelationEntity
, but only when retrieving data. We still need to complete the circle and enforce the one-to-one relationship during the time of insertion/modification, because right now there are two issues. First, it's still possible to insert a row into the address table where the studentOwnerId
value does not match any studenId
in the student table. Second, it's possible to have two rows in the address table refer to the same student, therefore breaking the one-to-one relationship.
To fix the first issue, we just need to update the child entity, AddressForOneToOneRelationEntity
, by adding a foreign key.
@Entity(
tableName = "addressForOneToOneRelation",
foreignKeys = [
ForeignKey(
entity = StudentWithOneToOneRelationEntity::class,
parentColumns = arrayOf("studentId"),
childColumns = arrayOf("studentOwnerId"),
onDelete = ForeignKey.CASCADE
)
],
)
data class AddressForOneToOneRelationEntity(
// ...
)
Where:
entity
- The name of the parent table.parentColumns
- The name of the column in the parent table that the foreign key references.childColumns
- The name of the column in the child table that contains the foreign key value.onDelete
- Defines how to treat the child entity when the parent entity it references is deleted. In the case of cascade, when the parent entity is deleted the child entity is also deleted.
To fix the second issue we once again update the @Entity
annotation for AddressForOneToOneRelationEntity
. It has an indices
property in which we can add an Index
for the foreign key column, and require it to be unique in the table.
@Entity(
tableName = "addressForOneToOneRelation",
foreignKeys = [
ForeignKey(
entity = StudentWithOneToOneRelationEntity::class,
parentColumns = arrayOf("studentId"),
childColumns = arrayOf("studentOwnerId"),
onDelete = ForeignKey.CASCADE
)
],
indices = [Index(value = ["studentOwnerId"], unique = true)]
)
data class AddressForOneToOneRelationEntity(
// ...
)
Now if we try and insert rows with duplicate studentOwnerId
values an exception will be thrown.
Process: com.nicholasfragiskatos.objectrelationshipsinroom, PID: 10239
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: addressForOneToOneRelation.studentOwnerId (code 2067 SQLITE_CONSTRAINT_UNIQUE)
One-to-Many Relationship
Luckily, the one-to-many relationship is almost identical to the one-to-one relationship except for a couple of minor tweaks.
The child entity no longer needs the unique index.
The intermediate model has a list of addresses instead of just one.
@Entity(tableName = "studentWithOneToManyRelation")
data class StudentWithOneToManyRelationEntity(
@PrimaryKey(autoGenerate = true)
val studentId: Long = 0,
val firstName: String,
val lastName: String
)
@Entity(
tableName = "addressForOneToManyRelation",
foreignKeys = [
ForeignKey(
entity = StudentWithOneToManyRelationEntity::class,
parentColumns = arrayOf("studentId"),
childColumns = arrayOf("studentOwnerId"),
onDelete = ForeignKey.CASCADE
)
]
)
data class AddressForOneToManyRelationEntity(
@PrimaryKey(autoGenerate = true)
val id: Long = 0,
val houseNumber: String,
val streetName: String,
val streetType: String,
val city: String,
val state: String,
@ColumnInfo(name = "postal_code")
val postalCode: String,
val studentOwnerId: Long
)
data class StudentWithAddressOneToManyIntermediate(
@Embedded
val student: StudentWithOneToManyRelationEntity,
@Relation(
parentColumn = "studentId",
entityColumn = "studentOwnerId"
)
val address: List<AddressForOneToManyRelationEntity>?
)
@Dao
interface StudentWithOneToManyRelationDao {
// ...
@Transaction
@Query("SELECT * from studentWithOneToManyRelation")
fun getAllStudentsWithAddresses(): List<StudentWithAddressOneToManyIntermediate>
}
Many-to-Many Relationship
For this approach, the parent entity stays the same again, but the child entity no longer uses a foreign key, which also means it no longer needs the studentOwnerId
field.
@Entity(tableName = "studentWithManyToManyRelation")
data class StudentWithManyToManyRelationEntity(
@PrimaryKey(autoGenerate = true)
val studentId: Long = 0,
val firstName: String,
val lastName: String
)
@Entity(tableName = "addressForManyToManyRelation")
data class AddressForManyToManyRelationEntity(
@PrimaryKey(autoGenerate = true)
val addressId: Long = 0,
val houseNumber: String,
val streetName: String,
val streetType: String,
val city: String,
val state: String,
@ColumnInfo(name = "postal_code")
val postalCode: String
)
The Many-to-Many relationship is slightly more involved, and requires the use of a junction table (I've also seen this referred to as an Association Table, or Join Table), which like any other table, is modeled with an entity class. It's a two-column table where one column holds a reference to the primary key of the parent table and the other column holds a reference to the primary key of the child table. This helps resolve the many-to-many relationship. The primary key for the junction table itself is the composite of both columns.
For this example, the junction table entity contains two fields, one for the student ID and one for the address ID. Then we make sure to specify both fields as primary keys in the @Entity
annotation. Since this is a many-to-many relationship there can be multiple rows with duplicate student IDs, and multiple rows with duplicate address IDs, but there can not be multiple rows with a duplicate combination of IDs. It's also probably a good idea to define two foreign keys with cascade deletes, one pointing to the student entity, and one pointing to the address entity. This will avoid having orphaned rows in the table when either a student or address is deleted.
@Entity(
tableName = "studentAddressCrossRef",
primaryKeys = ["studentId", "addressId"],
foreignKeys = [
ForeignKey(
entity = StudentWithManyToManyRelationEntity::class,
parentColumns = ["studentId"],
childColumns = ["studentId"],
onDelete = ForeignKey.CASCADE
),
ForeignKey(
entity = AddressForManyToManyRelationEntity::class,
parentColumns = ["addressId"],
childColumns = ["addressId"],
onDelete = ForeignKey.CASCADE
)
]
)
data class StudentAddressCrossRef(
val studentId: Long,
val addressId: Long
)
In the intermediate object the @Relation
annotation now refers to the junction table. The parentColumn
property points to the studentId
column in the junction table, and the entityColumn
property points to the addressId
column in the junction table. Lastly, there is the addition of the associateBy
property that specifies the junction table entity class.
data class StudentWithAddressManyToManyIntermediate(
@Embedded
val student: StudentWithManyToManyRelationEntity,
@Relation(
parentColumn = "studentId",
entityColumn = "addressId",
associateBy = Junction(StudentAddressCrossRef::class)
)
val address: List<AddressForManyToManyRelationEntity>?
)
Finally, we define the DAO interface as before.
@Dao
interface StudentWithManyToManyRelationDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun saveStudent(student: StudentWithManyToManyRelationEntity): Long
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun saveAddress(address: AddressForManyToManyRelationEntity): Long
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun saveStudentAddressCrossRef(ref: StudentAddressCrossRef): Long
@Insert(onConflict = OnConflictStrategy.REPLACE)
@Transaction
fun saveAddress(address: AddressForManyToManyRelationEntity, studentId: Long) {
saveAddress(address)
saveStudentAddressCrossRef(StudentAddressCrossRef(studentId, address.addressId))
}
@Query("DELETE FROM studentWithManyToManyRelation where studentId = :studentId")
fun deleteStudent(studentId: Long): Int
@Query("DELETE FROM addressForManyToManyRelation where addressId = :addressId")
fun deleteAddress(addressId: Long): Int
@Transaction
@Query("SELECT * from studentWithManyToManyRelation")
fun getAllStudentsWithAddresses(): List<StudentWithAddressManyToManyIntermediate>
}
It's important to note that when it comes to inserting new student or address data, if there is a relationship then the junction table needs to be manually updated. The business logic of when and how a relationship is formed is entirely up to your use case. However, the important point is that just saving a new student or address using the standard @Insert
functions do not automatically create a new entry in the junction table.
As a simple example, I added the following utility functions to the DAO to facilitate inserting a new address and junction table row within one transaction.
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun saveStudentAddressCrossRef(ref: StudentAddressCrossRef): Long
@Insert(onConflict = OnConflictStrategy.REPLACE)
@Transaction
fun saveAddress(address: AddressForManyToManyRelationEntity, studentId: Long) {
saveAddress(address)
saveStudentAddressCrossRef(StudentAddressCrossRef(studentId, address.addressId))
}
Conclusion
Room is a powerful and convenient ORM for Android app development, but it does not support nested entity relationships like other ORMs that are used on more traditional back ends. This limitation stems from the need to preserve the responsiveness of the UI and avoid consuming unnecessary memory resources. Despite this limitation, there are still ways to create object relationships.
First, we looked at embedding the child object into the parent object using Room's @Embedded
annotation. This approach is simple, but it can only support one-to-one relationships, and it does not efficiently handle identical child data being referenced by multiple parent entities.
Next, we looked at using a Type Converter to serialize the child object to a JSON string to store the whole object as a string in one of the columns for the parent entity. When the parent entity is fetched from the database the Type Converter will deserialize the string into the correct object. This approach can handle one-to-one and one-to-many relationships, but it also requires duplicating data. However, what might be even worse is that the child data is not easily queryable.
Finally, we learned about the most complex, but powerful approach which best leverages Room and SQLite by treating both parent and child objects as entities, and using a combination of the@Relation
annotation, embedded objects, foreign keys, and intermediate models. This approach allows us to define one-to-one, one-to-many, and many-to-many relationships between objects all while keeping the data queryable without data duplication.
Thank you for taking the time to read my article. I hope it was helpful.
If you noticed anything in the article that is incorrect or isn't clear, please let me know. I always appreciate the feedback.
Top comments (1)