The first two articles, we have learned how to create Room database in android studio. Also, we quickly learned how to insert, update, delete and collect rows from it. If it is the first time to visit Android Hands website, you can use the below links to study Room Database from the first part. Furthermore, In this article we will learn in depth how use Room Database Queries in Android Studio Using DAO (Data Access Objects).
1- Room Database in Android Studio through simple To Do List App.
2- Room Database Migration in Android Studio.
Many android applications that depend on local persistence requires different ways to filter users data. In this article we will learn in depth the different Room Database query types either convenience queries or Queries for information using Room Dao classes. Moreover, we will learn these queries on a real simple ToDo List App.
Defining The Convenience Method
In Room Database there are multiple convenience methods that we can use in in Dao. For example, Insert, delete and update methods.
Insert Query
The following example code shows the different ways to insert one and multiple rows using Insert Query.
@Dao
public interface MyDaoInterface {
//insert single row
@Insert(onConflict = OnConflictStrategy.REPLACE)
public void insert(ToDoTable toDoTable);
//insert list of rows
@Insert(onConflict = OnConflictStrategy.REPLACE)
public void insert(List<ToDoTable> toDoTableList);
}
Furthermore,The insert method can receive one or more parameters. If it received one parameter, it should return long value which is the id of the new inserted row. Moreover, If it received list of parameters, it should return long[] which considered the list of id`s of the insert rows.
On Conflict Strategy
From the above code, we note the the @Insert annotation take a parameter which is onConflict. Fore instance we have set the on conflict strategy as Replace. So, to understand the on conflict strategy, we need to know what is constraint violation. Sometimes we need to define specific columns and not null, unique or specify the data type as integer. After that when we try to do an update or insert queries that violates the constraint (different data type). By the default SQLite database abort the operation in the progress and returns a constraint violation error. SQLite also allows one to define alternative ways for dealing with constraint violations.
ROLLBACK
When a constraint violation occurs, an immediate ROLLBACK occurs, thus ending the current transaction, and the command aborts with a return code of SQLITE_CONSTRAINT. If no transaction is active then this algorithm works the same as ABORT.
ABORT
When a constraint violation occurs, the command backs out any prior changes it might have made and aborts with a return code of SQLITE_CONSTRAINT. This is the default behavior for SQLite.
FAIL
When a constraint violation occurs, the command aborts with a return code SQLITE_CONSTRAINT. But any changes to the database that the command made prior to encountering the constraint violation are preserved and are not backed out by FAIL strategy. For example, if an UPDATE statement encountered a constraint violation on the 100th row that it attempts to update, then the first 99 row changes are preserved but change to rows 100 and beyond never occur.
IGNORE
When a constraint violation occurs, the one row that contains the constraint violation is not inserted or changed. But the command continues executing normally. Other rows before and after the row that contained the constraint violation continue to be inserted or updated normally.So, when you use IGNORE strategy, no error is returned.
REPLACE
When a UNIQUE constraint violation occursand you are using a REPLACE strategy, the pre-existing row that caused the constraint violation is removed prior to inserting or updating the current row. Thus the insert or update always occurs. The command continues executing normally. No error is returned.
Update Query
Easily, we update a set of entities using a convenience update query. Just, cast the entity or list of entities in the query method. Furthermore it uses the query that matches the id of the entity.
@Dao
public interface MyDaoInterface {
@Update(onConflict = OnConflictStrategy.REPLACE)
void update(ToDoTable toDoTable);
}
Delete Query
@Dao
public interface MyDaoInterface {
@Delete()
void delete(ToDoTable toDoTable);
@Delete()
void delete(List<ToDoTable> toDoTableList);
}
The Delete convenience method removes a set of entities, given as parameters, from the database. It uses the primary keys to find the entities to delete.
Query for information in Room Database Queries Using DAO
Moreover, we can use Room Database Queries Using DAO. Just, by using @Query() annotation in Dao classes. It helps to perform insert,update,delete and select queries. Each method is verified at compile time so that we can detect the query errors( as wrong column names).
@Dao
public interface MyDaoInterface {
@Query("SELECT * FROM to_to_table")
List<ToDoTable> collectItems();
}
From the above example, we not that the collectItems() method returns a list of todo table. At run time Room database detects that it is select query for all fields in todo table.
Parsing parameters into the query
In most of our apps we need to perform filtering on Room database. such as we need to filter the completed task items. Moreover, we need to filter by date. Also, arrange and limit number of rows. So that Room database allow us to parse parameter into the query. See the following example.
@Dao
public interface MyDaoInterface {
@Query("SELECT * FROM to_to_table WHERE completed LIKE :bCompleted")
List<ToDoTable> collectItems(boolean bCompleted);
}
The above query will collect entities that value of completed column like the caste value of bCompleted. Another example shows how to collect entities where date lies between two dates.
@Dao
public interface MyDaoInterface {
@Query("SELECT * FROM to_to_table WHERE date BETWEEN :startDate AND :endDate")
List<ToDoTable> collectItems(Date startDate,Date endDate);
}
Thank You
This article not covers all about Room Database Queries Using DAO, as we explain it in the next article where we ready to learn Room Relational Database.
Next