07/08/2021 Database@Android | Alexandria
MODULE
Database@Android
Nawfal Ali
Updated 29 April 2020
In this week, we will learn how to add a local database to Android applications.
A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). [oracle.com]
Android uses SQLite as a database management system.
SQLite uses SQL language (Structured Query Language)
SQL is a language that allows you to access and manipulate databases. SQL statements are used to execute tasks such as add data to a database or retrieve data from a database. SQL is used by nearly all relational databases to query, manipulate, and de ne data, and to provide access control.
What is a database?
What language does SQLite use to communicate?
So, what is SQL?
https://www.alexandriarepository.org/module/databaseandroid/ 1/17
07/08/2021 Database@Android | Alexandria
Now, let¡¯s have an example. The depicted below is an excerpt from a table called ¡®Customers¡¯ [Ref WeSchools] and you can notice the following:
Tables consist of rows and columns Each column has a name
Each row has the same set of columns Each cell stores one value only
The data in a column have the same data type
The table has a column that works as an index (CustomerID). This column is unique and cannot contain NULL values. This column is called the ¡®primary key¡¯.
What if we need to retrieve the customers who live in Germany, then we would call:
1. SELECT*FROMCustomersWHERECountry=’Germany’; https://www.alexandriarepository.org/module/databaseandroid/ 2/17
07/08/2021 Database@Android | Alexandria
Where:
¡®Customers¡¯ is the table name
¡®*¡¯ means fetch all the columns
¡®WHERE¡¯ is the row lter. It selects the row that matches the provided criteria.
and here is the output after executing the above SQL statement
As you can see, all the values in the last column ¡®Country¡¯ is Germany.
SQLite, Rooms, and LiveData
https://www.alexandriarepository.org/module/databaseandroid/ 3/17
07/08/2021
Database@Android | Alexandria
SQLite database: is a relational database management system that is used by Android to store relational data.
DAO: Data Access Objects are the main classes where you de ne your database interactions. They can include a variety of query methods.
Entities: Each entity represents one table in the database
Room database: The room database object provides the interface to the underlying SQLite database.
Repository: It¡¯s a class that contains all of the code necessary for directly handling all data sources used by the application. This avoids the need for the UI controller and ViewModel to contain code that directly accesses sources such as databases or web services.
ViewModel: A ViewModel object provides the data for a speci c UI component, such as a fragment or activity, and contains data-handling business logic to communicate with the model.
LiveData: Its a data holder that allows a value to become observable. In other words, an observable object has the ability to notify other objects when changes to its data occur thereby solving the problem of making sure that the user interface always matches the data within the ViewModel.
https://www.alexandriarepository.org/module/databaseandroid/ 4/17
07/08/2021 Database@Android | Alexandria
Let¡¯s develop an application that generates customers randomly. Each customer is represented by a name and address as shown below:
https://www.alexandriarepository.org/module/databaseandroid/ 5/17
07/08/2021 Database@Android | Alexandria
https://www.alexandriarepository.org/module/databaseandroid/
6/17
07/08/2021 Database@Android | Alexandria
Entities
As aforementioned, an entity is a Java class that de nes a table in the database.
Now, let¡¯s create a table the following attributes
NAME IN JAVA id
name address
NAME IN DB customerId
customerName customerAddress
DATATYPE
integer
string
string
Primary Key NonNull
1. packagecom.fit2081.rooms.provider; 2.
3. importandroidx.annotation.NonNull;
4. importandroidx.room.ColumnInfo;
5. importandroidx.room.Entity;
6. import androidx.room.PrimaryKey;
7.
8. @Entity(tableName = “customers”)
9. public class Customer {
10. @PrimaryKey(autoGenerate = true)
11. @NonNull
12. @ColumnInfo(name = “customerId”)
13. private int id;
14. @ColumnInfo(name = “customerName”)
https://www.alexandriarepository.org/module/databaseandroid/
7/17
07/08/2021
15. 16. 17. 18. 19.
20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40.
Notes
line@8: the annotation @Entity is required to de ne the class as a Room Entity. It also speci es the table name.
the class ¡®Customers¡¯ has three attributes id, name, and address. Each attribute has an annotation ¡®ColumnInfo¡¯ that speci es the column
private String name;
@ColumnInfo(name = “customerAddress”)
private String address;
public Customer(String name, String address) { this.name = name;
this.address = address;
}
public int getId() { return id;
}
public String getName() { return name;
}
public String getAddress() { return address;
}
public void setId(@NonNull int id) { this.id = id;
} }
Database@Android | Alexandria
https://www.alexandriarepository.org/module/databaseandroid/ 8/17
07/08/2021
Database@Android | Alexandria
name in the database
line@10: the annotation ¡®@PrimaryKey¡¯ makes the id as a primary key for the current table which the ¡® @NonNull¡¯ ensures that this column will not be saved without a value.
Room Database
In this class, we will de ne the Room Database (database might contain one or more tables)
1. 2. 3. 4. 5.
6. 7. 8.
9. 10. 11. 12. 13.
14. 15. 16. 17.
18.
19. 20.
packagecom.fit2081.rooms.provider; importandroid.content.Context; importandroidx.room.Database; importandroidx.room.Room; importandroidx.room.RoomDatabase;
import java.util.concurrent.ExecutorService; importjava.util.concurrent.Executors;
@Database(entities = {Customer.class}, version = 1) publicabstractclassCustomerDatabaseextendsRoomDatabase{
public static final String CUSTOMER_DATABASE_NAME = “customer_database”;
public abstract CustomerDao customerDao();
// marking the instance as volatile to ensure atomic access to the
variable
private static volatile CustomerDatabase INSTANCE; private static final int NUMBER_OF_THREADS = 4; static final ExecutorService databaseWriteExecutor =
https://www.alexandriarepository.org/module/databaseandroid/ 9/17
07/08/2021
21. 22. 23. 24. 25. 26. 27.
28.
29. 30. 31. 32. 33. 34. 35. 36.
Note:
Database@Android | Alexandria
Executors.newFixedThreadPool(NUMBER_OF_THREADS);
static CustomerDatabase getDatabase(final Context context) { if (INSTANCE == null) {
synchronized (CustomerDatabase.class) { if (INSTANCE == null) {
INSTANCE =
Room.databaseBuilder(context.getApplicationContext(),
CUSTOMER_DATABASE_NAME)
}
} }
return INSTANCE; }
}
CustomerDatabase.class,
.build();
Line@10: The ¡®@Database¡¯ annotation is required to consider the current class as a Room database. It speci es the list of entities and the current version. The version is required for upgrading or downgrading the current scheme.
Line@20: the ¡®databaseWriteExecutor¡¯ instance will be used by the repository to execute the DAO methods
Line@23: the ¡®getDatabase¡¯ method returns a reference to the current database instance if it is not null. Otherwise, it creates a new instance using ¡®Room.databaseBuilder()¡¯, which needs as input the context, a reference to the Room Database class, and a name for the database.
DAO
https://www.alexandriarepository.org/module/databaseandroid/
10/17
07/08/2021 Database@Android | Alexandria
The Dao is an interface that de nes the database operations that should be performed on the database.
1. packagecom.fit2081.rooms.provider; 2.
3. importandroidx.lifecycle.LiveData;
4. importandroidx.room.Dao;
5. importandroidx.room.Insert;
6. import androidx.room.Query;
7.
8. importjava.util.List; 9.
10. @Dao
11. publicinterfaceCustomerDao{
12.
13. @Query(“select * from customers”)
14. LiveData
15.
16. @Query(“select * from customers where customerName=:name”)
17. List
18.
19. @Insert
20. void addCustomer(Customer customer);
21.
22. @Query(“delete from customers where customerName= :name”)
23. void deleteCustomer(String name);
24.
25. @Query(“delete FROM customers”)
26. void deleteAllCustomers();
27. }
Notes:
Line@10: the annotation ¡®@Dao¡¯ is required to consider the interface as a DAO.
https://www.alexandriarepository.org/module/databaseandroid/
11/17
07/08/2021
Database@Android | Alexandria
Line@13: the query annotation provides the select SQL statement that should be executed when the method ¡®getAllCustomer()¡¯ is invoked. You can notice that the select statement at line 13 retrieves all the records in table ¡®customers¡¯ (see entity@line 8) as it does not have the ¡®WHERE¡¯ clause.
Line@14: the output of ¡®getAllCustomer¡¯ method is LiveData which allows us to observe any changes to the database.
Line@16: this query is pretty similar to the previous one except it contains a ¡®WHERE¡¯ clause. It retrieves all the customers with a name equals to a value that is provided as an input parameter to the method ¡®getCustomer¡¯. Please note the colon (:) which is required to specify the name of the variable that should be used.
Line@19: the ¡®@Insert¡¯ annotation inserts the object that is passed through method ¡®addCustomer¡¯
Line@22: this delete query deletes all the rows that have name equals to input parameter to the method ¡®deleteCustomer¡¯
Line@25: this query deletes all the rows (empty the table) if a call to method ¡®deleteAllCustomers¡¯ occurs
Room Repository
The repository is a Java class that providers easy and clean API so that the application can access di erent data sources as depicted blow. As you can notice, the repository is managing two di erent data sources a local SQLite database and a remote data source.
https://www.alexandriarepository.org/module/databaseandroid/
12/17
07/08/2021
Database@Android | Alexandria
1. 2. 3. 4. 5.
6. 7. 8.
9. 10. 11. 12. 13. 14.
packagecom.fit2081.rooms.provider; importandroid.app.Application; importandroidx.lifecycle.LiveData;
importjava.util.List;
public class CustomerRepository {
private CustomerDao mCustomerDao;
private LiveData
CustomerRepository(Application application) {
CustomerDatabase db = CustomerDatabase.getDatabase(application); mCustomerDao = db.customerDao();
https://www.alexandriarepository.org/module/databaseandroid/
13/17
07/08/2021
15. 16. 17. 18. 19.
20. 21.
22. 23. 24. 25. 26. 27. 28. 29.
Database@Android | Alexandria
mAllCustomers = mCustomerDao.getAllCustomer();
}
LiveData
}
void insert(Customer customer) {
CustomerDatabase.databaseWriteExecutor.execute(() ->
mCustomerDao.addCustomer(customer));
}
void deleteAll(){ CustomerDatabase.databaseWriteExecutor.execute(()->{
mCustomerDao.deleteAllCustomers();
});
} }
Notes:
Line@9: declares a reference to the Dao interface which will be used to execute the database operations we have de ned earlier.
Line@10: de nes an array list that is used to hold a copy of your data. Line@12: this contractor creates a reference to the database that will be used to access the Dao interface. The Dao interface will be used later (line@15) to get the list of customers.
Line@20: this method inserts a new row (object) to the database. It uses the ¡®databaseWriteExecutor¡¯ to access the database and execute the insert SQL statement.
Line@24: the same as above. This method uses the ¡®databaseWriteExecutor¡¯ to execute the delete statement.
ViewModel
https://www.alexandriarepository.org/module/databaseandroid/
14/17
07/08/2021 Database@Android | Alexandria
If a fragment or an activity needs special data or a di erent way to retrieve the data, then the ViewModel is the best place to implement your logic. In the following AndroidViewModel, all the methods (that will be invoked later by the activity or fragment) call their counterpart methods that are
https://
15/17
implemented in the Repository.
1. packagecom.fit2081.rooms.provider; 2.
3. importandroid.app.Application;
4.
5. importandroidx.annotation.NonNull;
6. import androidx.lifecycle.AndroidViewModel;
7. importandroidx.lifecycle.LiveData;
8.
9. import java.util.List;
10.
11. publicclassCustomerViewModelextendsAndroidViewModel{
12. private CustomerRepository mRepository;
13. private LiveData
14.
15. public CustomerViewModel(@NonNull Application application) {
16. super(application);
17. mRepository = new CustomerRepository(application);
18. mAllCustomers = mRepository.getAllCustomers();
19. }
20.
21. public LiveData
22. return mAllCustomers;
23. }
24.
25. public void insert(Customer customer) {
26. mRepository.insert(customer);
27. }
www.alexandriarepository.org/module/databaseandroid/
07/08/2
https://www.alexandriarepository.org/module/databaseandroid/ 16/17
021 Database@Android | Alexandria
28. 29. 30.
31. 32.
public void deleteAll(){ mRepository.deleteAll();
} }
MainActivity
Now, its time to execute some database operations.
If the activity¡¯s controller (i.e. Activity Java le) needs to access the database, it has to create an instance of the view model as shown in the architecture depicted above.
1. 2. 3.
4. 5. 6. 7. 8.
Notes:
private CustomerViewModel mCustomerViewModel;
mCustomerViewModel = new ViewModelProvider(this).get(CustomerViewModel.class);
mCustomerViewModel.getAllCustomers().observe(this, newData -> { adapter.setCustomers(newData); adapter.notifyDataSetChanged();
tv.setText(newData.size() + “”);
});
Line@3: the app uses the ViewModelProvider (An utility class that provides ViewModels for a scope) to get access to the view model. Line@4: this line invokes the getAllCustomers to get the list of customers. Because the output of this method is LiveData, the caller has to observe. The observe method invokes the callback method which is provided in the second parameter each time the data gets changes. The new updates can be accessed via the input variable ¡®newData¡¯
07/08/2021 Database@Android | Alexandria
References:
https://developer.android.com/jetpack/docs/guide https://developer.android.com/training/data-storage/room
Copyright ý Monash University, unless otherwise stated. All Rights Reserved, except for individual components (or items) marked with their own licence restrictions
Copyright ý 2021 Monash University, unless otherwise stated
Disclaimer and Copyright Privacy
Service Status
https://www.alexandriarepository.org/module/databaseandroid/
17/17