Complex data queries in Android databases

Development of a complex application is impossible without the use of databases that provide powerful functionality for storing, sorting and retrieving information. Their application in Android development has its own specifics caused by the features of mobile devices: less hardware resources, battery saving, mobile application architecture. Therefore, the use of databases in Android development is a separate topic that requires close study.

AAs of 2020, SQLite, Realm and ObjectBox remain the most popular database management systems (DBMS) for Android applications. Each of them occupies its own niche in the market of mobile applications, offering developers various approaches to the storage and use of structured data.

SQLite was first introduced in 2000 and has since become one of the most popular DBMSs. It is based on a relational approach to data storage and uses the SQL query language to manage information. Unlike other well-known DBMSs, such as MySQL and PostgreSQL, SQLite does not require a separate database server, stores all information in one file and takes up little disk space. These features make it the most optimal among relational DBMS for use in mobile devices. At the moment, SQLite is the most frequently used DBMS in the development of Android applications. Pure SQLite is currently rarely used in the development of Android applications. To simplify the work, the ORM Room from Google is widely used, which avoids writing boilerplate code. Data tables are created using annotations in the model class, which determine the transformation of class attributes into names and properties of table columns.

An alternative to SQLite is Realm. Its first stable version for Android appeared in 2014. The creators conceived Realm as a database that will avoid writing cumbersome boilerplate code in SQL, will make it possible to work with data as objects, and increase the speed of CRUD operations. In 2019, Realm was acquired by MongoDB Inc. and added the serverless platform option to the original functionality. Realm belongs to the noSQL DBMS group, using model classes to describe the data structure, not tables and the relationships between them. This removes the problem of object-relational mapping, relevant for relational databases and reduces the cost of resources for data conversion.

ObjectBox – the newest of the considered DBMS. It was created by Green Robot, a well-known Android developer for its GreenDao, EventBus, and Essentials products. ObjectBox was originally developed as a DBMS for mobile and IoT devices; therefore, it compares favorably with its competitors in the speed of operation and the convenience of integration into mobile applications. Like Realm, it implements a noSQL approach in which the attributes of model classes and the relationships between them are directly written to the database.

Using the database in your Android application in most cases means that you have to deal with structured and interconnected information. Simple storage of primitive data is straightforward. However, more complex manipulations with information can be a real challenge for a developer, requiring good knowledge of the tools of his chosen DBMS. Let’s try to consider how the most popular mobile databases – SQLite, Realm and ObjectBox – solve complex problems:

  1. storing complex objects
  2. receiving data with many conditions
  3. getting related entities (one-to-one, one-to-many, many-to-many).

As an example, let’s take an application for a library that will work with data about books. Each book has one or more authors, title, place and year of publication, publishing house, current status (issued on hand, available, for restoration), etc. Consider what options for organizing data can be used in the case of using each of the above DBMS.

Storing complex objects

SQLite and Room

One of the features of relational databases is the support of strictly defined data types that can be assigned to table columns. In the case of SQLite, these are INTEGER, REAL, TEXT, BLOB, and NULL. To save objects, you need to convert them into a set of attributes of the corresponding types. Room allows you to do this in several ways:

  1. Adding @Entity annotation to a class that describes a data model. In this case, Room will create a separate table in the SQLite database and save your objects as rows of this table. Using annotations, you can specify column names, necessary fields for saving, data properties, etc.
  2. Using TypeConverters, which describe the logic for converting an object into a simple data type, suitable for storage in 1 cell. A classic example is that the Date type can be saved to the database as a timestamp.

    Convert to primitive values can be more complex objects, such as arrays and lists.

  3. Using the @Embedded annotation. When using it, Room will automatically create additional fields for the attributes of the nested object in the parent table. With clear-cut simplicity, the main limitation of this approach is obvious – it is only suitable for saving nested objects.

Realm

Since Realm was originally developed as an object-oriented database, the conversion of application models to save them in the database requires minimal effort from the developer. First of all, the model class must be accessible for inheritance (in Java, nothing needs to be changed, in Kotlin – add the open modifier). Also, this class should be the descendant of the abstract class RealmObject, which encapsulates the logic of interaction with the database.

Saving nested objects is also not a problem. The requirements for nested objects are the same as for the model class.

ObjectBox

The principle of operation of the ObjectBox is similar to Realm, therefore it also requires minimal manipulation of the model classes to save them to the database: adding @Entity and @Id annotations, public attributes or their getters and setters.

Saving links to other objects requires specifying the type of connection – ToOne, if there is only one associated object, or ToMany if there are several. In our case, we can supplement our Book class with a link to the authors.

Getting data with many conditions

SQLite and Room

They provide powerful functionality for generating complex queries based on SQL expressions. To use it, you must have a good knowledge of SQL, as well as the features of its implementation in SQLite.

For example, a query looks like this, returning all books after a given year, sorted alphabetically.

A query that returns all books with a specific title published between specified years.

A query that returns a list of publishers recorded in a database for a specific city.

Realm

This DBMS provides a rich set of operators for fetching and filtering data. Using their combinations, you can create a query of any complexity. Let’s rewrite the SQLite examples for Realm.

ObjectBox

In this DBMS, QueryBuilder is used to create queries, which has an extensive arsenal of functions for creating the desired data selection. The principle of creating queries is very similar to Realm. The main difference is that in Realm the query returns data wrapped in the RealmResults class. Its main task is to keep links to data up to date and provide functionality for accessing them. To get the data in raw form, we must copy it from the database using the copyFromRealm () method. ObjectBox immediately returns data when requested. Its second feature is the ability to cache and reuse requests. Examples already familiar to us when using an ObjectBox will look like this.

Getting related entities (one-to-one, one-to-many, many-to-many)

SQLite and Room

Despite the fact that the implementation of relationships between entities is a strong point of relational databases, their implementation in SQLite and Room requires a significant amount of code and developer efforts. The reason is the ban on the use of object references. As the creators of Room explain, in the Android application, accessing nested objects leads to delayed loading from the main thread with all the ensuing consequences: delay in rendering the UI or excessive consumption of resources. Therefore, the most obvious way to implement relationships between entities is not supported.

To implement relationships in Room, you need to create an additional class that will be returned when requesting the corresponding data.

An example of a one-to-one and one-to-many relationship. Let’s create a Meta class that contains service information about each book: code and status. Each book can have only one meta data and each meta data refers to only one book.

Books with metadata are received in the form of several requests, therefore, they require @Transaction annotation.

The one-to-many relationship in Room is implemented identically, but instead of a reference to a single object, a list link is indicated.

An example of a many-to-many relationship. Let’s create a version of the Author class, already familiar to us from the example from Realm.

To implement the relationship, you need to create three additional classes: a link and two query results.

Requests for data will be as follows.

Realm

The difference in getting related data between Room and Realm is enormous. Getting happens the same way as with a simple access to the property of the object. An important point – when implementing a many-to-many communication, the connection between objects is unidirectional by default. For two-way dependency between objects, you must use the @LinkingObjects annotation.

ObjectBox

Getting related entities is very similar to Realm and happens in one line.

One-to-one example

Many-to-many example

Conclusion

Criterion
Type Relational with ORM Object oriented Object oriented
Entry treshold Low Middle Low
Storing complex objects It is convenient when working with simple types, it requires additional time to implement relationships between objects. Storing complex objects requires minimal effort. Storing complex objects requires minimal effort.
Getting data with many conditions Great toolkit for the formation of complex multi-level queries.You need to know SQL. A large set of built-in functions for selecting and sorting data. A large set of built-in functions for selecting and sorting data.
Retrieving related entities Requires a significant investment a implementation time. Occurs in a few lines. Occurs in one lines.
Type Relational with ORM
Entry treshold Low
Storing complex objects It is convenient when working with simple types, it requires additional time to implement relationships between objects.
Getting data with many conditions Great toolkit for the formation of complex multi-level queries.You need to know SQL.
Retrieving related entities Requires a significant investment a implementation time.
Type Object oriented
Entry treshold Middle
Storing complex objects Storing complex objects requires minimal effort.
Getting data with many conditions A large set of built-in functions for selecting and sorting data.
Retrieving related entities Occurs in a few lines.
Type Object oriented
Entry treshold Low
Storing complex objects Storing complex objects requires minimal effort.
Getting data with many conditions A large set of built-in functions for selecting and sorting data.
Retrieving related entities Occurs in one lines.

Thus, the complex operations of storing and retrieving data in the most popular databases for Android applications have their own specifics. The combination of SQLite and Room, despite the status of the most popular solution for storing structured data, requires significant manipulations to store and retrieve related objects, as well as good knowledge of the SQL basics for efficient work. Realm and ObjectBox, based on an object-oriented approach to data organization, are significantly superior to their competitors in both speed and usability.

See how we came up with a cross-platform database solution using MS SQL

Please enter your business email

Section image