Room: An SQLite Object Mapping Library
Miu, Magda
Handling an SQLite database in Android implies challenges like: a lot of boilerplate code, database operations on the main thread, queries checked at runtime and especially unmaintainable code. At Google I/O 2017, the Android team launched Room, an SQLite object mapper, that provides a set of components and features that will solve all these challenges and will help us, the developers, to have a better experience when using a database in our app. The presentation will include examples about how to use the main components from Room (@Entity, @Dao, @Database), how to handle the relations between the entities, how to use the (observable) queries and not run database operations on the main thread. Also we will discover more details about the migration support, compile time query verification and what’s happening behind the scenes.
 Okay. Hello, everybody. Let's play the imagination game. Please try to find out an answer for the next question. If you have the possibility to change something in the Android development, what will it be that thing? Do you have something in mind? Yeah. Maybe the way to handle the lifecycle of the UI components, or maybe the way to update the UI when something is changing your database, or even the way to handle an SQLI database in the Android development? Google addressed the same question to the Android developers, and they got also an interesting answer. One of the developers said that he doesn't want to deal with the technical things. What do you mean by that? What technical things? You are a developer? He specified that he just wanted to deliver the project. His explanation was related to the fact that sometimes we have a lot of tasks to be finished, a lot of issues that should be solved, and you don't have enough time in order to make a detailed research about a specific topic. In that moment, you just want to finish the project and move on. I don't know if this is the best approach, but this was his point of view. If you take a closer look on the way to handle an SQLI database in Android, we will find this documentation page related to the SQLI Open Helper class. We could manage an SQLI class by extending this class, and inside of it, we will need to define a lot of constants for the database version, for the database name, for all the tables that we should define, and also for all the fields that should be created, all the columns of those tables. Other than that, we need to create the table, and after the table was created, we need to insert the data inside of it. And finally, we will get the data. And you write this code, a lot of code, and you just want to run your app and see if it works. And your app will crash because you just forgot to add a comma. So what are the main SQLI challenges? We have a lot of boilerplate code. We have SQL queries checked at runtime. We have database operations on the main thread, and also we have unmaintainable and untestable code. As an answer to these things that should be improved in the Android development, at Google I.O. 2017, Google launched architecture components. These are more a set of guidelines that could help us, the developers, to create better apps. At Google I.O. this year, architecture components are actually part of the Android jetpack. And the developers were happy again. My presentation will focus on ROOM, which is an object relational mapping library. Why an ORM? First of all, because of the productivity. When you are using an ORM, we write less code. We just define the models, and the tool is responsible to generate the code for us. Other than that, we have also an application design as the main advantage of an ORM. When you are using an ORM, the concerns are really well separated, and you are able to also follow some design patterns. Other than that, another reason that should be used in an ORM is the application maintainability. By using a good ORM tool, you care less about the code generated by it. It should be well tested. So we just need to focus on adding new elements in our database schema. Also the developers were the ones who decided the features that should be included in ROOM. They decided to have compile time verification. Because it's important to know that if your query is written correctly or not. So Android Studio will write a compilation error if something is not correctly created. Other than that, we need to have also migration support. Because usually we take care about the user's data, and we want to maintain it across the versions. They didn't want to have a Java query builder API. And the reason behind of this is the fact that usually when you are using a Java query builder API, the code is not so predictable. So you write an inner join between three or three tables. And when you check again your code after two or two weeks, you will not understand from the beginning what's happening there. Other than that, they also like to have SQL completion in Android Studio. So the tool will help us to write the SQLs. And for sure, since we are using a relational database, we need to have relationships between the entities. Are you ready to start? Okay. I hope yes. So first of all, we need to define the dependencies in our Gradle file. At this moment, the current version of ROOM is 1.1.1.1. And if you are using Android extension, the current version is 2.0. An interesting thing to mention here is the fact that ROOM will work only for the minimum SDK 14. So we have defined the dependencies. After that, we need to define the components. We have three types. Entity, data access, object, and the database. Let's start with entity. Let's say we have a company class. Our company class has three fields, the ID of the company, the name, and the picture. And I would like to save in the database only the first two fields. I mean only the ID and the name. So my table should have this structure, where the ID will be the primary key. In order to do that in ROOM, I will just need to annotate my class with the entity annotation. And inside of it, we could also specify the name of the table that should be created in the database. For each field, we could mention a column info annotation in order to define the name of the column for that table. Since our company ID will be the primary key of the table, we will annotate with the primary key annotation. And because we don't want to save the picture in the database, we will annotate to it, ignore. So our table has this structure. And let's say that I would like to also add a date when the company was inserted in my database. So this field is a date type. I will try to use this approach. But do you think it will work in SQLite? No, it will not, because SQLite doesn't support the date type. So we will need to find a way to solve this issue. And the best approach for this case is to use an integer type. So, and this for sure will work. So we will try to translate the date to an integer. And RUM offers us a type converter. So in our case, we will have two methods. One that will translate the time step to a date. And the other one that will translate the date to a time step. This type converter could be applied to a field. And also it could be applied to an entire data access object class or entity. Or it could be available at the entire database level. Let's go to the DAOs, data access objects. Let's say that I would like to get the data from my company table. So in SQLite, I will write something like this, select all from company. Actually RUM understands the SQL queries. So we could use it directly in our Java or Kotlin code. In our case, the DAO will be actually an annotation added to an interface. In this case, the company DAO interface. And inside of it, we have some methods to query, insert, update, and delete the data. Let's say that by mistake, instead of company, I'll write companies. In this case, we will get a compilation error that will let us know that the company's table doesn't exist in our database. Okay, let's take a closer look to the DAO class. So it's an interface. Let's take this example with department DAO. We have an insert all method inside of it. This class is an interface. So since it's an interface, someone should provide the implementation for it. In our case, RUM will provide this implementation. So behind the scenes, RUM will create a class that will implement our interface. Inside of it, we'll find the private final reference to our database. And also the insert all method implemented. Inside of the insert all method, we will get a transaction that is started at the beginning. After that, the data is inserted in the table. We set the transaction successfully, and finally, the transaction is closed. Let's go to the database annotation. Database annotation actually combines the entities and the DAOs. So in our case, we will use the database annotation. And inside of it, we will need to specify the entities that should be saved in our database. So in our case, it's the company entity. If we have many entities, we could specify them using a comma. Another important thing is that we need to specify also the version of the database. After that, we have a class that actually extends the RUM database. It's an abstract class. And inside of it, we will need to have some abstract methods in order to have a reference to our DAOs. For sure, since it's a database, we need to have an instance of it. In this case, I use a single tone in order to get the instance of our database. And inside of this single tone, we have two possibilities. We could use database builder or in-memory database. The difference between these two static methods is the fact that in-memory database will not persist the data. So if we will add some changes, we will insert some new fields or some new data, we will update something, those changes will not be persisted in our database. In this case, I use the database builder method. Other than that, we need to specify also the name of the class which is annotated with the database annotation and the name of the file for this database. If you are taking a closer look, you will see that this approach looks similar with retrofit if you have used it until now. Okay. We defined the dependencies, the main components. Let's go to the relations. SQLite is a relational database. So it understands the relations between the entities. But why I use the quotes for the word relations? I use those quotes because in room, entities cannot contain entities. Since you are using a relational database, SQLite from an object-oriented language, Java or Kotlin, for sure you have read something about object-relational-impendence mismatch. This is a fancy way of saying, yeah, it's difficult getting stuff into and out of the database. And usually the purpose of an ORM is to solve this issue or at least to make it easier, the translation between the entities and the Java or Kotlin classes. Room also tries to solve this issue. But let's see why Google decided not to have entities inside of another entity. Let's go back to our company example. Our company has also a CEO. So I would like to add a reference to this employee with special rights in my entity class. So I would like to have a reference of it. After that, I get the requirement that I should display all the companies in a recycle view. So I have a recycle view with all the company names inside of it. And after that, I get a new requirement that I should display also the name of the director for each company. It looks like a simple task. I will add a new text view. I will set the text company.getdirector.getname. And I'm done with the task. But is it okay to do that? Right now we are trying to run a database operation on the main thread. So even if our query will be very, very fast, the main thread has only 60 milliseconds in order to render the UI and do all the stuff that should be done at that moment. So this is why Google decided not to have entities inside of another entity. Okay. But how will handle the relations? We have three approaches. Let's go back to our example. Our company has two offices. One in San Francisco and another one here in Berlin. The one from here is the headquarter. So I would like to save these two locations in my company table. So I will have a class location with two fields, latitude and longitude. And I would like to insert this data in my table. So in order to do that, Ruhm will help us by having nested objects. And we could achieve this thing by using the embedded annotation. So I will embed these two locations. At this moment, Ruhm will try to create two columns with the same name because Ruhm will go back and see that location has latitude and longitude. And in this case, for each office, we'll try to create two columns for latitude and two columns for longitude. But this thing is not possible in SQLite, so we need to fix this issue. The solution is to use the prefix, and we will annotate the headquarter location with the prefix HQ. So in this case, we will be able to save all these two locations in my table. So right now, this is the current structure of our table. Okay. Our company has also employees. So it looks like a classical approach for a foreign key. Ruhm also offers support. So we will do this classical thing. We have a relation one to many. So the company ID, the primary key of the company table will become the foreign key of the employee table. We have also the foreign key annotation. And this foreign key annotation should be used for the entity that contains the foreign key. Inside of it, we will need to define the parent entity, the parent columns, and the child columns. Another way to handle the relationships between the entities in Ruhm is by using the relation annotation. Let's say that our company has many departments. And I would like to get a list of all the departments from a company using the company ID. But I don't like to connect them using a foreign key. So the solution is to create a pojo. And to combine the company entity with a list of departments. So I will embed the company entity. And the list of departments, I will annotate it with relation. An important thing to mention here is that the relation annotation should be used only for a field that is a list or a set. Okay. So we define our pojo. So I would like to use it. In order to be used, we will create a DAO class that will query separately the fields in order to get the data that we want. In this case, all the departments from a company. Okay. Let's move on to the queries. We have four different options. We have insert, update, delete, and query. Insert, update, and delete. Works similar. I mean, we could use these methods for one object, a list of objects, or a var arcs of objects. In my case, all the methods have as a return type the void. But we could also get a value for each one of them. In case of the insert method, that value will represent actually the new ID that is inserted in the database. And for update and delete, that value will represent actually the number of the rows affected. Let's move on to the query. Query will be used with insert, update, delete, and select. I already mentioned that Room supports SQL queries. So we are able to write SQL queries directly in our code. In this example, I have a classical approach, the first one, but we have also observable queries. With live data and flowable. So Room offers support for live data and edX Java. What are these observable queries? These observable queries are used when you want to notify the UI about some changes that appeared in our database. So each time something is changed in the database, the UI will be updated accordingly. We have also the opportunity to use the raw query. And we don't know from the beginning how we will manage the SQL. I mean, we will not know from the beginning how the SQL will look. We will need to create it dynamically. So this is the best approach for raw query. And also raw query could be used in combination with live data. But if we will try to use it in this combination, we need to specify the entity that should be observed. Other than that, if we will try to run a database operation on the main thread, we will get an illegal state exception. So don't try it. Transaction. There are some scenarios when you want to run different operations on the same block. In this case, we will use the transaction annotation. In my case, I have an insert of a new department. And after that, I just want to delete an oldest one. So I just need to annotate my method with the transaction annotation. And everything will be executed in the same block. The transaction annotation could be used also with query. And there are two types of scenarios in which this could be used. The first one is when we have a big amount of data. And we are not sure that this data will fit in the cursor window size, which is about two mega. And the second scenario is when we are using a pojo with the relation annotation. Because in that scenario, room will query separately the fields. So in order to make sure that we have consistent data, we should use the transaction annotation. Another interesting thing to mention is related to the threading part. If we are using room 1.0, and you have two different threads. On the first thread, you are trying to write something, and on the second one, you are trying to read something. In room 1.0, these two operations will not be done in the same time. So on the first thread, we will need to write. The second one will wait. And after the write is done, we could also read the data. This was available on room 1.0. In room 1.1, we solved this issue, and at this moment, you could run both operations at the same time. So we write and read at the same time. Migration. This is a painful topic sometimes. Room also offers migration support. So let's see what's happening. We have our company table, which has this current structure in version 1. And for the next release, we need to add a ref number inside of it. So we will need to change the database schema. What does it mean to offer migration? It means to keep the data of the user from a version to another one. So to make the migration of the data from version 1 to version 2 in our case. If you are using the SQLite approach, we just need to override the onupgrade method, which has three parameters, the database, the old version and the new version. This is the classical approach. But wait, we are trying to use room. Let's see how we can manage it in room. In room, we have the class migration, and we need to override the onmigrafe method. So this is the room approach. In our case, we will just need to alter the company table by adding a new column ref number. So this is the migration from one to two. Do you see also it's added here in the constructor. In order to apply this migration, we will use addmigrations method inside of the database builder. And if we are having many migrations, we will specify them using a comma. So if we have from one to two to three and so on, you could add them only by using a comma. Let's see what's happening behind the scenes. Behind the scenes, there is a room master table. And inside of this room master table, we have an identity hash. This identity hash is changed every time our database schema is changed. So based on that, the room will know that we will need to offer or not a migration. So if you go to see how your database looks, by using an explorer, you will find also this room master table. And inside of it, you will find the identity hash. Other than that, we are having the opportunity to export the database schema in our folder in the project. By using this annotation processor option, we will export the JSON file that could be used to see all the versions of our database. So it's a good thing for the new members of the team. And also for us, when we forgot what happened in version five, let's say. And another thing is that this JSON file is used in order to test the migrations. If we don't provide the migration, we will get also an illegal state exception. In the case when we are not interested to keep the user's data, there are two approaches that could be used. We have the method fall back to destructive migration. In the case when you just want to keep the data and you just want to get the latest database schema. So when you want to clear the data, use fall back to destructive migration. There is a second scenario when we have many database versions. Let's say we have seven different database versions. And I want to offer migration support only for the first four versions. And for the other ones, I'm not interested to keep the data. So to solve this feature, we could use fall back to destructive migration from. And instead of it, we should specify the versions of the database that we will not be kept for which we will not keep the data of the user. So what are the main advantages? We have less boilerplate code. We have SQL queries that are checked at compile time. We don't have database operations on the main thread. The code is maintainable. And also we have migration support. If you have questions, these are the resources. Thank you. And thank you, Sean. Yes, please. One question. Can you query partial objects? In your examples, you always have query star and get the full data out of it. Can you just request company name, for instance? Yes, exactly. You can use it and add two points and specify the name of the parameter. And that parameter will be used in your method. But I will get back the whole DAO. It's just not filled. Or can I specify the simple thing? You can combine them. Exactly like in SQLite. You can add and or inner join everything you want. Everything that is available in SQLite is available also in room. And the result object? Sorry? How is that defined? The result object. I make a query, I select something, I select just company name. Do I get back a company object? Or do I get back, can I define just an object containing a string? Usually in this case, when you have many columns in your table and you just want to get a subset, you should create a pojo. Because a room will give you the initial object. Or if you are interested to get only a subset of the columns, you will create a pojo and you will get only those columns from your table. Then I can annotate the members with the column name. Yeah, it is not necessary to add a column name for annotation. If you define the field and it has the same value like in the database, it will work. If not, and if you prefer to have a name in Java or Kotlin and another name in the database, you should use the column name for annotation. Okay. Do I have to set the observe entity for that? Or? What do you mean? Observable query to have the live data for it? Or what? Yes, if I have live data. If you need it to be updated in the UI, if not, it is not necessary. It depends on what exactly would you like to do with it. But it will always update when the whole table was updated. Let's say if I only select the name, I'm only interested in the changes in the name, of course. Yeah. So actually, live data in Flopole works. They are notified every time when something is changed at that table. It doesn't know exactly what was changed or if it's related to your object that you want to get. It just let you know that something was changed in that table. Okay. And then we'll do a whole week query. Yes. Okay. Thank you. Thank you, too. Any other questions? Does room also offer many to many relationships? It's not by default. We just need to follow the same approach. You split the tables. You have one table, those two tables, and a joint table that will have that primary key combined from the two primary keys of those two other tables. So I have to create a table in between by myself. Exactly. A joint table. Thank you. Okay. You have this example with the bitmap. Yes. It's an attribute. And you said it to ignore. Yes. So you persisted in the database. But what would you do in this example to read it? Because then you would get a null bitmap, right? So we would lose information after persisting it, right? So the idea was if you annotate a fill with ignore annotation, that fill will not be persisting in your table. But at some later case, you want to read it from the database and then the bitmap is there, so you would need to do something else. A migration. If after that you change your mind and you want to save it also. Anyway, it's not a good idea to save a bitmap in the database. That's a thing that I think everybody knows. But let's say you want to save the path of that picture. Okay? So in version one, you had only the name and the ID. And in version two, you want also to have the path. So in this case, you should provide a migration. Yeah. Okay. Makes sense. Thank you, too. Any other questions? I have a question, actually. How does Room handle the relationship with API and with the database SQLite? With the API? Yes. So, Room, it's only an ORM for SQLite. So it speed up the development in terms of SQLite database. So everything that was available in SQLite, it's also available in Room. For the API, you need to use whatever you are using, retrofit or what else you are using right now. And you get the data from the API and you save it in the database. So there are two different layers. This is the offline layer for SQLite and the API layer is the business logic or how you structure the classes in your project. Okay, so the API doesn't talk directly to Room. That would be to the repository and then just to the... Yes, exactly. Exactly. Thank you. Yes, please. Since Room manages all the queries internally or the query function itself, is it injection proof? I think so. Yeah. So, take your company query, set a new company name, name it, whatever. Yeah, honestly, I didn't try it. Simicolon drop tables. I didn't try it, but you could encrypt your data if it's necessary. I didn't try it. Thank you. Any other questions? Okay, then, thank you very much.
 Okay.  Hello, everybody.  Let's play the imagination game.  Please try to find out an answer for the next question.  If you have the possibility to change something in the Android development, what will it be  that thing?  Do you have something in mind?  Yeah.  Maybe the way to handle the lifecycle of the UI components, or maybe the way to update  the UI when something is changing your database, or even the way to handle an SQLI database  in the Android development?  Google addressed the same question to the Android developers, and they got also an interesting  answer.  One of the developers said that he doesn't want to deal with the technical things.  What do you mean by that?  What technical things?  You are a developer?  He specified that he just wanted to deliver the project.  His explanation was related to the fact that sometimes we have a lot of tasks to be finished,  a lot of issues that should be solved, and you don't have enough time in order to make  a detailed research about a specific topic.  In that moment, you just want to finish the project and move on.  I don't know if this is the best approach, but this was his point of view.  If you take a closer look on the way to handle an SQLI database in Android, we will find  this documentation page related to the SQLI Open Helper class.  We could manage an SQLI class by extending this class, and inside of it, we will need  to define a lot of constants for the database version, for the database name, for all the  tables that we should define, and also for all the fields that should be created, all  the columns of those tables.  Other than that, we need to create the table, and after the table was created, we need to  insert the data inside of it.  And finally, we will get the data.  And you write this code, a lot of code, and you just want to run your app and see if it  works.  And your app will crash because you just forgot to add a comma.  So what are the main SQLI challenges?  We have a lot of boilerplate code.  We have SQL queries checked at runtime.  We have database operations on the main thread, and also we have unmaintainable and untestable  code.  As an answer to these things that should be improved in the Android development, at Google  I.O. 2017, Google launched architecture components.  These are more a set of guidelines that could help us, the developers, to create better apps.  At Google I.O. this year, architecture components are actually part of the Android jetpack.  And the developers were happy again.  My presentation will focus on ROOM, which is an object relational mapping library.  Why an ORM?  First of all, because of the productivity.  When you are using an ORM, we write less code.  We just define the models, and the tool is responsible to generate the code for us.  Other than that, we have also an application design as the main advantage of an ORM.  When you are using an ORM, the concerns are really well separated, and you are able to  also follow some design patterns.  Other than that, another reason that should be used in an ORM is the application maintainability.  By using a good ORM tool, you care less about the code generated by it.  It should be well tested.  So we just need to focus on adding new elements in our database schema.  Also the developers were the ones who decided the features that should be included in ROOM.  They decided to have compile time verification.  Because it's important to know that if your query is written correctly or not.  So Android Studio will write a compilation error if something is not correctly created.  Other than that, we need to have also migration support.  Because usually we take care about the user's data, and we want to maintain it across the  versions.  They didn't want to have a Java query builder API.  And the reason behind of this is the fact that usually when you are using a Java query  builder API, the code is not so predictable.  So you write an inner join between three or three tables.  And when you check again your code after two or two weeks, you will not understand from  the beginning what's happening there.  Other than that, they also like to have SQL completion in Android Studio.  So the tool will help us to write the SQLs.  And for sure, since we are using a relational database, we need to have relationships between  the entities.  Are you ready to start?  Okay.  I hope yes.  So first of all, we need to define the dependencies in our Gradle file.  At this moment, the current version of ROOM is 1.1.1.1.  And if you are using Android extension, the current version is 2.0.  An interesting thing to mention here is the fact that ROOM will work only for the minimum  SDK 14.  So we have defined the dependencies.  After that, we need to define the components.  We have three types.  Entity, data access, object, and the database.  Let's start with entity.  Let's say we have a company class.  Our company class has three fields, the ID of the company, the name, and the picture.  And I would like to save in the database only the first two fields.  I mean only the ID and the name.  So my table should have this structure, where the ID will be the primary key.  In order to do that in ROOM, I will just need to annotate my class with the entity annotation.  And inside of it, we could also specify the name of the table that should be created in  the database.  For each field, we could mention a column info annotation in order to define the name  of the column for that table.  Since our company ID will be the primary key of the table, we will annotate with the primary  key annotation.  And because we don't want to save the picture in the database, we will annotate to it, ignore.  So our table has this structure.  And let's say that I would like to also add a date when the company was inserted in my  database.  So this field is a date type.  I will try to use this approach.  But do you think it will work in SQLite?  No, it will not, because SQLite doesn't support the date type.  So we will need to find a way to solve this issue.  And the best approach for this case is to use an integer type.  So, and this for sure will work.  So we will try to translate the date to an integer.  And RUM offers us a type converter.  So in our case, we will have two methods.  One that will translate the time step to a date.  And the other one that will translate the date to a time step.  This type converter could be applied to a field.  And also it could be applied to an entire data access object class or entity.  Or it could be available at the entire database level.  Let's go to the DAOs, data access objects.  Let's say that I would like to get the data from my company table.  So in SQLite, I will write something like this, select all from company.  Actually RUM understands the SQL queries.  So we could use it directly in our Java or Kotlin code.  In our case, the DAO will be actually an annotation added to an interface.  In this case, the company DAO interface.  And inside of it, we have some methods to query, insert, update, and delete the data.  Let's say that by mistake, instead of company, I'll write companies.  In this case, we will get a compilation error that will let us know that  the company's table doesn't exist in our database.  Okay, let's take a closer look to the DAO class.  So it's an interface.  Let's take this example with department DAO.  We have an insert all method inside of it.  This class is an interface.  So since it's an interface, someone should provide the implementation for it.  In our case, RUM will provide this implementation.  So behind the scenes, RUM will create a class that will implement our interface.  Inside of it, we'll find the private final reference to our database.  And also the insert all method implemented.  Inside of the insert all method, we will get a transaction that is started at the beginning.  After that, the data is inserted in the table.  We set the transaction successfully, and finally, the transaction is closed.  Let's go to the database annotation.  Database annotation actually combines the entities and the DAOs.  So in our case, we will use the database annotation.  And inside of it, we will need to specify the entities that should be saved in our database.  So in our case, it's the company entity.  If we have many entities, we could specify them using a comma.  Another important thing is that we need to specify also the version of the database.  After that, we have a class that actually extends the RUM database.  It's an abstract class.  And inside of it, we will need to have some abstract methods in order to have a reference  to our DAOs.  For sure, since it's a database, we need to have an instance of it.  In this case, I use a single tone in order to get the instance of our database.  And inside of this single tone, we have two possibilities.  We could use database builder or in-memory database.  The difference between these two static methods is the fact that in-memory database will not  persist the data.  So if we will add some changes, we will insert some new fields or some new data, we will  update something, those changes will not be persisted in our database.  In this case, I use the database builder method.  Other than that, we need to specify also the name of the class which is annotated with  the database annotation and the name of the file for this database.  If you are taking a closer look, you will see that this approach looks similar with  retrofit if you have used it until now.  Okay.  We defined the dependencies, the main components.  Let's go to the relations.  SQLite is a relational database.  So it understands the relations between the entities.  But why I use the quotes for the word relations?  I use those quotes because in room, entities cannot contain entities.  Since you are using a relational database, SQLite from an object-oriented language, Java  or Kotlin, for sure you have read something about object-relational-impendence mismatch.  This is a fancy way of saying, yeah, it's difficult getting stuff into and out of the  database.  And usually the purpose of an ORM is to solve this issue or at least to make it easier,  the translation between the entities and the Java or Kotlin classes.  Room also tries to solve this issue.  But let's see why Google decided not to have entities inside of another entity.  Let's go back to our company example.  Our company has also a CEO.  So I would like to add a reference to this employee with special rights in my entity class.  So I would like to have a reference of it.  After that, I get the requirement that I should display all the companies in a recycle view.  So I have a recycle view with all the company names inside of it.  And after that, I get a new requirement that I should display also the name of the director  for each company.  It looks like a simple task.  I will add a new text view.  I will set the text company.getdirector.getname.  And I'm done with the task.  But is it okay to do that?  Right now we are trying to run a database operation on the main thread.  So even if our query will be very, very fast, the main thread has only 60 milliseconds in  order to render the UI and do all the stuff that should be done at that moment.  So this is why Google decided not to have entities inside of another entity.  Okay.  But how will handle the relations?  We have three approaches.  Let's go back to our example.  Our company has two offices.  One in San Francisco and another one here in Berlin.  The one from here is the headquarter.  So I would like to save these two locations in my company table.  So I will have a class location with two fields, latitude and longitude.  And I would like to insert this data in my table.  So in order to do that, Ruhm will help us by having nested objects.  And we could achieve this thing by using the embedded annotation.  So I will embed these two locations.  At this moment, Ruhm will try to create two columns with the same name because Ruhm will  go back and see that location has latitude and longitude.  And in this case, for each office, we'll try to create two columns for latitude and two  columns for longitude.  But this thing is not possible in SQLite, so we need to fix this issue.  The solution is to use the prefix, and we will annotate the headquarter location with  the prefix HQ.  So in this case, we will be able to save all these two locations in my table.  So right now, this is the current structure of our table.  Okay.  Our company has also employees.  So it looks like a classical approach for a foreign key.  Ruhm also offers support.  So we will do this classical thing.  We have a relation one to many.  So the company ID, the primary key of the company table will become the foreign key of the employee  table.  We have also the foreign key annotation.  And this foreign key annotation should be used for the entity that contains the foreign  key.  Inside of it, we will need to define the parent entity, the parent columns, and the child columns.  Another way to handle the relationships between the entities in Ruhm is by using the relation  annotation.  Let's say that our company has many departments.  And I would like to get a list of all the departments from a company using the company  ID.  But I don't like to connect them using a foreign key.  So the solution is to create a pojo.  And to combine the company entity with a list of departments.  So I will embed the company entity.  And the list of departments, I will annotate it with relation.  An important thing to mention here is that the relation annotation should be used only  for a field that is a list or a set.  Okay.  So we define our pojo.  So I would like to use it.  In order to be used, we will create a DAO class that will query separately the fields  in order to get the data that we want.  In this case, all the departments from a company.  Okay.  Let's move on to the queries.  We have four different options.  We have insert, update, delete, and query.  Insert, update, and delete.  Works similar.  I mean, we could use these methods for one object, a list of objects, or a var arcs of  objects.  In my case, all the methods have as a return type the void.  But we could also get a value for each one of them.  In case of the insert method, that value will represent actually the new ID that is inserted  in the database.  And for update and delete, that value will represent actually the number of the rows  affected.  Let's move on to the query.  Query will be used with insert, update, delete, and select.  I already mentioned that Room supports SQL queries.  So we are able to write SQL queries directly in our code.  In this example, I have a classical approach, the first one, but we have also observable  queries.  With live data and flowable.  So Room offers support for live data and edX Java.  What are these observable queries?  These observable queries are used when you want to notify the UI about some changes that  appeared in our database.  So each time something is changed in the database, the UI will be updated accordingly.  We have also the opportunity to use the raw query.  And we don't know from the beginning how we will manage the SQL.  I mean, we will not know from the beginning how the SQL will look.  We will need to create it dynamically.  So this is the best approach for raw query.  And also raw query could be used in combination with live data.  But if we will try to use it in this combination, we need to specify the entity that should  be observed.  Other than that, if we will try to run a database operation on the main thread, we will get  an illegal state exception.  So don't try it.  Transaction.  There are some scenarios when you want to run different operations on the same block.  In this case, we will use the transaction annotation.  In my case, I have an insert of a new department.  And after that, I just want to delete an oldest one.  So I just need to annotate my method with the transaction annotation.  And everything will be executed in the same block.  The transaction annotation could be used also with query.  And there are two types of scenarios in which this could be used.  The first one is when we have a big amount of data.  And we are not sure that this data will fit in the cursor window size, which is about  two mega.  And the second scenario is when we are using a pojo with the relation annotation.  Because in that scenario, room will query separately the fields.  So in order to make sure that we have consistent data, we should use the transaction annotation.  Another interesting thing to mention is related to the threading part.  If we are using room 1.0, and you have two different threads.  On the first thread, you are trying to write something, and on the second one, you are  trying to read something.  In room 1.0, these two operations will not be done in the same time.  So on the first thread, we will need to write.  The second one will wait.  And after the write is done, we could also read the data.  This was available on room 1.0.  In room 1.1, we solved this issue, and at this moment, you could run both operations  at the same time.  So we write and read at the same time.  Migration.  This is a painful topic sometimes.  Room also offers migration support.  So let's see what's happening.  We have our company table, which has this current structure in version 1.  And for the next release, we need to add a ref number inside of it.  So we will need to change the database schema.  What does it mean to offer migration?  It means to keep the data of the user from a version to another one.  So to make the migration of the data from version 1 to version 2 in our case.  If you are using the SQLite approach, we just need to override the onupgrade method, which  has three parameters, the database, the old version and the new version.  This is the classical approach.  But wait, we are trying to use room.  Let's see how we can manage it in room.  In room, we have the class migration, and we need to override the onmigrafe method.  So this is the room approach.  In our case, we will just need to alter the company table by adding a new column ref number.  So this is the migration from one to two.  Do you see also it's added here in the constructor.  In order to apply this migration, we will use addmigrations method inside of the database  builder.  And if we are having many migrations, we will specify them using a comma.  So if we have from one to two to three and so on, you could add them only by using a  comma.  Let's see what's happening behind the scenes.  Behind the scenes, there is a room master table.  And inside of this room master table, we have an identity hash.  This identity hash is changed every time our database schema is changed.  So based on that, the room will know that we will need to offer or not a migration.  So if you go to see how your database looks, by using an explorer, you will find also this  room master table.  And inside of it, you will find the identity hash.  Other than that, we are having the opportunity to export the database schema in our folder  in the project.  By using this annotation processor option, we will export the JSON file that could be  used to see all the versions of our database.  So it's a good thing for the new members of the team.  And also for us, when we forgot what happened in version five, let's say.  And another thing is that this JSON file is used in order to test the migrations.  If we don't provide the migration, we will get also an illegal state exception.  In the case when we are not interested to keep the user's data, there are two approaches  that could be used.  We have the method fall back to destructive migration.  In the case when you just want to keep the data and you just want to get the latest database  schema.  So when you want to clear the data, use fall back to destructive migration.  There is a second scenario when we have many database versions.  Let's say we have seven different database versions.  And I want to offer migration support only for the first four versions.  And for the other ones, I'm not interested to keep the data.  So to solve this feature, we could use fall back to destructive migration from.  And instead of it, we should specify the versions of the database that we will not be kept for  which we will not keep the data of the user.  So what are the main advantages?  We have less boilerplate code.  We have SQL queries that are checked at compile time.  We don't have database operations on the main thread.  The code is maintainable.  And also we have migration support.  If you have questions, these are the resources.  Thank you.  And thank you, Sean.  Yes, please.  One question.  Can you query partial objects?  In your examples, you always have query star and get the full data out of it.  Can you just request company name, for instance?  Yes, exactly.  You can use it and add two points and specify the name of the parameter.  And that parameter will be used in your method.  But I will get back the whole DAO.  It's just not filled.  Or can I specify the simple thing?  You can combine them.  Exactly like in SQLite.  You can add and or inner join everything you want.  Everything that is available in SQLite is available also in room.  And the result object?  Sorry?  How is that defined?  The result object.  I make a query, I select something, I select just company name.  Do I get back a company object?  Or do I get back, can I define just an object containing a string?  Usually in this case, when you have many columns in your table and you just want to get a subset,  you should create a pojo.  Because a room will give you the initial object.  Or if you are interested to get only a subset of the columns, you will create a pojo and  you will get only those columns from your table.  Then I can annotate the members with the column name.  Yeah, it is not necessary to add a column name for annotation.  If you define the field and it has the same value like in the database, it will work.  If not, and if you prefer to have a name in Java or Kotlin and another name in the database,  you should use the column name for annotation.  Okay.  Do I have to set the observe entity for that?  Or?  What do you mean?  Observable query to have the live data for it?  Or what?  Yes, if I have live data.  If you need it to be updated in the UI, if not, it is not necessary.  It depends on what exactly would you like to do with it.  But it will always update when the whole table was updated.  Let's say if I only select the name, I'm only interested in the changes in the name, of  course.  Yeah.  So actually, live data in Flopole works.  They are notified every time when something is changed at that table.  It doesn't know exactly what was changed or if it's related to your object that you want  to get.  It just let you know that something was changed in that table.  Okay.  And then we'll do a whole week query.  Yes.  Okay.  Thank you.  Thank you, too.  Any other questions?  Does room also offer many to many relationships?  It's not by default.  We just need to follow the same approach.  You split the tables.  You have one table, those two tables, and a joint table that will have that primary key  combined from the two primary keys of those two other tables.  So I have to create a table in between by myself.  Exactly.  A joint table.  Thank you.  Okay.  You have this example with the bitmap.  Yes.  It's an attribute.  And you said it to ignore.  Yes.  So you persisted in the database.  But what would you do in this example to read it?  Because then you would get a null bitmap, right?  So we would lose information after persisting it, right?  So the idea was if you annotate a fill with ignore annotation, that fill will not be persisting  in your table.  But at some later case, you want to read it from the database and then the bitmap is  there, so you would need to do something else.  A migration.  If after that you change your mind and you want to save it also.  Anyway, it's not a good idea to save a bitmap in the database.  That's a thing that I think everybody knows.  But let's say you want to save the path of that picture.  Okay?  So in version one, you had only the name and the ID.  And in version two, you want also to have the path.  So in this case, you should provide a migration.  Yeah.  Okay.  Makes sense.  Thank you, too.  Any other questions?  I have a question, actually.  How does Room handle the relationship with API and with the database SQLite?  With the API?  Yes.  So, Room, it's only an ORM for SQLite.  So it speed up the development in terms of SQLite database.  So everything that was available in SQLite, it's also available in Room.  For the API, you need to use whatever you are using, retrofit or what else you are using  right now.  And you get the data from the API and you save it in the database.  So there are two different layers.  This is the offline layer for SQLite and the API layer is the business logic or how you  structure the classes in your project.  Okay, so the API doesn't talk directly to Room.  That would be to the repository and then just to the...  Yes, exactly.  Exactly.  Thank you.  Yes, please.  Since Room manages all the queries internally or the query function itself, is it injection  proof?  I think so.  Yeah.  So, take your company query, set a new company name, name it, whatever.  Yeah, honestly, I didn't try it.  Simicolon drop tables.  I didn't try it, but you could encrypt your data if it's necessary.  I didn't try it.  Thank you.  Any other questions?  Okay, then, thank you very much.
_DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_0_227.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_228_1139.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_1140_1283.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_1284_1403.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_1404_2147.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_2148_2279.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_2280_2435.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_2436_3467.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_3468_3551.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_3552_4007.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_4008_4571.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_4572_4643.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_4644_4799.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_4800_6131.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_6132_7907.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_7908_8003.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_8004_8051.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_8052_8111.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_8112_8231.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_8232_8651.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_8652_8843.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_8844_8891.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_8892_16955.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_16956_18527.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_18528_20711.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_20712_20831.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_20832_29831.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_29832_30431.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_30432_30503.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_30504_30659.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_30660_30923.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_30924_31031.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_31032_31247.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_31248_31307.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_31308_31667.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_31668_32003.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_32004_32399.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_32400_32507.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_32508_33587.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_33588_34103.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_34104_34331.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_34332_34379.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_34380_36983.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_36984_37439.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_37440_37523.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_37524_46679.png _DCBerlin18_209_Miu_Room_An_SQLite_Object_Mapping__grCpunREe0Y_46680_46689.png
Okay. Hello, everybody. Let's play the imagination game. Please try to find out an answer for the next question. If you have the possibility to change something in the Android development, what will it be that thing? Do you have something in mind? Yeah. Maybe the way to handle the lifecycle of the UI components, or maybe the way to update the UI when something is changing your database, or even the way to handle an SQLI database in the Android development? Google addressed the same question to the Android developers, and they got also an interesting answer. One of the developers said that he doesn't want to deal with the technical things. What do you mean by that? What technical things? You are a developer? He specified that he just wanted to deliver the project. His explanation was related to the fact that sometimes we have a lot of tasks to be finished, a lot of issues that should be solved, and you don't have enough time in order to make a detailed research about a specific topic. In that moment, you just want to finish the project and move on. I don't know if this is the best approach, but this was his point of view. If you take a closer look on the way to handle an SQLI database in Android, we will find this documentation page related to the SQLI Open Helper class. We could manage an SQLI class by extending this class, and inside of it, we will need to define a lot of constants for the database version, for the database name, for all the tables that we should define, and also for all the fields that should be created, all the columns of those tables. Other than that, we need to create the table, and after the table was created, we need to insert the data inside of it. And finally, we will get the data. And you write this code, a lot of code, and you just want to run your app and see if it works. And your app will crash because you just forgot to add a comma. So what are the main SQLI challenges? We have a lot of boilerplate code. We have SQL queries checked at runtime. We have database operations on the main thread, and also we have unmaintainable and untestable code. As an answer to these things that should be improved in the Android development, at Google I.O. 2017, Google launched architecture components. These are more a set of guidelines that could help us, the developers, to create better apps. At Google I.O. this year, architecture components are actually part of the Android jetpack. And the developers were happy again. My presentation will focus on ROOM, which is an object relational mapping library. Why an ORM? First of all, because of the productivity. When you are using an ORM, we write less code. We just define the models, and the tool is responsible to generate the code for us. Other than that, we have also an application design as the main advantage of an ORM. When you are using an ORM, the concerns are really well separated, and you are able to also follow some design patterns. Other than that, another reason that should be used in an ORM is the application maintainability. By using a good ORM tool, you care less about the code generated by it. It should be well tested. So we just need to focus on adding new elements in our database schema. Also the developers were the ones who decided the features that should be included in ROOM. They decided to have compile time verification. Because it's important to know that if your query is written correctly or not. So Android Studio will write a compilation error if something is not correctly created. Other than that, we need to have also migration support. Because usually we take care about the user's data, and we want to maintain it across the versions. They didn't want to have a Java query builder API. And the reason behind of this is the fact that usually when you are using a Java query builder API, the code is not so predictable. So you write an inner join between three or three tables. And when you check again your code after two or two weeks, you will not understand from the beginning what's happening there. Other than that, they also like to have SQL completion in Android Studio. So the tool will help us to write the SQLs. And for sure, since we are using a relational database, we need to have relationships between the entities. Are you ready to start? Okay. I hope yes. So first of all, we need to define the dependencies in our Gradle file. At this moment, the current version of ROOM is 1.1.1.1. And if you are using Android extension, the current version is 2.0. An interesting thing to mention here is the fact that ROOM will work only for the minimum SDK 14. So we have defined the dependencies. After that, we need to define the components. We have three types. Entity, data access, object, and the database. Let's start with entity. Let's say we have a company class. Our company class has three fields, the ID of the company, the name, and the picture. And I would like to save in the database only the first two fields. I mean only the ID and the name. So my table should have this structure, where the ID will be the primary key. In order to do that in ROOM, I will just need to annotate my class with the entity annotation. And inside of it, we could also specify the name of the table that should be created in the database. For each field, we could mention a column info annotation in order to define the name of the column for that table. Since our company ID will be the primary key of the table, we will annotate with the primary key annotation. And because we don't want to save the picture in the database, we will annotate to it, ignore. So our table has this structure. And let's say that I would like to also add a date when the company was inserted in my database. So this field is a date type. I will try to use this approach. But do you think it will work in SQLite? No, it will not, because SQLite doesn't support the date type. So we will need to find a way to solve this issue. And the best approach for this case is to use an integer type. So, and this for sure will work. So we will try to translate the date to an integer. And RUM offers us a type converter. So in our case, we will have two methods. One that will translate the time step to a date. And the other one that will translate the date to a time step. This type converter could be applied to a field. And also it could be applied to an entire data access object class or entity. Or it could be available at the entire database level. Let's go to the DAOs, data access objects. Let's say that I would like to get the data from my company table. So in SQLite, I will write something like this, select all from company. Actually RUM understands the SQL queries. So we could use it directly in our Java or Kotlin code. In our case, the DAO will be actually an annotation added to an interface. In this case, the company DAO interface. And inside of it, we have some methods to query, insert, update, and delete the data. Let's say that by mistake, instead of company, I'll write companies. In this case, we will get a compilation error that will let us know that the company's table doesn't exist in our database. Okay, let's take a closer look to the DAO class. So it's an interface. Let's take this example with department DAO. We have an insert all method inside of it. This class is an interface. So since it's an interface, someone should provide the implementation for it. In our case, RUM will provide this implementation. So behind the scenes, RUM will create a class that will implement our interface. Inside of it, we'll find the private final reference to our database. And also the insert all method implemented. Inside of the insert all method, we will get a transaction that is started at the beginning. After that, the data is inserted in the table. We set the transaction successfully, and finally, the transaction is closed. Let's go to the database annotation. Database annotation actually combines the entities and the DAOs. So in our case, we will use the database annotation. And inside of it, we will need to specify the entities that should be saved in our database. So in our case, it's the company entity. If we have many entities, we could specify them using a comma. Another important thing is that we need to specify also the version of the database. After that, we have a class that actually extends the RUM database. It's an abstract class. And inside of it, we will need to have some abstract methods in order to have a reference to our DAOs. For sure, since it's a database, we need to have an instance of it. In this case, I use a single tone in order to get the instance of our database. And inside of this single tone, we have two possibilities. We could use database builder or in-memory database. The difference between these two static methods is the fact that in-memory database will not persist the data. So if we will add some changes, we will insert some new fields or some new data, we will update something, those changes will not be persisted in our database. In this case, I use the database builder method. Other than that, we need to specify also the name of the class which is annotated with the database annotation and the name of the file for this database. If you are taking a closer look, you will see that this approach looks similar with retrofit if you have used it until now. Okay. We defined the dependencies, the main components. Let's go to the relations. SQLite is a relational database. So it understands the relations between the entities. But why I use the quotes for the word relations? I use those quotes because in room, entities cannot contain entities. Since you are using a relational database, SQLite from an object-oriented language, Java or Kotlin, for sure you have read something about object-relational-impendence mismatch. This is a fancy way of saying, yeah, it's difficult getting stuff into and out of the database. And usually the purpose of an ORM is to solve this issue or at least to make it easier, the translation between the entities and the Java or Kotlin classes. Room also tries to solve this issue. But let's see why Google decided not to have entities inside of another entity. Let's go back to our company example. Our company has also a CEO. So I would like to add a reference to this employee with special rights in my entity class. So I would like to have a reference of it. After that, I get the requirement that I should display all the companies in a recycle view. So I have a recycle view with all the company names inside of it. And after that, I get a new requirement that I should display also the name of the director for each company. It looks like a simple task. I will add a new text view. I will set the text company.getdirector.getname. And I'm done with the task. But is it okay to do that? Right now we are trying to run a database operation on the main thread. So even if our query will be very, very fast, the main thread has only 60 milliseconds in order to render the UI and do all the stuff that should be done at that moment. So this is why Google decided not to have entities inside of another entity. Okay. But how will handle the relations? We have three approaches. Let's go back to our example. Our company has two offices. One in San Francisco and another one here in Berlin. The one from here is the headquarter. So I would like to save these two locations in my company table. So I will have a class location with two fields, latitude and longitude. And I would like to insert this data in my table. So in order to do that, Ruhm will help us by having nested objects. And we could achieve this thing by using the embedded annotation. So I will embed these two locations. At this moment, Ruhm will try to create two columns with the same name because Ruhm will go back and see that location has latitude and longitude. And in this case, for each office, we'll try to create two columns for latitude and two columns for longitude. But this thing is not possible in SQLite, so we need to fix this issue. The solution is to use the prefix, and we will annotate the headquarter location with the prefix HQ. So in this case, we will be able to save all these two locations in my table. So right now, this is the current structure of our table. Okay. Our company has also employees. So it looks like a classical approach for a foreign key. Ruhm also offers support. So we will do this classical thing. We have a relation one to many. So the company ID, the primary key of the company table will become the foreign key of the employee table. We have also the foreign key annotation. And this foreign key annotation should be used for the entity that contains the foreign key. Inside of it, we will need to define the parent entity, the parent columns, and the child columns. Another way to handle the relationships between the entities in Ruhm is by using the relation annotation. Let's say that our company has many departments. And I would like to get a list of all the departments from a company using the company ID. But I don't like to connect them using a foreign key. So the solution is to create a pojo. And to combine the company entity with a list of departments. So I will embed the company entity. And the list of departments, I will annotate it with relation. An important thing to mention here is that the relation annotation should be used only for a field that is a list or a set. Okay. So we define our pojo. So I would like to use it. In order to be used, we will create a DAO class that will query separately the fields in order to get the data that we want. In this case, all the departments from a company. Okay. Let's move on to the queries. We have four different options. We have insert, update, delete, and query. Insert, update, and delete. Works similar. I mean, we could use these methods for one object, a list of objects, or a var arcs of objects. In my case, all the methods have as a return type the void. But we could also get a value for each one of them. In case of the insert method, that value will represent actually the new ID that is inserted in the database. And for update and delete, that value will represent actually the number of the rows affected. Let's move on to the query. Query will be used with insert, update, delete, and select. I already mentioned that Room supports SQL queries. So we are able to write SQL queries directly in our code. In this example, I have a classical approach, the first one, but we have also observable queries. With live data and flowable. So Room offers support for live data and edX Java. What are these observable queries? These observable queries are used when you want to notify the UI about some changes that appeared in our database. So each time something is changed in the database, the UI will be updated accordingly. We have also the opportunity to use the raw query. And we don't know from the beginning how we will manage the SQL. I mean, we will not know from the beginning how the SQL will look. We will need to create it dynamically. So this is the best approach for raw query. And also raw query could be used in combination with live data. But if we will try to use it in this combination, we need to specify the entity that should be observed. Other than that, if we will try to run a database operation on the main thread, we will get an illegal state exception. So don't try it. Transaction. There are some scenarios when you want to run different operations on the same block. In this case, we will use the transaction annotation. In my case, I have an insert of a new department. And after that, I just want to delete an oldest one. So I just need to annotate my method with the transaction annotation. And everything will be executed in the same block. The transaction annotation could be used also with query. And there are two types of scenarios in which this could be used. The first one is when we have a big amount of data. And we are not sure that this data will fit in the cursor window size, which is about two mega. And the second scenario is when we are using a pojo with the relation annotation. Because in that scenario, room will query separately the fields. So in order to make sure that we have consistent data, we should use the transaction annotation. Another interesting thing to mention is related to the threading part. If we are using room 1.0, and you have two different threads. On the first thread, you are trying to write something, and on the second one, you are trying to read something. In room 1.0, these two operations will not be done in the same time. So on the first thread, we will need to write. The second one will wait. And after the write is done, we could also read the data. This was available on room 1.0. In room 1.1, we solved this issue, and at this moment, you could run both operations at the same time. So we write and read at the same time. Migration. This is a painful topic sometimes. Room also offers migration support. So let's see what's happening. We have our company table, which has this current structure in version 1. And for the next release, we need to add a ref number inside of it. So we will need to change the database schema. What does it mean to offer migration? It means to keep the data of the user from a version to another one. So to make the migration of the data from version 1 to version 2 in our case. If you are using the SQLite approach, we just need to override the onupgrade method, which has three parameters, the database, the old version and the new version. This is the classical approach. But wait, we are trying to use room. Let's see how we can manage it in room. In room, we have the class migration, and we need to override the onmigrafe method. So this is the room approach. In our case, we will just need to alter the company table by adding a new column ref number. So this is the migration from one to two. Do you see also it's added here in the constructor. In order to apply this migration, we will use addmigrations method inside of the database builder. And if we are having many migrations, we will specify them using a comma. So if we have from one to two to three and so on, you could add them only by using a comma. Let's see what's happening behind the scenes. Behind the scenes, there is a room master table. And inside of this room master table, we have an identity hash. This identity hash is changed every time our database schema is changed. So based on that, the room will know that we will need to offer or not a migration. So if you go to see how your database looks, by using an explorer, you will find also this room master table. And inside of it, you will find the identity hash. Other than that, we are having the opportunity to export the database schema in our folder in the project. By using this annotation processor option, we will export the JSON file that could be used to see all the versions of our database. So it's a good thing for the new members of the team. And also for us, when we forgot what happened in version five, let's say. And another thing is that this JSON file is used in order to test the migrations. If we don't provide the migration, we will get also an illegal state exception. In the case when we are not interested to keep the user's data, there are two approaches that could be used. We have the method fall back to destructive migration. In the case when you just want to keep the data and you just want to get the latest database schema. So when you want to clear the data, use fall back to destructive migration. There is a second scenario when we have many database versions. Let's say we have seven different database versions. And I want to offer migration support only for the first four versions. And for the other ones, I'm not interested to keep the data. So to solve this feature, we could use fall back to destructive migration from. And instead of it, we should specify the versions of the database that we will not be kept for which we will not keep the data of the user. So what are the main advantages? We have less boilerplate code. We have SQL queries that are checked at compile time. We don't have database operations on the main thread. The code is maintainable. And also we have migration support. If you have questions, these are the resources. Thank you. And thank you, Sean. Yes, please. One question. Can you query partial objects? In your examples, you always have query star and get the full data out of it. Can you just request company name, for instance? Yes, exactly. You can use it and add two points and specify the name of the parameter. And that parameter will be used in your method. But I will get back the whole DAO. It's just not filled. Or can I specify the simple thing? You can combine them. Exactly like in SQLite. You can add and or inner join everything you want. Everything that is available in SQLite is available also in room. And the result object? Sorry? How is that defined? The result object. I make a query, I select something, I select just company name. Do I get back a company object? Or do I get back, can I define just an object containing a string? Usually in this case, when you have many columns in your table and you just want to get a subset, you should create a pojo. Because a room will give you the initial object. Or if you are interested to get only a subset of the columns, you will create a pojo and you will get only those columns from your table. Then I can annotate the members with the column name. Yeah, it is not necessary to add a column name for annotation. If you define the field and it has the same value like in the database, it will work. If not, and if you prefer to have a name in Java or Kotlin and another name in the database, you should use the column name for annotation. Okay. Do I have to set the observe entity for that? Or? What do you mean? Observable query to have the live data for it? Or what? Yes, if I have live data. If you need it to be updated in the UI, if not, it is not necessary. It depends on what exactly would you like to do with it. But it will always update when the whole table was updated. Let's say if I only select the name, I'm only interested in the changes in the name, of course. Yeah. So actually, live data in Flopole works. They are notified every time when something is changed at that table. It doesn't know exactly what was changed or if it's related to your object that you want to get. It just let you know that something was changed in that table. Okay. And then we'll do a whole week query. Yes. Okay. Thank you. Thank you, too. Any other questions? Does room also offer many to many relationships? It's not by default. We just need to follow the same approach. You split the tables. You have one table, those two tables, and a joint table that will have that primary key combined from the two primary keys of those two other tables. So I have to create a table in between by myself. Exactly. A joint table. Thank you. Okay. You have this example with the bitmap. Yes. It's an attribute. And you said it to ignore. Yes. So you persisted in the database. But what would you do in this example to read it? Because then you would get a null bitmap, right? So we would lose information after persisting it, right? So the idea was if you annotate a fill with ignore annotation, that fill will not be persisting in your table. But at some later case, you want to read it from the database and then the bitmap is there, so you would need to do something else. A migration. If after that you change your mind and you want to save it also. Anyway, it's not a good idea to save a bitmap in the database. That's a thing that I think everybody knows. But let's say you want to save the path of that picture. Okay? So in version one, you had only the name and the ID. And in version two, you want also to have the path. So in this case, you should provide a migration. Yeah. Okay. Makes sense. Thank you, too. Any other questions? I have a question, actually. How does Room handle the relationship with API and with the database SQLite? With the API? Yes. So, Room, it's only an ORM for SQLite. So it speed up the development in terms of SQLite database. So everything that was available in SQLite, it's also available in Room. For the API, you need to use whatever you are using, retrofit or what else you are using right now. And you get the data from the API and you save it in the database. So there are two different layers. This is the offline layer for SQLite and the API layer is the business logic or how you structure the classes in your project. Okay, so the API doesn't talk directly to Room. That would be to the repository and then just to the... Yes, exactly. Exactly. Thank you. Yes, please. Since Room manages all the queries internally or the query function itself, is it injection proof? I think so. Yeah. So, take your company query, set a new company name, name it, whatever. Yeah, honestly, I didn't try it. Simicolon drop tables. I didn't try it, but you could encrypt your data if it's necessary. I didn't try it. Thank you. Any other questions? Okay, then, thank you very much.
an SQLI database in the Android development? handle an SQLI database in Android, we will find have a lot of boilerplate code. We have SQL queries checked at runtime. We have database Android development, at Google I.O. 2017, Google launched architecture components. These are more a set of guidelines that could help us, the developers, to create better apps. At Google I.O. decided to have compile time verification. SQL queries. So we could use it directly in our database. So it understands the relations between are using a relational database, SQLite from an between the entities and the Java or Kotlin Java. What are these observable queries? These try to run a database operation on the main migration support. So let's see what's happening. a comma. Let's see what's happening behind the database operations on the main thread. The code