Room android many to many

Database relations with Room

An important part of designing a relational database is splitting the data into related tables and pulling the data together in meaningful ways. Starting with Room 2.2 (now stable) we have support for all possible relations between tables: one-to-one, one-to-many and many-to-many, with one annotation: @Relation .

One-to-one relations

Let’s say that we live in a (sad) world where a person can own only one dog and a dog can have only one owner. This is a one-to-one relation. To model this in a relational database, we create two tables: Dog and Owner , where the Dog table has a reference to the owner id, or the Owner has a reference to a dog id. In Room, we create two entities:

Let’s say that we want to display the list of all dogs and their owners on the screen. To do this, we would create a DogAndOwner data class:

To query this using SQLite, we would need to 1) run two queries: one that gets all owners, and one that gets all dogs based on owner ids and then 2) handle the object mapping.

To get a List using Room, we don’t need to implement the two queries ourselves and handle the object mapping, but rather, use the @Relation annotation.

In our example, since Dog has the owner’s information, we add the @Relation annotation to the dog variable,: specifying that the ownerId column on the parent (i.e. the Owner entity) corresponds to the dogOwnerId :

Our Dao is now simplified to:

Note: Because Room runs the two queries for us under the hood, add the @Transaction annotation, to ensure that this happens atomically.

One-to-many relations

Let’s say that an owner can have multiple dogs (yay!); we’d have a one-to-many relation between Dog and Owner . The database schema we previously defined doesn’t change — we still have the same tables, since the relating key is already in the “many” table of the relationship.

Now, to display the list of owners with their dogs, we need to create a new data class to model this:

To avoid running two separate queries, we can define a one-to-many relation between Dog and Owner , by annotating the List with @Relation as before:

The Dao becomes:

Many-to-many relations

Now suppose we live in a perfect world where an owner can have multiple dogs, and that a dog can have multiple owners. To model this schema, our Dog and Owner tables are not enough. Since a dog can have multiple owners, we need to have multiple entries of the same dog id, matching to different owner ids. Because dogId is the primary key in Dog , we can’t insert multiple dogs with the same id. To overcome this, we need to create an associative table (also known as cross-reference table) that keeps (dogId,ownerId) pairs:

If we now want to get the list of all owners with dogs: List , using just SQLite queries, we need to write two queries: one that gets all owners and one that joins the Dog and the DogOwnerCrossRef tables:

To implement this in Room, we need to update our OwnerWithDogs data class and tell Room that in order to get the Dogs , it needs to use the DogOwnerCrossRef associate table. We reference the table by using a Junction :

In our Dao, we need to select from Owners and return the right data class:

Advanced relation use cases

When using the @Relation annotation, Room infers the entity to use from the type of the annotated property by default. For example, until now we annotated a Dog (or a List ) with @Relation , telling Room how to model the class and which columns to query

If we want to return a different object, for example a Pup , that is not an entity but contains some of the fields, we can specify the entity to use in the @Relation annotation:

If we want to return only specific columns from an entity you need to tell Room which these are by defining them in the projection property of the @Relation . For example, let’s say that we just want to get the names of all the dogs in our OwnerWithDogs data class. Since we would need a List , Room can’t deduce whether those strings correspond to the name or to the breed, so we need to specify the column in the projection:

If you want to define a stricter relationship between the dogOwnerId and ownerId , independent of what kind of relation you’re creating, use a ForeignKey constraint between the fields. Keep in mind that SQLite foreign keys define indices and can have cascading triggers that update or delete entries in your tables. So decide whether you want to use foreign keys based on whether you do want this kind of functionality in your database.

Whether you need one-to-one, one-to-many or many-to-many support, Room has you (and your doggos) covered with one annotation: @Relation . Find out more about Room 2.2 features from our Android Dev Summit ’19 talk:

Источник

Room: Один ко многим

Один ко многим

Тут все просто: в документации сказано как организовать классы, чтобы получить связь сущностей «один ко многим». Берем 2 сущности:

и связываем их в единую сущность:

Для получения новой сущности типа DialogWithTags нужно использовать Dao, которое будет загружать данные из таблицы DialogPojo, при этом автоматически загрузятся tags из связанной таблицы (entity = TagPojo.class):

Используя эти знания уже можно собирать звезду смерти приложение. Однако, в процессе работы могут возникнуть вопросы, ответы на которые лучше знать на подготовительных этапах.

Целостность

Как это ни странно, но запрос на получение DialogWithTags не гарантирует целостность данных. Т.е., возможна ситуация, когда DialogPojo уже загружен, а список TagPojo нет. Предупреждение о возможных проблемах появляется на этапе компиляции программы.А кто их читает, эти предупреждения? Для обеспечения целостности данных, в запрос нужно добавить аннотацию Transaction.

Сохранение

К сожалению, сохранить модель DialogWithTags просто так не получится. Сохранять данные нужно отдельно и, желательно, в одной транзакции, например:

Live Data

Самое большое разочарование ждет при использовании LiveData. Данные будут живыми только для Embedded поля dialog. Изменения для tags отслеживаться не будут. Конечно можно объявить поле tags как LiveData, но, не стоит забывать, что LiveData вернет данные только в том случае, если зарегистрирован хотя бы один обсервер.

Источник

Handling Many to Many SQL Relationship in Android with Room

In this article, I will explain how to create and handle a Many to Many relationships using Room. I will follow these points :

• Description of Many to Many Relationship
• Creating the Room Entity for it
• Inserting a relationship
• Querying the relationship data

For this purpose, I will use the following Entities: Course and Instructor, where an Instructor could be assigned to multiple Courses, and a Course can be taught by multiple Instructors. Let’s take a sample data first.

In the above, we can see the list of instructors and courses available and now we need to map the instructor to the course. This can be achieved by creating another column in the table Course with name instructorID but this will not help us as one course can be taught by multiple instructors and one instructor can be associated with multiple courses, so for this, we need to make a join table or the junction table.

As you can see Arnav Gupta(IntructorID = 1) is mapped to Courses Android Dev(CourseID = 1),Web Dev(CourseID = 2) and Interview Prep(CourseID = 6) and Prateek Narang(IntructorID = 2) is mapped to Courses Machine Learning(CourseID = 3 and Interview Prep(CourseID = 6).So the above table solves the problem of hanlding many to many relationship.Now lets move to the code part.

Let’s take a look at all the entities which we need to define for our tables

Now as we defined our base entities now we will defining our junction table which is required to Assigned an Instructor to a Course.

In the above table, we have created the primary key using the combination of CourseID and InstructorID to maintain the integrity of the database.

Remember that you need to add this table to your Entities list in your Database class.

Now that we have defined a new entity and made the database aware of it, we need to define a function to assign an Instructor. So let’s define a Dao called CourseWithInstructorDao

I’m using the OnConflictStrategy.IGNORE to avoid to insert an AssignedInstructor which already exists with the same course and instructor.

Now we have our main tables, but what we really want is the pair of the course and all its instructors. So now let’s define the class for that.

Look a bit complicated I guess but let’s try to understand the annotations used so first is Embedded. So whenever a class contains a nested object which is also an entity we need to use “@Embedded” annotation next we have Relation which is a convenience annotation that can be used in a Pojo to automatically fetch relation entities. When the Pojo is returned from a query, all of its relations are also fetched by Room so, in a way, it reduces the developer effort of writing complex SQL queries which consist of joins and including multiple tables. Inside Relation Annotation, we have multiple parameters in which we are defining both the columns on which this relation is based upon which are CourseId and InstructorID, important thing to notice here is associateby parameter in which we are providing the Join Table or Junction Table which we created earlier to define the mapping along with parent column and entity column.

In this case, we are taking a course as a parent and its instructors as an entity because we want to get all the list of courses with their instructors. If we want to get the list of all the instructors along with the courses they teach we would be flipping the parent and entity column.

So after declaring all our classes and their relationship the final result we will be getting after calling the getCourses function which we defined earlier in the CourseWithInstructorDao is shown below.

So as we can see we are getting the desired result and on top of that Room will also be clubbing the results of the same course with the list of their corresponding instructors.

It’s not a simple topic, I know but this is probably the only blog on this as of now. Also this will only with Room 2.2.0 and onwards.

If you liked this article, click on the 👏 button, follow me here on Medium and share this article on your socials!

Источник

Android Room: How works One to many relationship + example

Room provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite. One-to-many relationship exists when one row in table A may be linked with many rows in table B, but one row in table B is linked to only one row in table A.

How works room?

The importance of using room to work classes as entities is very powerful but its documentation is very poor on how to work the relations between entities.
To know the basic notions of room you can follow this tutorial https://developer.android.com/training/data-storage/room

Android one to many in room explanation:

For this example using basic concepts of databases and room, first declare their entities Course.java and Student.java:

Course.java is our parent class and we add an id that is auto generated with the @PrimaryKey annotation (autoGenerate = true)

In the child class Student.java an attribute must be added that contains the id of the parent class, in this case id_fkcourse and the annotation @ForeigKey will be used to make the relationship between entities.

To do this, create a new data class where each instance holds an instance of the parent entity and a list of all corresponding child entity instances. Add the @Relation annotation to the instance of the child entity, with parentColumn set to the name of the primary key column of the parent entity and entityColumn set to the name of the column of the child entity that references the parent entity’s primary key.

Add Dao interface:

It’s important to mention that the @Transaccion annotation should be used. One method will be for the course and another for the list of assigned students.
Continuing with the MVVM pattern we already have to have our classes for ViewModel and Repository:

In order for the inserting process not to conflict with the main thread we must add an asynchronous class that handles it.

In the for instruction we assign each student the id of the course that was inserted. This id is generated automatically and we only have to obtain it by assigning a long data type to the result.
This snippet is very important what understand because manage the relationship.
Viewmodel class:

Finally these are the classes that are in charge of managing the relationship of one to many. The CourseWithStudents class is very important since it handles the relationship at the entity level linking both course models with their students.

Источник

Entity Relationship in Room

Note: This article is part of the advanced Room series which covers all the details about the Room persistence library. You can read all the articles here:

• Introduction to Room Persistent Library in Android
• Data Access Objects — DAO in Room
• Entity Relationship in Room [You are here]
• How does Room work internally?
• Room Database Migrations
• Using Room with LiveData and other third-party libraries

So, let’s get started.

This article covers how we can define the relationship between entities in Room persistence library.

Since SQLite is a relational database, entities can have relationships between them. In Room, entities cannot directly reference other entities because it might cause loading unnecessary data every time.

Still, sometimes, you would want to reference other entities from your entity. You can achieve this in multiple ways.

Embedded Objects

You can use the @Embedded annotation to represent an object that you’d like to decompose into its subfields within a table(entity). You can then query the embedded fields just as you would for other individual columns.

Embedded fields can also include other embedded fields.

The table representing a User object then contains columns with the following names: id , firstName , street , state , city , and postCode .

If an entity has multiple embedded fields of the same type, you can keep each column unique by setting the prefix property. Room then adds the provided value to the beginning of each column name in the embedded object.

In the above example, fields of an object are decomposed into an entity. In case you want to represent relationship between multiple entities, you cannot use the @Embedded annotation.

You can use either @Relation annotation or foreignkeys parameter of @Entity annotation for defining the relationship between two entities.
Both of them are different from each other in such a way that @Relation annotation can only be applied on a non-entity class whereas ForeignKey is used on an entity class. Also, ForeignKey affects the schema of an entity that requires that the child column(s) exist in the parent column(s). @Relation is used to join the tables without affecting the schema of tables.

You can define the relationship between entities in 3 ways:

• One-to-one relationship
• One-to-many relationship or Many-to-one relationship
• Many-to-many relationship

One-to-one relationships

A one-to-one relationship between two entities is a relationship where each instance of the parent entity corresponds to exactly one instance of the child entity, and vice-versa.

For example, consider a music streaming app where the user has a library of songs that they own. Each user has only one library, and each library corresponds to exactly one user.

In the above example, User and Library are the entities which have a one-to-one relationship. One of the entities must include a variable that is a reference to the primary key of the other entity( userOwnerId in Library entity).

In order to query the list of users and corresponding libraries, we must first model the one-to-one relationship between the two entities, which is done using UserAndLibrary class. The UserAndLibrary class contains an instance of the parent entity( User ) and the corresponding instance of the child entity( Library ). Then, add the @Relation annotation to the instance of the child entity, with parentColumn set to the name of the primary key column of the parent entity and entityColumn set to the name of the column of the child entity that references the parent entity’s primary key.

Now we can query our database in the following way:

This method requires Room to run two queries, so add the @Transaction annotation to this method to ensure that the whole operation is performed atomically.

One-to-many relationships

A one-to-many relationship between two entities is a relationship where each instance of the parent entity corresponds to zero or more instances of the child entity, but each instance of the child entity can only correspond to exactly one instance of the parent entity.

In the previous music streaming app example, a User can have multiple playlists. Each user can create as many playlists as they want, but each playlist is created by exactly one user.

As we can see, the approach is very similar to one-to-one relationship, the only difference here is in the relationship model( UserWithPlaylists ). Instead of containing a single child entity, it now contains a list of child entity. Querying the database is also very similar.

Many-to-many relationships

A many-to-many relationship between two entities is a relationship where each instance of the parent entity corresponds to zero or more instances of the child entity, and vice-versa.

In the music streaming app example, each playlist can include many songs, and each song can be a part of many different playlists. Therefore, there should be a many-to-many relationship between the Playlist entity and the Song entity.

Many-to-many relationships are distinct from other relationship types because there is generally no reference to the parent entity in the child entity. Instead, a third class is used to represent an associative entity (or cross-reference table) between the two entities. The cross-reference table must have columns for the primary key from each entity in the many-to-many relationship represented in the table.

Now, the next step depends on how you want to query these related entities.

• If you want to query playlists and a list of the corresponding songs for each playlist, create a new data class that contains a single Playlist object and a list of all of the Song objects that the playlist includes.
• If you want to query songs and a list of the corresponding playlists for each, create a new data class that contains a single Song object and a list of all of the Playlist objects in which the song is included.

In either case, model the relationship between the entities by using the associateBy property in the @ Relation annotation in each of these classes to identify the cross-reference entity providing the relationship between the Playlist entity and the Song entity.

Querying the database is similar to the previous approaches.

This is all about Entity Relationship in Room. Hope you enjoyed this blog. In the next blog, we are going to learn How does Room work internally?

You can also connect with me on LinkedIn , Twitter , Facebook and Github .

Источник