Where in room android

Урок 8. Room. Query

В этом уроке поговорим подробнее про Query. В каком виде мы можем получать данные: List, массив, Cursor, LiveData. Как передавать параметры. Как получать только некоторые поля. Как с помощью Query выполнять update и delete запросы в Room.

Полный список уроков курса:

В качестве примера будем работать с таким Entity классом:

List, массив, Cursor

Чтобы запросить из базы Employee-объекты, необходимо в Dao создать метод с аннотацией Query

В Query прописываем запрос, который должен вернуть данные. А в качестве возвращаемого типа указываем List .

При вызове этого метода, Room сделает запрос в таблицу employee, конвертирует полученные данные в Employee объекты и упакует их в List.

Запрос, который вы указываете в Query проверяется на правильность синтаксиса во время компиляции. Если в нем будет ошибка, система вам сразу подскажет это.

Вместо List, мы также можем использовать массив:

и даже Cursor, если это необходимо по каким-то причинам:

LiveData

Room умеет возвращать данные в LiveData обертке.

Получение данных в коде Activity выглядит так:

Получаем LiveData и подписываемся на него.

Использование LiveData имеет огромное преимущество перед использование списка или массива. Подписавшись на LiveData, вы будете получать свежие данные при их изменении в базе. Т.е. при добавлении новых, удалении старых или обновлении текущих данных в таблице employee, Room снова выполнит ваш Query запрос, и вы получите в onChanged методе актуальные данные с учетом последних изменений. Вам больше не надо самим запрашивать эти данные каждый раз. И все это будет приходить вам в UI поток.

Передача параметров

В Query можно передавать параметры, чтобы сделать запросы более конкретными.

Например, запрос данных по id

Перед параметром employeeId в запросе должно стоять двоеточие. Room возьмет значение этого параметра из метода и подставит его в запрос.

Рассмотрим еще несколько примеров:

Поиск сотрудников с зарплатой больше заданного значения

Поиск сотрудников с зарплатой в заданном диапазоне

Поиск сотрудников по имени или фамилии

Поиск сотрудников по списку id.

Subsets

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

Допустим нам надо получать только имя и фамилию сотрудника. Если сделать так:

то уже при компиляции получим ошибку: The columns returned by the query does not have the fields [id,salary] in Employee even though they are annotated as non-null or primitive. Columns returned by the query: [first_name,last_name].

Room сообщает, что в данных, которые вернет этот запрос, не хватает полей, чтобы заполнить все поля объекта Employee.

В этом случае мы можем использовать отдельный объект.

Обратите внимание, что он не Entity. Это обычный класс. С помощью ColumnInfo мы настраиваем имена полей, чтобы они совпадали с полями таблицы.

Используем этот класс в методе запроса:

Теперь все ок, и мы получим список Name объектов.

Вы также можете в этих не Entity классах использовать вложенные классы с аннотацией @Embedded. Подробно об этой аннотации мы говорили в Уроке 6.

insert, update и delete запросы

Аннотации Insert, Update и Delete позволяют нам модифицировать данные, но их возможности слишком ограниченны. Часто возникает необходимость обновить только некоторые поля или удалить записи по определенному условию. Это можно сделать запросами с помощью Query.

Давайте рассмотрим пару примеров.

Обновление зарплат у сотрудников по списку id.

Опционально метод может возвращать int значение, в котором мы получим количество обновленных строк. Если вам это не нужно, то делайте метод void.

Вызов метода будет выглядеть так:

Удаление сотрудников по списку id

Запросы удаления также могут возвращать int значение, в котором мы получим количество удаленных строк.

Присоединяйтесь к нам в Telegram:

— в канале StartAndroid публикуются ссылки на новые статьи с сайта startandroid.ru и интересные материалы с хабра, medium.com и т.п.

— в чатах решаем возникающие вопросы и проблемы по различным темам: Android, Kotlin, RxJava, Dagger, Тестирование

— ну и если просто хочется поговорить с коллегами по разработке, то есть чат Флудильня

— новый чат Performance для обсуждения проблем производительности и для ваших пожеланий по содержанию курса по этой теме

Источник

How to map Custom SQL queries to Data Objects in Android Room Database

If you’re building an application that has a considerable amount of local database usage with a lot of tables, you will come across this problem at least once where you need to write custom queries

Jul 24, 2020 · 4 min read

R oom Database is an excellent solution by Google which makes our life a lot easier using the Sqlite Database on Android Devices. It uses the approach of Spring style JPA objects called Entities and it does it very well. At the ground level,each entity object = 1 table in the database. It can even handle joins and nested objects very well using @Relations and @Embedded annotations.

But what about custom queries and custom objects which don’t necessarily point to a single table? Let’s find out how.

Enter @RawQuery

The official docs say,

RawQuery serves as an escape hatch where you can build your own SQL query at runtime but still use Room to convert it into objects.

It precise l y means what it says. You write custom queries and you can map it to your custom object. Let’s see how.

Consider this Entity Object

You’ll probably guess, this table will store events. So naturally, it’ll have a lot of rows. Each event, i.e., row, carries a point value also.

Let’s say, we need to fetch a summary of these events with the points added up. The best use-case would be to show it to the user a summary of their points on the app.

Let’s write a query for that.

Query Formation

If you notice, I have used alias ( as )in the query. We’ll see why shortly.

So, this query returns 2 columns, one is EventType and the other the Sum total of points GROUP BY EventType

Obviously, you can see, this is a custom result from an existing table events . You don’t have an Entity object for this and we don’t need one. Let’s create a pojo (poko, in this case 😄) to hold our data.

We have defined a data class i.e., a simple data class to hold our data.

How it all fits

Now let’s integrate the query with Room.

We’ll make use of @RawQuery as mentioned before. The only caveat is, the RawQuery method accepts SupportSqliteQuery parameter only.

So your method will look like this

@RawQuery doesn’t accept any query parameter. So how do we write our query? We’ll have to create another method which will create a SimpleSqliteQuery object and pass our query string to it.

Dao Method

Either you create this in your Repository or maybe in the DAO itself, it’s up to you. So what is this method doing?

Very simple, we are passing our query string to the SimpleSQLiteQuery object and that is being passed as a parameter to the original method calculatePointsSummaryInternal .

And that’s it! Hit build and let Room do it’s magic.

Once the build is over, you’ll Room has successfully generated the code for the method calculatePointsSummaryInternal . Let’s inspect it, shall we?

Sometimes, it’s good to inspect the generated code by these tools and figure out what’s happening under the hood, whether it generated what you want or not.

Well, that’s a lot of code! But remember the days, when we had to write this by hand ourselves.

Pay close attention to the bold part. It’s trying to fetch the columns eventType and totalPoints from the result set. Those are exactly the names of the variables in our POJO. So how did it figure that out?

Remember the alias in the query?

It is very important to alias your custom queries if you want to map it to your custom object. The reason being, in the above query, the result set will have a column called sum(points) which cannot be a POJO member variable obviously.

It’s a good practice to alias your results to derive a better meaning from your queries.

So that’s it!. Access your function the normal way

Источник

7 Pro-tips for Room

Room is an abstraction layer on top of SQLite that makes it easier and nicer to persist data. If you’re new to Room then check out this primer:

7 Steps To Room

A step by step guide on how to migrate your app to Room

In this article, I’d like to share some pro-tips on getting the most out of Room:

1. Pre-populate the database

Do you need to add default data to your database, right after it was created or when the database is opened? Use RoomDatabase#Callback ! Call the addCallback method when building your RoomDatabase and override either onCreate or onOpen .

onCreate will be called when the database is created for the first time, after the tables have been created. onOpen is called when the database was opened. Since the DAOs can only be accessed once these methods return, we‘re creating a new thread where we’re getting a reference to the database, get the DAO, and then insert the data.

See a full example here.

Note: When using the ioThread approach, if your app crashes at the first launch, in between database creation and insert, the data will never be inserted.

2. Use DAO’s inheritance capability

Do you have multiple tables in your database and find yourself copying the same Insert , Update and Delete methods? DAOs support inheritance, so create a BaseDao class, and define your generic @Insert , @Update and @Delete methods there. Have each DAO extend the BaseDao and add methods specific to each of them.

See more details here.

The DAOs have to be interfaces or abstract classes because Room generates their implementation at compile time, including the methods from BaseDao .

3. Execute queries in transactions with minimal boilerplate code

Annotating a method with @Transaction makes sure that all database operations you’re executing in that method will be run inside one transaction. The transaction will fail when an exception is thrown in the method body.

You might want to use the @Transaction annotation for @Query methods that have a select statement, in the following cases:

• When the result of the query is fairly big. By querying the database in one transaction, you ensure that if the query result doesn’t fit in a single cursor window, it doesn’t get corrupted due to changes in the database between cursor window swaps.
• When the result of the query is a POJO with @Relation fields. The fields are queries separately so running them in a single transaction will guarantee consistent results between queries.

@Delete , @Update and @Insert methods that have multiple parameters are automatically run inside a transaction.

4. Read only what you need

When you’re querying the database, do you use all the fields you return in your query? Take care of the amount of memory used by your app and load only the subset of fields you will end up using. This will also improve the speed of your queries by reducing the IO cost. Room will do the mapping between the columns and the object for you.

Consider this complex User object:

On some screens we don’t need to display all of this information. So instead, we can create a UserMinimal object that holds only the data needed.

In the DAO class, we define the query and select the right columns from the users table.

5. Enforce constraints between entities with foreign keys

Even though Room doesn’t directly support relations, it allows you to define Foreign Key constraints between entities.

Room has the @ForeignKey annotation, part of the @Entity annotation, to allow using the SQLite foreign key features. It enforces constraints across tables that ensure the relationship is valid when you modify the database. On an entity, define the parent entity to reference, the columns in it and the columns in the current entity.

Consider a User and a Pet class. The Pet has an owner, which is a user id referenced as foreign key.

Optionally, you can define what action to be taken when the parent entity is deleted or updated in the database. You can choose one of the following: NO_ACTION , RESTRICT , SET_NULL , SET_DEFAULT or CASCADE , that have same behaviors as in SQLite.

Note: In Room, SET_DEFAULT works as SET_NULL , as Room does not yet allow setting default values for columns.

6. Simplify one-to-many queries via @Relation

In the previous User — Pet example, we can say that we have a one-to-many relation: a user can have multiple pets. Let’s say that we want to get a list of users with their pets: List .

To do this manually, we would need to implement 2 queries: one to get the list of all users and another one to get the list of pets based on a user id.

We would then iterate through the list of users and query the Pets table.

To make this simpler, Room’s @Relation annotation automatically fetches related entities. @Relation can only be applied to a List or Set of objects. The UserAndAllPets class has to be updated:

In the DAO, we define a single query and Room will query both the Users and the Pets tables and handle the object mapping.

7. Avoid false positive notifications for observable queries

Let’s say that you want to get a user based on the user id in an observable query:

You’ll get a new emission of the User object whenever that user updates. But you will also get the same object when other changes (deletes, updates or inserts) occur on the Users table that have nothing to do with the User you’re interested in, resulting in false positive notifications. Even more, if your query involves multiple tables, you’ll get a new emission whenever something changed in any of them.

Here’s what’s going on behind the scenes:

1. SQLite supports triggers that fire whenever a DELETE , UPDATE or INSERT happens in a table.
2. Room creates an InvalidationTracker that uses Observers that track whenever something has changed in the observed tables.
3. Both LiveData and Flowable queries rely on the )» target=»_blank» rel=»noopener ugc nofollow»>InvalidationTracker.Observer#onInvalidated notification. When this is received, it triggers a re-query.

Room only knows that the table has been modified but doesn’t know why and what has changed. Therefore, after the re-query, the result of the query is emitted by the LiveData or Flowable . Since Room doesn’t hold any data in memory and can’t assume that objects have equals() , it can’t tell whether this is the same data or not.

You need to make sure that your DAO filters emissions and only reacts to distinct objects.

If the observable query is implemented using Flowables , use Flowable#distinctUntilChanged .

If your query returns a LiveData , you can use a MediatorLiveData that only allows distinct object emissions from a source.

In your DAOs, make method that returns the distinct LiveData public and the method that queries the database protected .

See more of the code here.

Note: if you’re returning a list to be displayed, consider using the Paging Library and returning a LivePagedListBuilder since the library will help with automatically computing the diff between list items and updating your UI.

New to Room? Check out our previous articles:

Источник