Android jetpack component - Room

What is Room?

Room is a framework library covering SQLite abstraction layer specially provided by Google to simplify old-fashioned SQLite operations
Room is also an ORM framework. It provides an abstraction layer on SQLite, shields some underlying details, and uses objects to operate the database. CRUD is as simple as calling methods with objects.
Room is an object relational mapping (ORM) library. You can easily convert SQLite table data into Java objects. Room checks SQLite statements at compile time.

Room provides an abstraction layer for SQLite, so that while making full use of SQLite, you can access the database smoothly.


realization SQLite Add, delete, query and modify functions of. 


1.Easy to use (similar to Retrofit Library), and realize relevant functions through annotation.
2.have SQLite All operation functions of the database (all operation and version upgrade of database tables)....)

The Room contains three main components:
Room Database: the main access point of the underlying connection, which is used to create the database.
Data Access Objects DAO: there will be a series of method declarations for CRUD of the database in the DAO
Entity class: it is the corresponding representation of the object and the data table. The entity class is designed and finally transformed into the corresponding data table

Description of main roles
• Entities: entity class, which represents the data of database tables.
• DAO: data operation interface. In DAO, there will be a series of method declarations for CRUD of the database.
• Database: Database Holder & Database version manager.
• Room: the creator of the database & the specific implementer responsible for updating the database version

Note description

1. Bean (entity)

• @ Entity: Entity class of the data table.
• @ PrimaryKey: each entity class needs a unique identifier.
• @ ColumnInfo: field name in the data table.
• @ Ignore: label attributes that do not need to be added to the data table.
• @ Embedded: refers to other entity classes in the entity class.
• @ ForeignKey: foreign key constraint.

//Sometimes, some fields or groups of fields in the database must be unique You can force the unique attribute to be completed by setting the unique attribute of annotation @ Index to true
@Entity(tableName = "user", indices = {@Index(value = {"name"}, unique = true)})

@NonNull//  Indicates that the return value of a parameter, member variable or method is never null

//@The ForeignKey annotation defines its relationship with the entity User
* Foreign keys are very powerful because they allow you to specify what to do when the reference entity is updated For example, you can tell SQLite to delete all books for users,
* When the corresponding User instance is deleted, the User is associated by Book by declaring onDelete = CASCADE in the @ ForeignKey annotation
* Note: SQLite operates @ Insert(onConflict = REPLACE) as a set of REMOVE and REPLACE instead of a separate UPDATE operation This method of replacing conflicting values can affect your foreign key constraints
 * */
@Entity(foreignKeys = @ForeignKey(entity = User.class,
        parentColumns = "id",
        childColumns = "user_id"))

Room will use all the fields of the class annotated with @ Entity to create the columns of the table. If some fields do not want to be stored, use @ Ignore to annotate the field

By default, Room uses the class name as the table name and the field name as the column name. We can define our own table name through the tableName attribute of @ Entity and our own column name through the name attribute of @ ColumnInfo.

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "_id")
    private int id;

Room uses the class name as the Table name of the database by default. You can set the name of the Table through the tableName property of @ Entity. (Note: in SQLite, Table names are case insensitive.)

Room uses the field (file) name as the default column name in the database. You can set the column name by adding the @ ColumnInfo annotation to the file.

Primary key
Each Entity defines at least one field as the primary key. You can set the AutoID by setting the autoGenerate property of @ PrimaryKey to true. If the Entity has a composite primary key, you can use the primaryKeys attribute of @ Entity to specify the primary key.

Each Entity must set at least one Field as the primary key. Even if there is only one Field, it needs to be set as the primary key.
There are two ways to set the primary key:
1. The annotation @ PrimaryKey can be used to set a single primary key.
If you need Room to automatically assign IDs to Entity, you can set the autoGenerate property of @ PrimaryKey
2. Use the primaryKeys attribute of annotation @ Entity to set single primary key and composite primary key.

Set Ignore fields
By default, Room creates a column for each Field in the Entity. If there are fields in the Entity that do not need to be persisted, you can add @ Ignore annotation to them.

If the subclass does not need to persist the Field in the parent class, it is more convenient to use the ignoredColumns attribute of @ Entity.

Indexing and uniqueness
Adding indexes to the database can speed up the query. In Room, you can add indexes through the indexes attribute of @ Entity.

Sometimes, you need to ensure that a field or a group of fields is unique. Uniqueness can be ensured by setting the unique attribute of @ Index to true. In the following example, first is prevented_ Name and last_ The two columns of name have the same data at the same time

SQLite is a relational database. You can specify the relationship between different objects. Although most ORM class libraries allow objects to refer to each other, Room explicitly prohibits this.
Although direct relationships cannot be used, Room still defines foreign keys between two entities

For example, there is another entity Book. You can use the @ ForeignKey annotation to define the relationship with the User.

Foreign keys are very useful because you can specify how to handle when the referenced entity changes. For example, if the onDelete attribute value of @ ForeignKey is CASCADE, all book s with the same userId will be deleted if user is deleted.
nested object
Room provides an annotation @ Embedded, which allows you to embed another entity in one entity. The created table uses all the fields of the current entity and the Embedded entity, so we can modify the User entity above

When multiple classes are nested in a class and these classes have the same fields, you need to call the @ Embedded attribute prefix to add a prefix, and the generated column name is prefix + column name

2. Dao (database operation)

DAO(Data access object)

In the Room persistence library, use data access objects (Daos) to access App data

A DAO can be an interface or an abstract class. If it is an abstract class, it can have a constructor that receives only one RoomDatabase parameter. At compile time, Room creates a concrete implementation for each DAO.

Note: Room does not support database access on the main thread unless allowMainThreadQueries() is called on the constructor because it may lock the UI for a long time. However, asynchronous queries (queries that return LiveData or Flowable instances) are not subject to this rule because they do asynchronous queries in the background thread when needed.

• @ Dao: label the class of database operation.
• @ Query: contains all Sqlite statement operations.
• @ Insert: Insert the annotation database.
• @ Delete: Delete the annotation database.
• @ Update: Update the annotation database.

Data access objects (DAOs)

@The parameter accepted by Query query is a string, so we can also use @ Query annotation to execute directly with SQL statements like deletion or update

@Query("delete  from user where userId = :id ")
fun deleteUserById(id:Long)

@Query("update  user set userName = :updateName where userID =  :id")
fun update(id: Long, updateName: String)

When we create a Dao method and use the @ Insert annotation, Room will Insert all parameters into the database in one transaction.

onConflict is used to specify the policy when a conflict occurs. For example, set the unique attribute of @ Index to true. When a conflict occurs, it is onconflictstrategy by default Abort will cause crash, which is set to onconflictstrategy Replace to replace old data in case of conflict. For other conflict strategies, you can read SQL as underlying by SQLite.

In addition to setting the method return value of @ Insert annotation to void, you can also set the method return value to long, Long, Long [] or List. If the parameter is a single entity, Long or Long is returned, which is the rowId of the new entry to be inserted. If the parameter is a set or multiple parameters, Long [] or List will be returned

to update
Using the @ Update annotation method, you can use the value of the parameter entity to Update the row whose primary key value is the same as the primary key of the parameter entity.
@The Update annotation method can also return int, indicating the number of rows affected.

Using the @ Delete annotation method, you can Delete rows with the same primary key value as the primary key of the parameter entity


@Query is an important annotation in DAO class. It allows read and write operations on the database. Each @ query method is verified at compile time; Therefore, if there is a query problem, a compilation error will occur instead of a runtime error

During compilation, Room also verifies the return value of the query. If the field name in the return object does not match the corresponding column name in the query, it will be notified in one of the following two ways: (mentioned in the subset of the return column in 3.4.3 below)
• if only part of the Field name matches, Warning will be displayed.
• if no Field name matches, Error will be displayed.

Simple query

@The value of query is an SQL statement and can be executed by SQLite@ Query supports query statements, delete statements and update statements, and does not support insert statements.

Room will check at compile time. When the code contains syntax errors or the table does not exist, room will display an error message at compile time

Query with parameters
In most cases, parameters need to be passed into the query to perform filtering operations. For example, only users older than a certain age need to be displayed. In this case, we can use method parameters.

At compile time, Room uses minAge method parameters to match: minAge binding parameters. If there are matching errors, a compilation error occurs.

You can also pass multiple parameters or reference them multiple times in a query.

When querying, the parameters passed can also be a collection. Room knows when a parameter is a collection and automatically expands it at run time according to the number of parameters provided.

Incoming parameters
What should we do if we want to get the user with the specified id@ The value of Query supports adding binding parameters, which must find the matching method parameters and obtain the value of the method parameters.

Incoming parameters
What should we do if we want to get the user with the specified id@ The value of Query supports adding binding parameters, which must find the matching method parameters and obtain the value of the method parameters.

In this example, the binding parameter: minAge matches the method parameter minAge
In addition, Room allows you to pass in a parameter set

Returns a subset of columns
In most cases, you only need to get a few fields of the entity. For example, your ui might display only the first and last names of users, not the details of each user. By getting only the columns you need, you can save resources and query faster.

Room allows any java object to be returned as long as the result of the query can be mapped to the returned object. For example, you can create the following java objects to get the first and last names of users.

Original link

Room allows any Java based object to be returned as long as the result column set can be mapped to the returned object. For example, you can create the following common Java objects (POJOs) to get the user's first name and last name:
Note: POJO can also use @ Embedded annotation.

Observable query
If you want the UI of App to update automatically when the data changes, you can return a value of LiveData type in the query method. Room will generate all the necessary code to update the LivaData object when the database changes

Fuzzy Lookup query with wildcard
There are two methods: one is to splice with double vertical bars, and the other is to splice%% when passing parameters
@Query("SELECT * FROM tb_use WHERE Name LIKE '%' || :name" || '%')

Responsive query of RxJava

Room supports returning the following RxJava2 type values:
 @ Query method: supports returning Publisher, Flowable and Observable values.
 @ Insert, @ Update and @ Delete methods: Room 2.1.0 and above supports returning values of types Completable, Single and may.
It needs to be in App build Add dependency on the latest rxjava2 version in gradle file:

Room can also return Publisher and Flowable objects of RxJava2. To use this feature, you need to add Android to gradle arch. persistence. room:RxJava2

Return to Cursor directly
Room can also return the Cursor object directly

Query multiple tables

     * For simplicity, we only store one user information in the table
     * This query statement can get all users, but we only need the first one
     * @return
    @Query("SELECT * FROM Users LIMIT 1")
    Flowable<User> getUser();
     * Want to insert a User object into the database
     * If it already exists in the database, replace it
     * @param user
     * @return
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    Completable insertUser(User user);

     * Clear all data
    @Query("DELETE FROM Users")
    void deleteAllUsers();
     * Read information from database
     * Since the reading rate may be much higher than the observer processing rate, the back pressure Flowable mode is used
     * Flowable:
    Flowable<User> getUser();

     * Write data to database
     * Update if data already exists
     * Completable It can be regarded as the Runnale interface of RxJava
     * However, it can only call onComplete and onError methods, and cannot perform map, flatMap and other operations
     * Completable:
    Completable insertOrUpdateUser(User user);

Original link

3. Database (database persistence)

The creation and upgrade of inheritance database are handled in this class
 @ Database: label the class of Database persistence

    defaultConfig {
        //Specify room The file path generated by schemalocation outputs sql statements to facilitate the writing of migration
        javaCompileOptions {
            annotationProcessorOptions {
                arguments += ["room.schemaLocation":

Example code:

//Classes that manage data creation and upgrade
@Database(entities = {RecordMarksBean.class},version = 2)
public abstract class RecordMarksBeanRoomDatabase extends RoomDatabase {
     * Database name
    private static final String DB_NAME = "room_sound_recorder.db";

    public abstract RecordMarksBeanDao getRecordMarksBeanDao();

    private static volatile RecordMarksBeanRoomDatabase INSTANCE;

    public static RecordMarksBeanRoomDatabase getDatabase(final Context context) {
        if (INSTANCE == null) {
            synchronized (RecordMarksBeanRoomDatabase.class) {
                if (INSTANCE == null) {
                    // Create database here
                    //Create a RoomDatabase object in the context, and the full name of the database is "word_database".
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                            RecordMarksBeanRoomDatabase.class, DB_NAME)
        return INSTANCE;

     * Database version upgrade Migration12 upgrade to the second version
    static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        public void migrate(SupportSQLiteDatabase database) {

//            //Database migration, create a new table
//            database.execSQL("CREATE TABLE room_mark_table (_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
//                    "file_id INTEGER,filepath TEXT,marks TEXT,uripath TEXT)");
            // Add new fields to the old table
            database.execSQL("ALTER TABLE room_mark_table "
                    + " ADD COLUMN  volumelist TEXT");
            //In table device_ Add TEXT field mimc in init
//            database.execSQL("ALTER TABLE mark_table"
//                    + " ADD COLUMN uripath TEXT");
            //Copy the data of the original table to the new table
//            database.execSQL("INSERT INTO room_mark_table (_id,file_id,filepath,marks)" +
//                    "SELECT _id,file_id,filepath,marks FROM mark_table");
            //Delete the original data table
//            database.execSQL("DROP TABLE mark_table");
            //The new table with the same name is the old data table
//            database.execSQL("ALTER TABLE mark_table_temp RENAME to mark_table");

//            private static final String CREATE_TABLE = "create table  " + SOUNDRECORDER_MARKS_TABLE + " (" +
//                    "  " + _ID + "  Integer primary key autoincrement, " + FILE_ID + " integer, "
//                    + FILEPATH + "  text,  " + MARKS + " text)";


Use type converter
Room supports strings, basic data types and their wrapper classes, but how to store them if they are not basic data types? For example, our User object has a birthday field of type Date. How should we store it. Room provides @ TypeConverter, which can convert non storable types to types that room can store.

The above example defines two methods. Room can call dateToTimestamp method to convert date into Long type for storage, or convert the obtained Long into Date object during query.

To have Room call the converter, add the converter to the AppDatabase using the @ TypeConverters annotation.

Database upgrade
After the app is released, we may add new tables or modify the structure of the original tables, which requires upgrading the database. Room provides a Migration class to migrate the database. For each Migration, you need to specify the start version and end version in the constructor. During runtime, room will execute the migrate() method of each Migration in the order of providing versions to upgrade the database to the latest version.

Note: in order for the migration logic to execute normally, please use the full query instead of the constant representing the query.

Basic use

1.Lib reference

module build Add the following dependencies to gradle

implementation ''
implementation ""
annotationProcessor ''
annotationProcessor ''

The first two sentences are required, and the last part is optional. Click here to view the latest dependency version number and dependency declaration method.

Room components

Room has three main components
From the structure of the figure above, at least Room Database, DAO and Entity need to be defined. The documents on the official website also point out that we need to create these three files before we can officially use Room.
Room Database: define an abstract class and inherit Room Database
DAO: define an interface class
Entity: ordinary Java Bean class
• Database: it contains the Database holder and serves as the main access point of the underlying connection to the data persistently associated with the App.
Classes annotated with @ Database should meet the following conditions:
• is an abstract class inherited from RoomDatabase.
• include a list of entities associated with the database in the annotation.
• contains an abstract method with 0 parameters and returns the class annotated with @ Dao.
At runtime, you can call room Databasebuilder () or room Inmemorydatabase builder() gets the Database instance.
• Entity: refers to the Table in the database.
• DAO: contains methods for accessing the database.

Relationship between components of Room
The general usage of Room is as follows:
• App obtains Database access objects (DAO) related to the Database through the Database of Room.
• then, App uses DAO to obtain Entity from the database and save the changes of Entity to the database.
• finally, APP uses Entity to obtain and set the data of tables in the database
room is divided into three parts:
Entity: database entity. The system creates a database according to the entity class, which specifies the necessary database settings such as PrimaryKey, column name and table name
Dao: Database access object: defines some operations to operate the database, such as adding, deleting, modifying and querying
Database: it can be considered as a complete database. A complete database includes database information and database operations, that is, Entity and Dao

It is worth noting that if the parameter of Entity() is empty, the system will take the class name as the table name of the database when creating the database. If you want to customize the table name, you can directly enter the parameter in Entity():
@Entity(tableName = "yourTableName")

Database elements
Primary key: each Entity defines at least one field as the primary key. You can set the AutoID by setting the autoGenerate property of @ PrimaryKey to true. If the Entity has a composite primary key, you can use the primaryKeys attribute of @ Entity to specify the primary key.

Create Entity class
Create Dao class
Create database class

We mark this class as database class through @ Database(). In its parameters, we can define:
 entities: pass in class objects of all entities;
 version: database version number.
 exportSchema: set whether to export database schema. The default value is true. It needs to be in build Settings in gradle:
When the database changes, the database version number will be changed to better backup and restore. We can't use it here, so we just design a value

 it must be an abstract class and an extension roomdatabase.
 the annotation of the class header must contain the list of entities associated with the database (the class corresponding to Entity).
 contains an abstract method with 0 parameters and returns the class annotated with @ Dao.

Use database
We are finally able to operate our database. However, all operations must be completed in the background Thread. You can do this by using AsyncTask, Thread, Handler, RxJava or other methods

Database upgrade

Using Room to reference complex data
Room provides the function to support the transformation between cardinality data type and wrapper type, but does not allow object reference between entities

Use type converter

Sometimes, the application needs to use a custom data type, and the value of this data type will be saved in the database column To add this custom class

Next, add the @ TypeConverters annotation to the AppDatabbase class, and then Room can use the converter on each entity and DAO defined in the AppDatabase

//DAO: contains methods for accessing the database
public interface UserDao {
    // OnConflictStrategy.REPLACE means that if there is already data, it will be overwritten
    //The data is matched through the primary key, that is, uid, not the whole user object
    //Return the Long data representation, and insert the primary key value (id) of the entry

    @Query("SELECT * FROM user")
    List<User> getAllUsers();

    // LiveData is a data holding class that can be observed. It caches or holds the latest data. When the data changes, its observer is notified.
    // LiveData is life cycle aware. UI components only observe relevant data and will not stop or resume observation.
    // LiveData automatically manages all of this because it is aware of relevant lifecycle state changes as it observes.
    @Query("SELECT * FROM user")
    LiveData<List<User>> getAllUser();

    @Query("SELECT * FROM user WHERE id=:id")
    User getUser(int id);

    @Query("SELECT * FROM user WHERE name=:name")
    User getUser(String name);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    List<Long> insert(User... users);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    Long insert(User user);

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    List<Long> insert(List<User> userLists);

    int update(User... users);

    int updateAll(User... user);

    int updateAll(List<User> user);

    int delete(User user);

    int deleteAll(List<User> users);

    int deleteAll(User... users);

    //Return to Publisher and Flowable in RxJava2
    @Query("SELECT * from user where name = :name LIMIT 1")
    Flowable<User> getUserByName(String name);

    //multi-table query
    @Query("SELECT * FROM book "
            + "INNER JOIN user ON = book.user_id "
            + "WHERE = :userName")
    List<Book> findBooksByUserName(String userName);


The following is the official introduction document of Android Room:

Room persistence Library (brief introduction to room Library)

Save data in a local database using Room

Android Room with a View - Java

Reason analysis of database table structure cannot be viewed using ROOM

There are three database files generated using room: db file db SHM file db wal file.

  1. DB wal: starting with version 3.7.0, SQLite supports a new transaction control mechanism called "pre write log" or "wal". When the database is in wal mode, all connections to the database must use wal. A specific database will use rollback logs or wal, but not both. The wal is always in the same directory as the database file and has the same name as the database file, but the string "- wal" is appended.

  2. DB shm: conceptually, wal index is shared memory, although the current VFS implementation uses a mapping file for wal index. The mapping file is located in the same directory as the database and has the same name as the database, followed by "- shm" suffix. Because wal indexes are shared memory, SQLite does not support journals on network file systems when clients are located on different machines_ mode=WAL. All users of the database must be able to share the same memory

When you want to open the corresponding database to view the table structure, you should put it at the same time db,. db-shm,. Put the three DB wal files in the same folder, and then import the corresponding files into the database viewing software DB file to view the database

Reference materials:
Detailed tutorial of Android Jetpack Room

Keywords: Android SQLite

Added by BobcatM on Wed, 09 Mar 2022 11:08:16 +0200