After being tortured by endless SQLite template code, I found that Google's Room encapsulation seems to be a good choice, but the extensive use of annotations makes developers who are not familiar with SQLite do not know why if they start with Room directly at the beginning. Here are my learning notes.

1. Review the direct operation of SQLite

I have a habit when operating the database, that is, I don't use the encapsulated api to write SQL directly. Because most APIs for operating databases are too cumbersome. If you write SQL directly to the database, you only need to use the three classes SQLiteOpenHelper, SQLiteDatabase and Cursor and the two main methods of execSQL(), rawQuery() under SQLiteDatabase

• SQLiteOpenHelper

  The auxiliary class of SQLite database is used to manage database creation and version control. Generally, we also encapsulate database operations in this class

  • onCreate()

    Override the table and initialization content used to create the database. This method will only be called when the entered database name does not match the database

  • onUpgrade()

    Rewriting is used for database version upgrade. The content of this method will become quite cumbersome in long-term projects

• SQLiteDatabase

  The instance can be obtained through the writableDatabase and readableDatabase method under SQLiteOpenHelper
  This class contains a series of encapsulated API s for operating SQLite, and also allows direct execution of SQL statements

  • execSQL()

    Used when executing create, insert, delete and update

    The following is to book_ The operation of inserting a row into the type table, where? Is a placeholder for the passed parameter

    // id is INTEGER type and type is VARCHAR type. Corresponding to Java Int and String respectively
    db.execSQL("INSERT INTO 'book_type'('book_type_id','book_type_str') value(?,?)"
                , arrayOf(book_type_id, book_type_str))// Java uses array listOf()
    

  • rawQuery()

    Used when performing a SELECT. If the query statement is operated by api, it will become extremely cumbersome and can not play the role of implicit implementation. It's better to write SQL directly with rawQuery(). The following is the operation of querying all rows of bookname starting with bookname from the book table. After querying, a table pointer of Cursor type will be returned

    val cursor = db.rawQuery("SELECT * FROM book WHERE book.book_name LIKE '${bookName}%'")
    

• Cursor

  A data pointer to a specific row of the table. You can use moveToFirst() to point to the first row of the table, and then call moveToNext() repeatedly to traverse all table entries

  if(cursor.moveToFirst()){
      do{
          // Use getColumnIndex() and the corresponding getXXX method to retrieve the contents of the corresponding column
          val book_name = cursor.getString(cursor.getColumnIndex("book_name"))
      }while(cursor.moveToNext())
  }
  

2. Overview

Different from the api encapsulated by the original SQLiteDatabase, Room perfectly encapsulates in depth by introducing a set of annotation framework, and developers don't have to write a pile of template code.

When you interact with Room and SQLite, you need to interact with three classes

• Entity

  Defines the entity of the SQLite table

• DAO (Data Access Object)

  Define the methods used to interact with the database, namely INSERT, DELETE, UPDATE, SELECT

• RoomDatabase

  Similar to SQLiteOpenHelper, the auxiliary class of SQLite

3. Entity

Entity is used to define the mapping relationship between Java layer and SQLite layer. The Java layer contains case sensitive fields, which will be mapped to corresponding case insensitive fields in SQLite layer. If you need to define an alias, you need to use the @ ColumnInfo annotation

Defining an Entity requires a series of annotations

• @Entity

  The class with @ Entity will be mapped to a table, and the annotation has two common properties

  • tableName is used to customize the alias of the table
  • primaryKeys is used to define a federated primary key
  • ignoredColumns is used to define fields that will not be mapped
  • Indexes are used to define indexes

• @PrimaryKey

  Define a single attribute as the primary key

  • autoGenerate is used to set primary key auto increment

• @ColumnInfo

  • name alias used to customize the column

• @Ignore

  Define fields that will not be mapped, such as BitMap in the cache held by the user, which does not need to be stored in the database

Suppose we need to define the table of the following SQLite statement

# eg.1
CREATE TABLE `writers`(
    `writer_name` VARCHAR,
    `writer_id` INTEGER PRIMARY KEY AUTOINCREMENT
);
# eg.2
CREATE TABLE `book_types`(
    `book_type_str` VARCHAR,
    `book_type_id` INTEGER PRIMARY KEY AUTOINCREMENT
);
# eg.3
CREATE TABLE `book`(
    `book_type_id` INTEGER,
    `writer_id` INTEGER,
    `book_name` VARCHAR,
    `book_isbn` VARCHAR,
    `book_id` INTEGER PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX `book_type_id` ON `book`(`book_type_id`);
CREATE INDEX `writer_id` ON `book`(`writer_id`);

The corresponding Entity can be

// eg.1 the role of ignored columns is demonstrated here
@Entity(tableName="writers", 
        ignoredColumns=arrayOf("picture"))
data class Writer(
    @ColumnInfo(name = "writer_name")
    val writerName:String?,
    var picture:Bitmap?=null,
    @ColumnInfo(name = "writer_id")
    @PrimaryKey(autoGenerate=true) 
    var writerId:Int = 0
)
// eg.2
@Entity(tableName="book_types")
data class BookType(
    @ColumnInfo(name = "book_type_str") 
    val bookTypeStr:String?
    @Ignore 
    val typePic:Bitmap?,
    @ColumnInfo(name = "book_type_id")
    @PrimaryKey(autoGenerate=true) 
    val bookTypeId:Int = 0
)
// eg.3
@Entity(tableName="books",
        indices=arrayOf(Index(value=["book_type_id", "writer_id"],
        unique=true)))
data class Book(
    @ColumnInfo(name = "book_type_id")
    val bookTypeId:Int,
    @ColumnInfo(name = "writer_id")
    val writerId:Int,
    @ColumnInfo(name = "book_name")
    val bookName:String,
    @ColumnInfo(name = "book_isbn")
    val bookIsbn:String,
    @PrimaryKey(autoGenerate=true) 
    @ColumnInfo(name = "book_id")
    val bookId:Int = 0
)

When using SQLite, we often define a row of a table as a separate JavaBean according to the column name. This JavaBean can be mapped into CREATE statements only by adding a few annotations, which is very convenient for development

3.1 provide full table retrieval support

If you directly operate SQLite, to provide FTS (Full Text Search), you need to manually create a series of virtual tables.

For versions above Room 2.1.0, you can add @ Fts3 or @ Fts4 comments and set the primary key with the INTEGER type name of rowid

@Fts4(languageId = "lid")
@Entity(tableName = "users")
data class User(
    // ...
    @ColumnInfo(name = "lid") val languageId: Int
)

4. DAO

4.1 DML mapping

Room uses the annotation processor to map the DML of the database, that is, insert, update and delete

@DAO
interface writerDao{
    // The returned Long is the ordinal number of the row inserted
	@Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertWriters(vararg writers: Writer):List<Long>
    
    // The Int returned is the number of rows updated
    @Update
    fun updateWriters(vararg writers: Writer):Int
    
    // The Int returned is the number of rows deleted
    @Delete
    fun deleteWriters(vararg writers: Writer):Int
}

4.2 DQL mapping

Room further encapsulates the return value of the Query statement

When using SQLite, if we need to return some columns of the table as an object, we need to define a JavaBean and manually assign values after obtaining the Cursor. After using Room, we can directly use the correct annotation to the JavaBean as the return value, and the template code is handed over to the annotation processor of Room.

If you need to use the traditional Cursor api, you can change the class returned by Dao to Cursor

data class BaseBook(
    @ColumnInfo(name="book_name") val bookName:String?,
    @ColumnInfo(name="book_isbn") val bookIsbn:String?
)

@Dao
interface BookDao{
    @Query("SELECT * FROM book WHERE book.writer_id = (SELECT writer.writer_id FROM writer WHERE writer.writer_name IN (:writerNames));")
    fun loadBaseBooksWithWriterName(writerNames:List<String>):List<BaseBook>
}

5. RoomDatabase

In SQLite, we use the following methods to create DatabaseOpenHelper and get the database

// Suppose our customized DatabaseOpenHelper is AppDatabaseHelper
// Here you create a bookbase Database named dB with version number 1
val dbHelper = AppDatabaseHelper(this, "BookBase.db", 1)
val writableDb = dbHelper.writableDatabase

Then a series of methods are called to operate the writableDb object. The creation of database tables and the methods of database operation are very scattered
When defining RoomDatabase, we need to directly define the tables contained in the database and the allowed operation methods at a glance

Because the cost of creating a RoomDatabase instance is quite high, it is similar to using SQLite. When instantiating an AppDatabase object, you should follow the singleton design pattern to keep only one RoomDatabase globally

@Database(entities = arrayOf(Writer::class, BookType::class, Book::class), version = 1)
abstract class AppDatabase : RoomDatabase(){
    abstract fun writerDao():WriterDao
    abstract fun bookDao():BookDao
}
// Create RoomDB using the class with the Database annotation
val db = Room.databaseBuilder(
    applicationContext,
    AppDatabase::class.java, "BookBase.db"
)
// Call example
db.writerDao().insertWriters(Writer("myName"))

6. Object mapping

nested object

When querying addresses and users, it is assumed that the users table has both user and address columns. If you want the user returned by the query method to hold an address, in the traditional SQLite query, you need to obtain the cursor, manually create the object and assign the value manually. If there are multiple similar requirements, you can write a set of template code generator to complete this work.

Using the annotation @ Embedded of Room can easily return nested objects

data class Address(
    val street:String?,
    val state:String?,
    val city:String?,
    @ColumnInfo(name="post_code") val postCode:Int
)

@Entity
data class User(
    @PrimaryKey val id:Int,
    val firstName:String?,
    @Embedded val address:Address?
)

When using this entity, the generated user table will have seven columns: ID, firstname, address, street, state, city and postcode.

There are more relationship definitions on the official website, but other mappings are more complex. Although you can avoid writing SQL statements in DAO, they are not readable. You can visit them if you are interested Official website.