Android room group by

Урок 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

Источник

Save data in a local database using Room Part of Android Jetpack.

Apps that handle non-trivial amounts of structured data can benefit greatly from persisting that data locally. The most common use case is to cache relevant pieces of data so that when the device cannot access the network, the user can still browse that content while they are offline.

The Room persistence library provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite. In particular, Room provides the following benefits:

• Compile-time verification of SQL queries.
• Convenience annotations that minimize repetitive and error-prone boilerplate code.
• Streamlined database migration paths.

Because of these considerations, we highly recommend that you use Room instead of using the SQLite APIs directly.

Setup

To use Room in your app, add the following dependencies to your app’s build.gradle file:

Groovy

Kotlin

Primary components

There are three major components in Room:

• The database class that holds the database and serves as the main access point for the underlying connection to your app’s persisted data.
• Data entities that represent tables in your app’s database.
• Data access objects (DAOs) that provide methods that your app can use to query, update, insert, and delete data in the database.

The database class provides your app with instances of the DAOs associated with that database. In turn, the app can use the DAOs to retrieve data from the database as instances of the associated data entity objects. The app can also use the defined data entities to update rows from the corresponding tables, or to create new rows for insertion. Figure 1 illustrates the relationship between the different components of Room.

Figure 1. Diagram of Room library architecture.

Sample implementation

This section presents an example implementation of a Room database with a single data entity and a single DAO.

Data entity

The following code defines a User data entity. Each instance of User represents a row in a user table in the app’s database.

Kotlin

To learn more about data entities in Room, see Defining data using Room entities.

Data access object (DAO)

The following code defines a DAO called UserDao . UserDao provides the methods that the rest of the app uses to interact with data in the user table.

Kotlin

Database

The following code defines an AppDatabase class to hold the database. AppDatabase defines the database configuration and serves as the app’s main access point to the persisted data. The database class must satisfy the following conditions:

• The class must be annotated with a @Database annotation that includes an entities array that lists all of the data entities associated with the database.
• The class must be an abstract class that extends RoomDatabase .
• For each DAO class that is associated with the database, the database class must define an abstract method that has zero arguments and returns an instance of the DAO class.

Kotlin

Note: If your app runs in a single process, you should follow the singleton design pattern when instantiating an AppDatabase object. Each RoomDatabase instance is fairly expensive, and you rarely need access to multiple instances within a single process.

If your app runs in multiple processes, include enableMultiInstanceInvalidation() in your database builder invocation. That way, when you have an instance of AppDatabase in each process, you can invalidate the shared database file in one process, and this invalidation automatically propagates to the instances of AppDatabase within other processes.

Usage

After you have defined the data entity, the DAO, and the database object, you can use the following code to create an instance of the database:

Kotlin

You can then use the abstract methods from the AppDatabase to get an instance of the DAO. In turn, you can use the methods from the DAO instance to interact with the database:

Kotlin

Additional resources

To learn more about Room, see the following additional resources:

Sample

• Android Sunflower, a gardening app that illustrates Android development best practices with Android Jetpack.
• Tivi, a TV show tracking app that uses the latest libraries and tools.

Codelabs

Blogs

Content and code samples on this page are subject to the licenses described in the Content License. Java is a registered trademark of Oracle and/or its affiliates.

Источник

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:

Источник