This post was published in Android Weekly #282 issue

In the previous article I wrote introduction to the Room persistence library from Android Architecture Components:

Today I’d like to introduce you into creating relationships in database with Room. Let’s start!

Introduction

In SQLite databases we’re allowed to specify relationships between objects, so we can bind one or many objects with one or many others objects. It’s called one-to-many and many-to-many relationships.

For example, we can have user in database. Single user can have many repositories. This is one (user) to many (repositories) relation. But on the other hand, every repository can have also it’s own users-contributors, so for every user we can have many repositories, and every repository can have many users. In this case this is called many-to-many relation.

One-to-many relation

Let’s use the example from the previous post and assume that we have User having many Repositories. In this scenario, we’ll have entity for User and Repo. Firstly, we need to create connection between User and Repo, and then we’ll be able to get proper data from the database.

In the first step, we need to create single entity for the User:

import android.arch.persistence.room.Entity;

import android.arch.persistence.room.PrimaryKey;



@Entity

public class User {

@PrimaryKey public final int id;

public final String login;

public final String avatarUrl;



public User(int id, String login, String avatarUrl) {

this.id = id;

this.login = login;

this.avatarUrl = avatarUrl;

}

}

If you’re not sure what @Entity and @PrimaryKey annotations are for, you can check my previous post.

For Repository model, we’ll reuse the Repo class from the previous post with a single, but significant, change:

import android.arch.persistence.room.Entity;

import android.arch.persistence.room.ForeignKey;

import android.arch.persistence.room.PrimaryKey;



import static android.arch.persistence.room.ForeignKey.CASCADE;



@Entity(foreignKeys = @ForeignKey(entity = User.class,

parentColumns = "id",

childColumns = "userId",

onDelete = CASCADE))

public class Repo {

@PrimaryKey public final int id;

public final String name;

public final String url;

public final int userId;



public Repo(final int id, String name, String url,

final int userId) {

this.id = id;

this.name = name;

this.url = url;

this.userId = userId;

}

}

If you compare this Repo model with the one from the previous post, you’ll notice two differences:

foreignKeys parameter in the @Entity annotation

parameter in the @Entity annotation additional field for userId

Adding foreignKeys means we create connection between this entity and some other class. In this parameter we declare parentColumns, which is name of the id column from User class and childColumns, which is the name of the user id column in Repo class.

Creating this connection is not necessary for having relation, but helps you to define what should happen with the Repo row in database in case of User row delete or update. That’s what is the last parameter for: onDelete = CASCADE . We tell specifically that if user row will be deleted, we’d like to delete also all of it repositories. You can also define similar solution with onUpdate = CASCADE parameter. You can read about other possible solutions in ForeignKey documentation.

Now, after we have our models prepared, we need to create proper SQL query to select repositories for specific user.

SQL query

DAOs are the best place to create our SQL statements. Our RepoDao may look like this:

import android.arch.persistence.room.Dao;

import android.arch.persistence.room.Delete;

import android.arch.persistence.room.Insert;

import android.arch.persistence.room.Query;

import android.arch.persistence.room.Update;

import java.util.List;



@Dao

public interface RepoDao {



@Insert

void insert(Repo repo);



@Update

void update(Repo... repos);



@Delete

void delete(Repo... repos); @Query("SELECT * FROM repo")

List<Repo> getAllRepos();



@Query("SELECT * FROM repo WHERE userId=:userId")

List<Repo> findRepositoriesForUser(final int userId);

}

From the top, we have three methods to insert, update and delete Repo (you can read more about them in my previous post) and two methods for getting data — first one for getting all repositories, and the second for what we actually want — getting repositories for the specific user.

We’ll also need complementary UserDao with methods for insert, update and remove:

import android.arch.persistence.room.Dao;

import android.arch.persistence.room.Delete;

import android.arch.persistence.room.Insert;

import android.arch.persistence.room.Update;



@Dao

public interface UserDao {



@Insert

void insert(User... user);



@Update

void update(User... user);



@Delete

void delete(User... user);

}

Our database class, RepoDatabase, also needs to be updated with proper model classes in @Database annotation and additional abstract method for getting UserDao:

@Database(entities = { Repo.class, User.class },

version = 1)

public abstract class RepoDatabase extends RoomDatabase { ... public abstract RepoDao getRepoDao();

public abstract UserDao getUserDao();

}

And that’s it! Now we can use database to insert users and repositories:

RepoDao repoDao = RepoDatabase

.getInstance(context)

.getRepoDao();



UserDao userDao = RepoDatabase

.getInstance(context)

.getUserDao();



userDao.insert(new User(1,

"Jake Wharton",

"https://avatars0.githubusercontent.com/u/66577"));



repoDao.insert(new Repo(1,

"square/retrofit",

"https://github.com/square/retrofit",

1));



List<Repo> repositoriesForUser = repoDao.

findRepositoriesForUser(1);

Many-to-many relation

In SQL many-to-many (or M:N) relation requires having a join table with foreign keys back to the other entities. We can change example from the previous section, so now not only every user can have many repositories, but also every repository can belong to many users!

To do this, we’ll go back to the simplest versions of our models:

import android.arch.persistence.room.Entity;

import android.arch.persistence.room.PrimaryKey;



@Entity

public class User {

@PrimaryKey

public final int id;

public final String login;

public final String avatarUrl;



public User(int id, String login, String avatarUrl) {

this.id = id;

this.login = login;

this.avatarUrl = avatarUrl;

}

}

And the same for Repo model:

import android.arch.persistence.room.Entity;

import android.arch.persistence.room.PrimaryKey;



@Entity

public class Repo {

@PrimaryKey

public final int id;

public final String name;

public final String url;



public Repo(int id, String name, String url) {

this.id = id;

this.name = name;

this.url = url;

}

}

In the next step we’ll create our join table — UserRepoJoin class

import android.arch.persistence.room.Entity;

import android.arch.persistence.room.ForeignKey;



@Entity(tableName = "user_repo_join",

primaryKeys = { "userId", "repoId" },

foreignKeys = {

@ForeignKey(entity = User.class,

parentColumns = "id",

childColumns = "userId"),

@ForeignKey(entity = Repo.class,

parentColumns = "id",

childColumns = "repoId")

})

public class UserRepoJoin {

public final int userId;

public final int repoId;



public UserRepoJoin(final int userId, final int repoId) {

this.userId = userId;

this.repoId = repoId;

}

}

At first glance it may look horrible, but give it a second chance 🙏

What do we have here?

tableName parameter for giving our table some specific name

parameter for giving our table some specific name primaryKeys parameter for having many primary keys — in SQL we can have not only a single primary key, but also set of primary keys! It’s called composite primary key and it’s used for declaring that every row in our join table should be unique for every pair of userId and repoId

parameter for having many primary keys — in SQL we can have not only a single primary key, but also set of primary keys! It’s called and it’s used for declaring that every row in our join table should be unique for every of and foreignKey parameter is for declaring an array of foreign keys to the other tables. Here we say that userId from our join table is the child id for User class and similarly for Repo model

Now, when we declared foreign keys, we’re ready to prepare SQL statement for inner join:

import android.arch.persistence.room.Dao;

import android.arch.persistence.room.Insert;

import android.arch.persistence.room.Query;

import java.util.List;



@Dao

public interface UserRepoJoinDao {

@Insert

void insert(UserRepoJoin userRepoJoin);



@Query("SELECT * FROM user INNER JOIN user_repo_join ON

user.id=user_repo_join.userId WHERE

user_repo_join.repoId=:repoId")

List<User> getUsersForRepository(final int repoId);



@Query("SELECT * FROM repo INNER JOIN user_repo_join ON

repo.id=user_repo_join.repoId WHERE

user_repo_join.userId=:userId")

List<Repo> getRepositoriesForUsers(final int userId);

}

This way we can get both users for repository, and repositories for user. The last step is to change our RepoDatabase:

@Database(entities = { Repo.class, User.class, UserRepoJoin.class },

version = 1)

public abstract class RepoDatabase extends RoomDatabase { ... public abstract RepoDao getRepoDao();

public abstract UserDao getUserDao();

public abstract UserRepoJoinDao getUserRepoJoinDao();

}

And now we can insert users and repositories into database:

RepoDao repoDao = RepoDatabase

.getInstance(context)

.getRepoDao();



UserDao userDao = RepoDatabase

.getInstance(context)

.getUserDao();



UserRepoJoinDao userRepoJoinDao = RepoDatabase

.getInstance(context)

.getUserRepoJoinDao(); userDao.insert(new User(1,

"Jake Wharton",

"https://avatars0.githubusercontent.com/u/66577"));



repoDao.insert(new Repo(1,

"square/retrofit",

"https://github.com/square/retrofit")); userRepoJoinDao.insert(new UserRepoJoin(1, 1));

Using @Relation annotation

There’s also another way of providing relationships using Room — with a @Relation annotation. You can declare such a relation only inside non-entity class. Let’s look at the example:

@Entity

public class User {

@PrimaryKey public final int id;

public final String login;

public final String avatarUrl;



public User(int id, String login, String avatarUrl) {

this.id = id;

this.login = login;

this.avatarUrl = avatarUrl;

}

} @Entity

public class Repo {

@PrimaryKey public final int id;

public final String name;

public final String url;

public final int userId;



public Repo(int id, String name, String url, int userId) {

this.id = id;

this.name = name;

this.url = url;

this.userId = userId;

}

}

Above we have simply our model classes — User and Repo with userId field. Now we need to create our non-entity model class:

public class UserWithRepos {

@Embedded public User user;



@Relation(parentColumn = "id",

entityColumn = "userId") public List<Repo> repoList;

}

Here we have two new annotations:

@Embedded is for having nested fields — this way we’ll have our User class embedded into our UserWithRepos class

@Relation is for having relation with other model class. Those two parameters say that parentColumn name from User class is id and entityColumn name from Repo class is userId.

This way we can use proper SQL statement in our DAO to select users with all their repositories:

@Dao

public interface UserWithReposDao {



@Query("SELECT * from user")

public List<UserWithRepos> getUsersWithRepos();



}

This is the easiest way, however, you can’t set action upon deleting or updating parents as it was with @ForeignKey annotation.

Conclusion

That’s all! I hope after this post you gained some valuable knowledge about creating object relationships in Room. If you have any suggestion, feel free to comment.

And if you really liked this post and want to make me feel happy, don’t forget to 👏!

In this series there are also:

Android Architecture Components: Room — Introduction

Android Architecture Components: Room — Custom Types

Android Architecture Components: Room — Migration

Android Architecture Components: ViewModel

Android Architecture Components: LiveData

Android Architecture Components: How to use LiveData with Data Binding?