Room学习三：创建视图与访问数据

前言

Room是对SQLite的封装，官方推荐使用Room。该文档来自google官方文档。该文档里的代码是kotlin。

在数据库中创建视图

1. @DatabaseView创建视图

@DatabaseView("SELECT user.id, user.name, user.departmentId," +
        "department.name AS departmentName FROM user " +
        "INNER JOIN department ON user.departmentId = department.id")
data class UserDetail(
    var id: Long,
    var name: String?,
    var departmentId: Long,
    var departmentName: String?
)

2. 关联视图

@Database(entities = arrayOf(User::class),
          views = arrayOf(UserDetail::class), version = 1)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
}

访问数据

1. 插入insert, onConflict属性是冲突解决的策略

@Dao
interface MyDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertUsers(vararg users: User)

    @Insert
    fun insertBothUsers(user1: User, user2: User)

    @Insert
    fun insertUsersAndFriends(user: User, friends: List<User>)
}

也可以设置返回值为long 、long[] 或 List<Long>类型,返回的是插入第几行

2.更新update

@Dao
interface MyDao {
    @Update
    fun updateUsers(vararg users: User)
}

也可以返回int，代表更新的哪一行

3. 删除delete

@Dao
interface MyDao {
    @Delete
    fun deleteUsers(vararg users: User)
}

也可以返回int，代表被删除的行数

4. sql语句 query

@Dao
interface MyDao {
    @Query("SELECT * FROM user")
    fun loadAllUsers(): Array<User>

   @Query("SELECT * FROM user WHERE age BETWEEN :minAge AND :maxAge")
    fun loadAllUsersBetweenAges(minAge: Int, maxAge: Int): Array<User>

    @Query("SELECT * FROM user WHERE first_name LIKE :search " +
           "OR last_name LIKE :search")
    fun findUserWithName(search: String): List<User>

     @Query("SELECT first_name, last_name FROM user WHERE region IN (:regions)")
     fun loadUsersFromRegions(regions: List<String>): List<NameTuple>
}

当然，返回值也可以是lIveData

@Dao
interface MyDao {
    @Query("SELECT first_name, last_name FROM user WHERE region IN (:regions)")
    fun loadUsersFromRegionsSync(regions: List<String>): LiveData<List<User>>
}

也可以返回RxJava2类型

dependencies {
    implementation 'androidx.room:room-rxjava2:2.1.0-alpha02'
}

@Dao
interface MyDao {
    @Query("SELECT * from user where id = :id LIMIT 1")
    fun loadUserById(id: Int): Flowable<User>

    // 返回添加到数据库的数量
    @Insert
    fun insertLargeNumberOfUsers(users: List<User>): Maybe<Int>

    // 确保操作成功完成
    @Insert
    fun insertLargeNumberOfUsers(varargs users: User): Completable

    //返回删除的数量
    @Delete
    fun deleteAllUsers(users: List<User>): Single<Int>
}

返回的是一个类呢？

@Dao
interface MyDao {
    @Query(
        "SELECT user.name AS userName, pet.name AS petName " +
        "FROM user, pet " +
        "WHERE user.id = pet.user_id"
    )
    fun loadUserAndPetNames(): LiveData<List<UserPet>>
}
    data class UserPet(var userName: String?, var petName: String?)