WIA2007
MOBILE APPLICATION DEVELOPMENT
APP DATA STORAGE II
Created and Updated by S. Ong
Adapted by Chiew TK
Semester 1, 2023/2024
simying.ong@um.edu.my
2
OVERVIEW
SAVE DATA (STORAGE)
• Few options to save your
mobile app data:
• App-specific storage
• Shared storage
• Preferences
• Database Part 2
• Overview
• SQLite
• Data Entities
• Data Access Objects
• Object Relationship
simying.ong@um.edu.my
3
OVERVIEW
DATABASE – RECALL FROM PART1
• Structured data stored in a private database.
• After the Jetpack library – Room Persistence Library is
introduced to handle all database access.
• Room is a persistence library that's part of Android
Jetpack.
• Room is an abstraction layer on top of a SQLite database.
• Do not need explicit permission to access the data.
• Other applications CANNOT access the data.
• The data will be removed when the mobile app is
uninstalled.
simying.ong@um.edu.my
4
LOCAL DATABASE
• It’s always important to store data locally – that the user
can access it (and still able to perform some crucial
operations) while the application is offline.
• SQLite is automatically created when the mobile
application is installed on the mobile device.
simying.ong@um.edu.my
5
THE SQLITE DATABASE
OVERVIEW
• Open source and lightweight relational database
administration system.
• Included in Android framework.
• Take a minimum amount of disk space. Just a single
disk file in the database:
• No server is required.
• It is an application library itself.
• Doesn’t need installation.
• Cross-platform
• can be copied and used on a different machine with a
different architecture.
simying.ong@um.edu.my
6
THE SQLITE DATABASE
THE ISSUES
• In the past (before Jetpack library), we interact directly
with SQLite databases using android.database.sqlite
API package (please refer to
https://developer.android.com/reference/android/dat
abase/sqlite/package-summary to know more about
it).
• However, using these APIs causes the following issues:
Tedious to manually handle the operations because
there are multiple classes involved.
No compile time verification for raw SQL query – might
be prone to error.
Use of many boilerplate codes (repetitive codes with
no/little variance to accomplish minor functionalities) to
convert between SQL queries and data objects.
simying.ong@um.edu.my
7
ROOM PERSISTENCE LIBRARY
• Hence, room persistence library is introduced to:
✓ Compile-time SQL query verification
✓ Ready and convenient annotation to reduce the
boilerplate codes (also more organized, and with simpler
operation).
✓ Streamline the database migration path.
• It is also an API BUT provide an abstraction layer on top
of SQLite for managing the data as Java object – and
can leverage the full power of SQLite database.
• Not an ORM (Object Relational Mapping) that contains
more restrictions when perform operation directly using
SQLite.
simying.ong@um.edu.my
8
SETUP ROOM
• Add dependencies in build.gradle file:
Add dependencies
simying.ong@um.edu.my
9
PRIMARY COMPONENTS
• There are THREE major
components involved in
Room:
• Database class
• Hold the database and
provide access to the
persisted data.
• Data Access Objects (DAO)
• Provide methods to
perform operations in the
database.
• Data Entities
• Represents table in the
database.
Room Library Architecture
simying.ong@um.edu.my
10
PRIMARY COMPONENTS
• 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.
simying.ong@um.edu.my
11
DATA ENTITIES
• Use Room to define the database schema without
writing any SQL code.
• Use data entities to represent the object to store.
• Each entity = table in the associated Room database.
• Each Room entity is a class annotated as @entity
• Each instance = row of data in the table.
simying.ong@um.edu.my
12
DATA ENTITIES
• To define the data entity:
@entity annotation
@Entity
public class User {
@PrimaryKey Table name
public int id;
Field names (and data type)
public String firstName;
public String lastName;
} Primary Key using @PrimaryKey
annotation (MUST define one)
simying.ong@um.edu.my
13
DATA ENTITIES
• To define the data entity (customization):
@Entity(tableName = "users") if the table name and class
public class User { name are different
@PrimaryKey
public int id;
@ColumnInfo(name = "first_name")
public String firstName; if the field names and
column names are different
@ColumnInfo(name = "last_name")
public String lastName;
}
simying.ong@um.edu.my
14
DATA ENTITIES
• To define the data entity (customization):
@Entity(tableName = "users") if the table name and class
public class User { names are different.
@PrimaryKey (autoGenerate = true)
public int id; if want the primary key to be
auto-generated.
@ColumnInfo(name = "first_name")
public String firstName; if the field names and
column names are different.
@ColumnInfo(name = "last_name")
public String lastName;
}
simying.ong@um.edu.my
15
DATA ENTITIES
• To define the data entity (customization):
@Entity(primaryKeys = {"firstName", "lastName"})
public class User {
public String firstName;
public String lastName;
} If contains composite key
simying.ong@um.edu.my
16
DATA ENTITIES
• To define the data entity (ignore field):
@Entity
public class User {
@PrimaryKey
public int id;
public String firstName; If do not want the field of the
public String lastName; entity to persist
- Use @ignore annotation
@Ignore
Bitmap picture;
}
simying.ong@um.edu.my
17
DATA ENTITIES
• To define the data entity (ignore field):
@Entity(ignoredColumns = "picture") If do not want the field of
public class RemoteUser extends User the entity to persist (and
{ the field is inherited from
@PrimaryKey parent entity
public int id; - Use ignoredColumns
parameter
public boolean hasVpn;
}
simying.ong@um.edu.my
18
DATA ACCESS OBJECT (DAO)
• Need to use Data Access Object (DAO) to interact with
the stored data that uses the Room database.
• Each DAO consists of methods that offer abstract
access to the database.
• Room Persistence Library will generate the actual
implementation during compile time.
• Have to define each DAO as an either interface or
abstract class.
• Annotate using @Dao
• Do not have property, but contains the definition of one
or more methods to interact with data in a database.
simying.ong@um.edu.my
19
DATA ACCESS OBJECTS
• To define DAO:
@Dao
public interface UserDao { @Dao annotation
@Query("SELECT * FROM user")
List<User> getAll();
Query method to writing
@Query("SELECT * FROM user WHERE uid IN (:userIds)") your own SQL query to
List<User> loadAllByIds(int[] userIds); interact with database
@Query("SELECT * FROM user WHERE first_name LIKE :first AND " +
"last_name LIKE :last LIMIT 1")
User findByName(String first, String last);
@Insert Convenience method to
void insertAll(User... users); insert, update and delete
without writing SQL codes
@Delete
void delete(User user);
}
simying.ong@um.edu.my
20
DATA ACCESS OBJECTS
CONVENIENCE METHODS - INSERT
• Used to define simpler insertion of the data in the
database.
• @Insert annotation
• When an insert method is called, Room inserts each
passed entity instance into the corresponding table.
• If a single parameter is received:
• Returns a long value – rowid created
• If the parameter is an array/collection:
• Returns array/collection of long values – rowids created
simying.ong@um.edu.my
21
DATA ACCESS OBJECTS
CONVENIENCE METHODS - INSERT
• Example of insertion method
@Dao
public interface UserDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
public void insertUsers(User... users);
@Insert
public void insertBothUsers(User user1, User user2);
@Insert
public void insertUsersAndFriends(User user, List<User> friends);
}
simying.ong@um.edu.my
22
DATA ACCESS OBJECTS
CONVENIENCE METHODS - UPDATE
• A simpler method to define a specific row update in
the Room database table.
• Use @Update annotation.
• Accepts data entity instances as the parameter.
• Uses primary key to match with the passed instances:
• If no match, no changes are made.
• Can optionally change the return type to int to obtain
the number of rows updated.
simying.ong@um.edu.my
23
DATA ACCESS OBJECTS
CONVENIENCE METHODS - UPDATE
• Example of update method
@Dao
public interface UserDao {
@Update To update one or more users
public void updateUsers(User... users);
}
simying.ong@um.edu.my
24
DATA ACCESS OBJECTS
CONVENIENCE METHODS - DELETE
• A simpler method to define the method to delete
specific rows in the table.
• Use @Delete annotation.
• Same with insert and delete methods, it accepts data
entity instances as the parameter.
• Uses primary key to match with the passed instances:
• If no match, no deletions are made.
• Can optionally change the return type to int to obtain
the number of rows deleted successfully.
simying.ong@um.edu.my
25
DATA ACCESS OBJECTS
CONVENIENCE METHODS - DELETE
• Example of delete method
@Dao
public interface UserDao {
@Delete To delete one or more users
public void deleteUsers(User... users);
}
simying.ong@um.edu.my
26
DATA ACCESS OBJECTS
QUERY METHOD
• All convenience methods are simpler methods
• Perform simple operations using the primary key
• The @Query allows us to write our own SQL queries (
more complex) to perform customized insertion,
update, and deletion in the table.
• Room validates SQL queries during compile-time that
significantly reduces run-time errors during any
database operations.
• For example
The Select query returns all
@Query("SELECT * FROM user")
public User[] loadAllUsers(); users’ data in the table.
simying.ong@um.edu.my
27
DATA ACCESS OBJECTS
QUERY METHOD – OBTAIN TABLE SUBSET
• Sometimes, not all columns in the table are relevant.
• Should always maintain a good habit of only retrieving
relevant data whenever necessary – to save resources.
• To obtain a subset of the table (say we only need first
name and last name in the user table):
public class NameTuple {
@ColumnInfo(name = "first_name")
public String firstName;
@ColumnInfo(name = "last_name") Let’s create an object to
@NonNull hold these two values
public String lastName;
}
simying.ong@um.edu.my
28
DATA ACCESS OBJECTS
QUERY METHOD – OBTAIN TABLE SUBSET
• To obtain a subset of the table (say we only need first
name and last name in the user table):
@Query("SELECT first_name, last_name FROM user") Then customized the
public List<NameTuple> loadFullName(); query method to only
obtain the two values.
• Room allows returning a simple object from any of the
queries if it matches the set of result columns on the
returned object.
• Room understands that the query method will return the
two values and these values can be mapped to
NameTuple class.
• If cannot be mapped (different column data type/number
of columns) – display warning.
simying.ong@um.edu.my
29
DATA ACCESS OBJECTS
QUERY METHOD – PASS PARAMETER
• Passing parameters to the query is important because it
helps with the filtering/provides a more specific target.
• Room uses the method parameter as the bind
parameter in the query.
• For example:
@Query("SELECT * FROM user WHERE age > :minAge")
public User[] loadAllUsersOlderThan(int minAge);
Returns all users above minAge.
Use “:” in the query to indicate it is
a bind parameter.
simying.ong@um.edu.my
30
DATA ACCESS OBJECTS
QUERY METHOD – PASS PARAMETER
• For example (multiple parameters):
@Query("SELECT * FROM user WHERE age BETWEEN :minAge AND
:maxAge")
public User[] loadAllUsersBetweenAges(int minAge, int maxAge);
Pass two parameters
@Query("SELECT * FROM user WHERE first_name LIKE :search " +
"OR last_name LIKE :search")
public List<User> findUserWithName(String search);
Same method parameter but
is used twice in the query
simying.ong@um.edu.my
31
DATA ACCESS OBJECTS
QUERY METHOD – PASS PARAMETER
• For example (collection of parameter – unknown
number):
@Query("SELECT * FROM user WHERE region IN (:regions)")
public List<User> loadUsersFromRegions(List<String> regions);
Returns data for a
subset of regions.
simying.ong@um.edu.my
32
DATA ACCESS OBJECTS
QUERY METHOD – MULTIPLE TABLES
• Use the JOIN clause to combine multiple tables in
fulfilling the query.
• For example (joins three tables together to return the
books that are currently on loan to a specific user):
@Query("SELECT * FROM book " +
"INNER JOIN loan ON loan.book_id = book.id " +
"INNER JOIN user ON user.id = loan.user_id " +
"WHERE user.name LIKE :userName")
public List<Book> findBooksBorrowedByNameSync(String userName);
simying.ong@um.edu.my
33
DATA ACCESS OBJECTS
QUERY METHOD – MULTIPLE TABLES
• For example (joins two tables together to return the
different columns from different table):
@Dao
public interface UserBookDao {
@Query("SELECT user.name AS userName, book.name AS bookName " +
"FROM user, book " +
"WHERE user.id = book.user_id")
public LiveData<List<UserBook>> loadUserAndBookNames();
// You can also define this class in a separate file, as long as you add the
// "public" access modifier.
static class UserBook {
Reference for LiveData:
public String userName;
public String bookName; https://developer.android.com/topic/
} libraries/architecture/livedata
}
simying.ong@um.edu.my
34
DATA ACCESS OBJECTS
QUERY METHOD – OTHERS
• Multi Map
• https://developer.android.com/training/data-
storage/room/accessing-data#multimap
• Special Type Return (Paging Library and Cursor access)
• https://developer.android.com/topic/libraries/architectu
re/paging
• https://developer.android.com/training/data-
storage/room/accessing-data#special-return-types
• https://developer.android.com/reference/kotlin/android
/database/Cursor
simying.ong@um.edu.my
35
OBJECT RELATIONSHIP
• SQLite is a structured and relational database.
• A relationship can be defined between entities:
• Embedded object
• One-to-One relationship
• One-to-Many relationship
• Many-to-Many relationship
• And others.
simying.ong@um.edu.my
36
OBJECT RELATIONSHIP
EMBEDDED OBJECT
• Group relevant (and cohesive) fields as a whole object.
• Use @Embedded annotation to represent the object that
is decomposed into its subfield within the table.
public class Address {
public String street;
public String state;
public String city;
@ColumnInfo(name = "post_code") public int postCode;
}
@Entity User has an Address
public class User { object that decomposed
@PrimaryKey public int id;
to street, state, city, and
public String firstName; postcode.
@Embedded public Address address;
}
simying.ong@um.edu.my
37
OBJECT RELATIONSHIP
EMBEDDED OBJECT
• Group relevant (and cohesive) fields as a whole object.
• Use @Embedded annotation to represent the object that
is decomposed into its subfield within the table.
public class Address {
public String street;
public String state;
public String city;
@ColumnInfo(name = "post_code") public int postCode;
}
@Entity This User table contains id,
public class User { firstName, street, state,
@PrimaryKey public int id;
city, and postcode
public String firstName; columns.
@Embedded public Address address;
}
simying.ong@um.edu.my
38
OBJECT RELATIONSHIP
1-TO-1 RELATIONSHIP
• Each instance of the parent entity corresponds to
exactly one instance of the child entity.
• For example:
• A music streaming app where a user has a library of
songs.
• One user can only have one library, while a library can
only correspond to one user.
simying.ong@um.edu.my
39
OBJECT RELATIONSHIP
1-TO-1 RELATIONSHIP
• Example (music streaming app)
@Entity Create two classes:
public class User { User and Library
@PrimaryKey public long userId;
public String name;
public int age;
}
@Entity
public class Library { One of the entities must
@PrimaryKey public long libraryId; include one variable that
public long userOwnerId; references to the primary
} key of another entity.
simying.ong@um.edu.my
40
OBJECT RELATIONSHIP
1-TO-1 RELATIONSHIP
• Before query, model the 1-to-1 relationship between
these two entities:
public class UserAndLibrary { Create a new data class
@Embedded public User user; that has two instances
@Relation( (one from parent entity
parentColumn = "userId", and one from child entity)
entityColumn = "userOwnerId"
) Use @Relation annotation
public Library library; to the instance of the child
} entity, and set:
parentColumn – primary
key in parent entity
entityColumn – column
name in child entity that
references to
simying.ong@um.edu.my
parentColumn
41
OBJECT RELATIONSHIP
1-TO-1 RELATIONSHIP
• Lastly, to perform the query (add Query method to
DAO class):
Returns all instances that
pairs and parent-child
@Transaction entities.
@Query("SELECT * FROM User")
public List<UserAndLibrary> getUsersAndLibraries();
Use @Transaction
annotation because two
queries will be performed
- To ensure the queries are
performed atomically
simying.ong@um.edu.my
42
OBJECT RELATIONSHIP
1-TO-MANY RELATIONSHIP
• Each instance in the parent entity corresponds to zero
to many instances of the child entity
• BUT each child can only correspond to exactly one
instance in the parent entity.
• For example (music streaming app)
• One user can create zero to many playlists, but each
playlist belongs to only one user.
• Create two classes – User and Playlist
@Entity @Entity
public class User { public class Playlist {
@PrimaryKey public long userId; @PrimaryKey public long playlistId;
public String name; public long userCreatorId;
public int age; public String playlistName;
} }
simying.ong@um.edu.my
43
OBJECT RELATIONSHIP
1-TO-MANY RELATIONSHIP
• Before query, model the 1-to-many relationship
between these two entities:
public class UserWithPlaylists { Codes are similar with 1-to-
@Embedded public User user; 1 relationship.
@Relation(
parentColumn = "userId",
entityColumn = "userCreatorId"
)
public List<Playlist> playlists; The only difference is the
} list of child instances.
• To query:
@Transaction
@Query("SELECT * FROM User")
public List<UserWithPlaylists> getUsersWithPlaylists();
simying.ong@um.edu.my
44
OBJECT RELATIONSHIP
MANY-TO-MANY RELATIONSHIP
• Each instance in the parent entity corresponds to zero
to many instances of the child entity
• AND each child instance corresponds to zero-to-many
instances in the parent entity.
• For example (music streaming app)
• Each playlist can include many songs.
• Each song can be in many playlists.
simying.ong@um.edu.my
45
OBJECT RELATIONSHIP
MANY-TO-MANY RELATIONSHIP
• Many-to-many relationship: @Entity
public class Playlist {
@PrimaryKey public long playlistId;
No reference to the parent public String playlistName;
entity in the child entity. }
Create a third class (cross- @Entity
reference table) to represent public class Song {
an associate entity. @PrimaryKey public long songId;
public String songName;
public String artist;
Must have columns for the }
primary key from both
classes. @Entity(primaryKeys = {"playlistId",
"songId"})
public class PlaylistSongCrossRef {
public long playlistId;
public long songId;
}
simying.ong@um.edu.my
46
OBJECT RELATIONSHIP
MANY-TO-MANY RELATIONSHIP
• Next step?
• If you want to query many playlists with a list of songs for
each playlist:
• Create a new data class that contains a
single Playlist object and a list of all Song objects that the
playlist includes.
• If you want to query many songs with a list of playlists for
each song:
• Create a new data class that contains a single Song object
and a list of all playlist objects that the song includes.
simying.ong@um.edu.my
47
OBJECT RELATIONSHIP
MANY-TO-MANY RELATIONSHIP
public class PlaylistWithSongs {
@Embedded public Playlist playlist;
@Relation(
parentColumn = "playlistId",
entityColumn = "songId",
associateBy = @Junction(PlaylistSongCrossref.class)
)
public List<Song> songs;
} Use associateBy property
in @Relation to model the
public class SongWithPlaylists { relationship.
@Embedded public Song song;
@Relation(
parentColumn = "songId",
entityColumn = "playlistId",
associateBy = @Junction(PlaylistSongCrossref.class)
)
public List<Playlist> playlists;
}
simying.ong@um.edu.my
48
OBJECT RELATIONSHIP
MANY-TO-MANY RELATIONSHIP
• To query, on DAO class:
Returns all the resulting
@Transaction PlaylistWithSongs objects.
@Query("SELECT * FROM Playlist")
public List<PlaylistWithSongs> getPlaylistsWithSongs();
@Transaction
@Query("SELECT * FROM Song")
public List<SongWithPlaylists> getSongsWithPlaylists();
Returns all the resulting
SongWithPlaylists objects.
simying.ong@um.edu.my
49
MORE REFERENCES
• Asynchronous Query Execution (running database
query on the main thread):
• https://developer.android.com/training/data-
storage/room/async-queries
• Test and debug database
• https://developer.android.com/training/data-
storage/room/testing-db
simying.ong@um.edu.my
