Work with SQLite database in Android with the help of Room library
Introduction
Room is a library recommended by Google as one of the components of the so-called. “Android Architecture Components” approach. Room is a wrapper around an SQLite database and is an abstraction layer that makes it easy to work with a database. The room library takes on most of the responsibilities so we can now create tables and manage data more easily.
Room has a so-called. compile-time checks ie. code control at compile time and if there is an error it will be shown at compile time. Room thus saves us from annoying small errors that occur when working with SQL database (lack of semicolons or spaces…) that cause RunTimeException.
To work with this library it is necessary to define dependencies:
def room_version = “2.2.5”
implementation “androidx.room:room-runtime:$room_version”
annotationProcessor “androidx.room:room-compiler:$room_version”
The room library consists of three main components:
Entity (table)
Entity is a class that represents a database table and is denoted by @Entity. In the continuation of this annotation (within brackets) we can define the name of the table which can be different from the name of the class. If we do not define a table name this way, Room will generate a table with the same name as the class name.
@Entity(tableName = “buy_item_table”)
public class BuyItem {
}
Every entity must have a constructor! The constructor is usually defined so that its parameters match the fields (based on type and name). However, the constructor does not have to accept all fields as parameters, the field that we do not want to put in the constructor must be marked with the @Ignore annotation, otherwise, the Android studio will display an error. Also within the constructor, we do not have to put a field that is autogenerated (ie autoGenerate = true), but for that field, there must be a public setter (we will not use it but it is necessary for the Room library).
@PrimaryKey
In working with the Room Library, one column must have a defined so-called PrimaryKey. Marking the column that will represent the “Primary key” is done by adding the @PrimaryKey annotation to that field. When we define that a field is PrimaryKey then we can use its autoGenerate () method or more simply just in parentheses to add (autoGenerate = true).
Example
In this example, the first mId field is generated automatically, so we will not insert it into the constructor, but it is still necessary to provide its setter because it needs a Room library (for other fields it is not necessary setters).
@Entity(tableName = “buy_item_table”)
public class BuyItem {
@PrimaryKey(autoGenerate = true)
private int mId;
private String mName;
private String mAmount;
private String mTimestamp;
public BuyItem(String mName, String mAmount, String mTimestamp) {
this.mName = mName;
this.mAmount = mAmount;
this.mTimestamp = mTimestamp;
}
// Setter only for id field because it is not given as a parameter in the constructor
public void setId(int id){
mId = id;
}
// Geters:
public int getId(){ return mId; }
public String getName() { return mName; }
public String getAmount() { return mAmount; }
public String getTimestamp() { return mTimestamp; }
}
REMARK:
The constructor can possibly be without any arguments, but then there must be defined setters for all fields.
@ColumnInfo
Room by default generates a table with the same column name as the class field, however, if we want the column name to have a different name from the field name it represents, then we must use the @ColumnInfo annotation and define a different column name in parentheses.
@Entity(tableName = “buy_item_table”)
public class BuyItem {
@ColumnInfo(name = “id”)
@PrimaryKey(autoGenerate = true)
private int mId;
@ColumnInfo(name = “name”)
private String mName;
@ColumnInfo(name = “amaunt”)
private String mAmount;
@ColumnInfo(name = “time”)
private String mTimestamp;
}
@Embedded
With this notation, it is possible to embed one table within another (one-one reactions).
public class Address {
public String street;
public String state;
public String city;
@ColumnInfo(name = “post_code”)
public int postCode;
}
@Entity
public class User {
@PrimaryKey public int id;
public String firstName;
@Embedded public
Address address;
}
We can now make queries in this table and the User object has all the columns: id, firstName, street, state, city, and post_code.
REMARK:
We can add a prefix to all column names of an embedded board if we define it in parentheses with the prefix:@Embedded public (prefix = “loc_”)
Address address;
@ForeignKey
With this annotation, we connect two entities (tables) by connecting their columns (column from child entity with the value of the column from parent entity). This is practically an additional annotation within the @Entity annotation of a child entity:
@Entity(foreignKeys = @ForeignKey(…))
Example
We first define the parent entity (table) Course.
@Entity(tableName = “course”)
public class Course {
@PrimaryKey(autoGenerate = true)
private long id_course;
private String courseName;
public Course(String courseName) {
this.courseName = courseName;
}
}
The previous table can be linked to multiple students, so we will use the @ForeignKey annotation to define the child table Student:
@Entity(@ForeignKey (entity = Course.class,
parentColumns = “id_course”,
childColumns = “id_fkcourse”,
onDelete = CASCADE
))
public class Student {
@PrimaryKey(autoGenerate = true)
private long id_student;
private long id_fkcourse;
private String studentName;
public Student(String studentName) {
this.studentName = studentName;
}
}
In this example, we associated the values of the id column of the User table with the values of the userId Repo table. The meanings of the assigned values for onDelete / onUpdate are as follows:
int CASCADE — The “CASCADE” action propagates the operation of deleting or updating the parent key on each dependent child key.
int NO_ACTION — Default behavior when the parent key is modified or deleted from the database, and no other special actions are taken.
int RESTRICT — The RESTRICT action means that the application is prohibited from deleting (for onDelete ()) or changing (for onUpdate ()) the parent key when there are one or more child keys mapped to it.
int SET_DEFAULT — The “SET DEFAULT” actions are similar to SET_NULL, except that each of the child key columns is set to contain the default value of the columns instead of NULL.
REMARK:
It is known that creating such a connection does not have to lead to a relationship between these tables but only helps to clearly define what will happen to the “child entity” when a member of the “parent entity” is deleted (onDelete) or updated (onUpdate).
@Relations
With this annotation, it is possible to connect two tables without using @Foreign.
Example
The previous example shows the connection of two tables with the help of the @ForeignKey annotation, the same request can be solved without the Foreign key using another notation called @Relation. For that, it is necessary to create a new class with which we can create an instance that contains both the parent entity instance and the list of child entity instances:
public class CourseWithStudents {
@Embedded
public Course course;
@Relation(parentColumn = “id_course”,
entityColumn = “id_student”)
public List<Student> students;
public CourseWithStudents(Course course, List<Student> students) {
this.course = course;
this.students = students;
}
}
Later, the DAO is created as follows:
@Dao
public interface CourseDao {
@Transaction
@Insert
long insertCourse(Course course);
@Insert
void insertStudents(List<Student> students);
}
More on this in the next section.
Data Access Object — DAO
DAO (data access object) as its name suggests is a data access object. DAO must be either an interface or an abstract class, i.e. its methods have nobody because Room will generate all the required code depending on the annotation (@Insert, @Delete, @ Query (). This is how the amount of code that a programmer needs to create is reduced.
Also, a great advantage of this object is the ability to validate SQL statements at compile-time and thus point out errors in a timely manner that were not possible in working with the SQLiteOpenHelper class.
@Dao
With this annotation, the Room is informed that the given interface or abstract class is actually DAO. In general, each DAO is made for each entity, so for our entity from the example of “BuyItem” Dao would look like this:
@Dao
public interface BuyItemDao {
}
@Insert
This annotation marks the method in charge of entering data into the database.
@Insert
void insertItemToDB(BuyItem buyItem);
@Delete
This annotation marks the method responsible for deleting data from the database.
@Delete
void removeItemFromDB(long id);
@Query
This annotation marks the method in charge of obtaining data from the database depending on the definition condition (query):
@Query(“SELECT * FROM buy_item_table ORDER BY name DESC”)
List<BuyItem> getAllItemsFromDB();
Forwarding parameters to the query
It is often necessary to pass some parameter with which the query is filtered, so it looks like in the following example:
Example
@Query(“SELECT * FROM user WHERE age > :minAge”)
public User[] loadAllUsersOlderThan(int minAge);
We can even pass more parameters as in the following example:
Example
@Query(“SELECT * FROM user WHERE age BETWEEN :minAge AND :maxAge”)
public User[] loadAllUsersBetweenAges(int minAge, int maxAge);
We can also pass a collection as parameters (it can return a LiveData object):
@Query(“SELECT first_name, last_name FROM user WHERE region IN (:regions)”)
public LiveData<List<User>> loadUsersFromRegionsSync(List<String> regions);
REMARK:
When you pass data through application architecture layers, from the Room database, through the Repository class, then through the ViewModel class all the way to the user interface, ie the View class (activity or fragment), this data must be LiveData in all layers, or in other words all data which Room sends from DAO through some query to the Repository, and then from the Repository to the ViewModel, must be LiveData.The explanation for this lies in the fact that nowhere in the application do we need to set it to do Room for us, so we don’t need a MutableLiveData object anywhere ( which unlike the LiveData object has public settere) methods).
Example
This is what a typical Dao interface looks like:
@Dao
public interface BuyItemDao {
@Insert
void insertItemToDB(BuyItem buyItem);
@Delete
void removeItemFromDB(BuyItem buyItem);
@Query(“SELECT * FROM buy_item_table ORDER BY name DESC”)
LiveData<List<BuyItem>> getAllItemsFromDB();
}
DataBase
The class that represents the Room database must be abstract and extend the RoomDatabase class:
public abstract class BuyItemDB extends RoomDatabase {
}
In addition to this, we need to mark this class so that Room knows which class it is, and this is achieved through the @Database annotation. In the continuation of this annotation (in brackets) we define which entities (tables) this database contains, as well as the current version of the database:
@Database(entities = {BuyItem.class}, version = 1)
public abstract class BuyItemDB extends RoomDatabase {
}
Creating a database
When creating databases, it is “smart” to use a singleton pattern. The database is created using the Room method called databaseBuilder (). This method accepts the parameters: context, “class that defines the database” and the name of the database. In order to create and initialize the database, we need to call the build () method:
instance = Room.databaseBuilder(context.getApplicationContext(), BuyItemDB.class, DB_NAME)
.build();
If we want to prevent problems with database migration then it is good to call the fallbackToDestructiveMigration () method.
public static BuyItemDB instance;
public static synchronized BuyItemDB getInstance(Context context){
if (instance == null) {
instance = Room.databaseBuilder(context.getApplicationContext(), BuyItemDB.class, “buy_items_database”)
.fallbackToDestructiveMigration()
.build();
}
return instance;
}
REMARK:
If you want to access the database (using DeviceFileExplore) and view it using DB Browser for SQLite or a similar application, you need to call the setJournalMode method (JournalMode.TRUNCATE) when creating the database, otherwise the database you are viewing will be empty.instance = Room.databaseBuilder(context.getApplicationContext(), BuyItemDB.class, DB_NAME)
.fallbackToDestructiveMigration()
.setJournalMode(JournalMode.TRUNCATE)
.build();
In addition to this, it is necessary to create an abstract method that will return the corresponding Dao object:
public abstract BuyItemDao getBuyItemDao()
Example
This is what the whole class looks like:
public abstract class BuyItemDB extends RoomDatabase {
private static final String DB_NAME = “buy_items_database”;
public static BuyItemDB instance;
public static synchronized BuyItemDB getInstance(Context context){
if (instance == null) {
instance = Room.databaseBuilder(context.getApplicationContext(), BuyItemDB.class, DB_NAME)
.fallbackToDestructiveMigration()
.build();
}
return instance;
}
public abstract BuyItemDao getBuyItemDao();
}
Repository
Although this class does not belong directly to the Room library but is hierarchically above it, it will still be covered in this article because the Room library does not allow database operations to be performed from the main thread! For this reason, all methods related to CRUD operations must be executed on the background thread, and the only exception is the method that returns a LiveData object because Room takes care of it and automatically calls it from the background thread.
One way to run a method on a background thread is to extend the AsyncTask class, but since Android version 11 (API 30) the AsyncTask class has been discontinued, and a different approach is needed. Since we need to avoid using AsyncTask, another way to execute is to use the Executor object.
Executor service is created in the Database class with the definition of the required number of threads:
@Database(entities = {BuyItem.class},exportSchema = false, version = 1)
public abstract class BuyItemDB extends RoomDatabase {
private static final String DB_NAME = “buy_items_database”;
private static final int NUMBER_OF_THREADS = 3;
public static final ExecutorService databaseWriteExecutor = Executors.newFixedThreadPool(NUMBER_OF_THREADS);
Now within our repository class we can use ExecutorService and execute the DAO method asihorono from the backround thread.
Example
public class BuyItemsRepository {
private BuyItemDao buyItemDao;
private LiveData<List<BuyItem>> listFromDB;
public BuyItemsRepository(Application app){
BuyItemDB database = BuyItemDB.getInstance(app);
buyItemDao = database.getBuyItemDao();
listFromDB = buyItemDao.getAllItemsFromDB();
}
/* Static instance — Singleton pattern */
private static BuyItemsRepository mInstance = null;
public static BuyItemsRepository getInstance(Application application){
if(mInstance == null){
mInstance = new BuyItemsRepository(application);
}
return mInstance;
}
/* With this method, we don’t have to worry about execution from the background thread
because that’s what Room takes care of */
public LiveData<List<BuyItem>> getAllItemsFromRepo() {
return listFromDB;
}
/* Call DB method from repo on background thread */
public void insertItem (BuyItem buyItem) {
BuyItemDB.databaseWriteExecutor.execute(new Runnable() {
@Override
public void run() {
buyItemDao.insertItemToDB(buyItem);
}
});
}
/* Call DB method from repo on background thread */
public void removeItem(BuyItem buyItem) {
BuyItemDB.databaseWriteExecutor.execute(()-> {
buyItemDao.removeItemFromDB(buyItem);
});
}
}
