The лун with the room

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:

Источник

how to use foreign key in Room persistence library

I am working with room persistence library in android, i would appreciate if someone can help me in using foreign key, how to get data by using foreign key.

3 Answers 3

Just to summarize the above posts for future readers:

The foreign key syntax in Kotlin is

The foreign key syntax in Java is:

Note: foreignKeys is an array, so in Java enclose @ForeignKey elements in

You can refer to the official documentation for more information. https://developer.android.com/reference/androidx/room/ForeignKey

Here how you can define and access a One-to-many (Foreign Key) relationship in Android Jetpack Room. Here the Entities are Artist and Album and the foreign key is Album.artist

And finally the DAO

trying this out, (all these operations happens in a Coroutine)

Logging out all Artists

D/debug: All Artists [ArtistAndAlbums(artist=Artist(id=hillsongunited, name=Hillsong United), albums=[Album(albumId=empires, name=Empires, artist=hillsongunited), Album(albumId=wonder, name=Wonder, artist=hillsongunited), Album(albumId=people, name=People, artist=hillsongunited)]), ArtistAndAlbums(artist=Artist(id=planetshakers, name=Planet Shakers), albums=[Album(albumId=rain, name=Rain, artist=planetshakers), Album(albumId=itschristmas, name=Its Christmas, artist=planetshakers), Album(albumId=overitall, name=Over It All, artist=planetshakers)])]

Logging out albums by a specific artist,

D/debug: Albums by artist ID: ArtistAndAlbums(artist=Artist(id=hillsongunited, name=Hillsong United), albums=[Album(albumId=empires, name=Empires, artist=hillsongunited), Album(albumId=wonder, name=Wonder, artist=hillsongunited), Album(albumId=people, name=People, artist=hillsongunited)])

Источник

The key to my room. Vs The key of my room

I am looking for the key of my room.

I am looking for the key to my room.

I have just known that the second is right and the first is wrong. However, sometimes it is confusing in a lot of situations to discern what the right one is.

Is there any rule in regard to the usage of to and of, particularly when we want to describe the belongings or possessions of something?!

Do I have to say

the damage to my house, or the damage of my house

the door to my room, or the door of my room

the password to my account, or the password of my account?

2 Answers 2

I have studied four Indo-European languages in addition to my native English. If there are any rules on which prepositions are proper in which situations, they are not easily discerned or explicated in any of those languages. In English, the predominant preposition relating to accessibility seems to be «to.»

So «door to that room,» «key to that lock,» and «password to that account» are all idiomatic. However, «door of that room,» «key for that lock,» and «password for that account» are also idiomatic.

That quasi-rule about «to» being associated with accessibility has no apparent relevance to the phrase «damage to,» but «to» is idiomatic although sometimes «damage in» will be idiomatic.

EDIT: Although «the key of that lock» does not sound euphonius, «that lock’s key» sounds perfectly natural. Prepositions are weird.

The choice of to or of is largely governed by idiom and will vary according to the context. More often than not, native English speakers will refer to the key to the safe, the door, the house, resolving a problem and much else.

It’s idiomatic. In fact, in most cases, they will simply say the room/safe/door key but that’s not what you are asking about.

The key of my room is acceptable but less likely than the key to my room. And the same is true for the door and the password. Damage is definitely to something and NOT of something.

Google Books Ngram Viewer indicates that while the expression the key of was twice as popular as the key to in the first half of the 19th century, the key to subsequently prevailed and is now about six times as common as the key of.

It’s hard to make a rule but as a guide, prefer the key to as the safer option in most instances.

Источник

West wing room 221 key