Kotlin+coroutine+room+mvvm

一、依赖包引入

//ROOM数据库相关
implementation"androidx.room:room-runtime:${versions.room_version}"
kapt"androidx.room:room-compiler:${versions.room_version}"
// optional - Kotlin Extensions and Coroutines support for Room
implementation"androidx.room:room-ktx:${versions.room_version}"
implementation "androidx.lifecycle:lifecycle-livedata-ktx:2.2.0"

ext {
    versions = [
     'room_version'       :"2.4.3"
    ]
}

二、数据库相关类定义
1. 数据实体

/**
 * 定义了一个 Pocketbook 数据实体。Pocketbook 的每个实例都代表应用数据库中 Pocketbook 表中的一行
 * Created by 2022/9/19
 * Copyright (c) 2022 . All rights reserved
 */
@Entity
data class Pocketbook(
    @PrimaryKey val id: Int? = null,
    @ColumnInfo(name = "amount") val amount: Double? = null,
    @ColumnInfo(name = "reason") val reason: String? = null,
    @ColumnInfo(name = "record_type") val recordType: String? = null,//记录类型 EXPENDITURE 支出，INCOME 收入
    @ColumnInfo(name = "create_time") val createTime: Long? = null,
)

2. 数据访问对象 (DAO)

/**
 * 数据访问对象 (DAO):定义了一个名为 UserDao 的 DAO。UserDao 提供了应用的其余部分用于与 user 表中的数据交互的方法
 * 为防止查询阻止界面，Room 不允许在主线程上访问数据库
 * Created by 2022/9/19
 * Copyright (c) 2022 . All rights reserved
 */
@Dao
interface PockbookDao {

    @Query("SELECT * FROM pocketbook WHERE record_type = :type AND create_time >= :createTimeStart AND create_time <= :createTimeEnd ORDER BY id DESC")
    suspend fun getPocketbookList(type: String, createTimeStart: Long, createTimeEnd: Long): List<Pocketbook>

    @Insert
    suspend fun insertRecord(pocketbook: Pocketbook)

    @Query("delete from pocketbook where id = :id")
    suspend fun deleteById(id: Int)

    @Query("SELECT SUM(amount) FROM pocketbook WHERE record_type = :type AND create_time >= :createTimeStart AND create_time <= :createTimeEnd")
    fun getTotalAmount(type: String, createTimeStart: Long, createTimeEnd: Long): Double
}

3. 用于保存数据库的 AppDatabase 类

/**
 * 定义了用于保存数据库的 AppDatabase 类。 AppDatabase 定义数据库配置，并作为应用对持久性数据的主要访问点
 * Created by 2022/9/19
 * Copyright (c) 2022 . All rights reserved
 */
@Database(entities = [Pocketbook::class], version = 2,exportSchema = false)
abstract class AppDatabase : RoomDatabase() {

    abstract fun getUserDao(): UserDao

    abstract fun gePockBookDao(): PockbookDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        fun getInstance(context: Context): AppDatabase {
            synchronized(this) {
                var instance = INSTANCE
                if (instance == null) {
                    instance = Room.databaseBuilder(
                        context.applicationContext,
                        AppDatabase::class.java,
                        DB_NAME
                    ).build()
                }
                return instance
            }
        }
    }
}

三、ViewModel类

class NoteBookViewModel : CoreViewModel() {

    val db = AppDatabase.getInstance(GlobalContextProvider.getGlobalContext())

    /**
     * 保存记录
     */
    val loadStateOfPockBookInsert = MutableLiveData<Boolean>()
    fun savePockBookInfo(request: RequestAddRecord) = launch {
        withIO {
            var nowMills = DateTime.now().millis
            var pocketbook = Pocketbook(
                recordType = request.recordType,
                createTime = nowMills,
                amount = request.amount?.toDouble(),
                reason = request.reason
            )
            db.gePockBookDao().insertRecord(pocketbook)
            loadStateOfPockBookInsert.postValue(true)
        }
    }

    /**
     * 获取记录列表
     */
    val loadStateOfPockBookList = MutableLiveData<List<Pocketbook>>()

    fun getPockBookList(type: String, createTimeStart: Long, createTimeEnd: Long) = launch {
        withIO {
            val cities = db.gePockBookDao().getPocketbookList(type, createTimeStart, createTimeEnd)
            loadStateOfPockBookList.postValue(cities)
        }
    }

    /**
     * 统计支出
     */
    val loadStateOfPockBookTotalPayAmount = MutableLiveData<Double>()

    fun getPockBookTotalAmount(type: String, createTimeStart: Long, createTimeEnd: Long) = launch {
        withIO {
            val amount = db.gePockBookDao().getTotalAmount(type, createTimeStart, createTimeEnd)
            loadStateOfPockBookTotalPayAmount.postValue(amount)
        }
    }

    /**
     * 统计收入
     */
    val getPockBookTotalIncomeAmount = MutableLiveData<Double>()

    fun getPockBookTotalIncomeAmount(type: String, createTimeStart: Long, createTimeEnd: Long) =
        launch {
            withIO {
                val amount = db.gePockBookDao().getTotalAmount(type, createTimeStart, createTimeEnd)
                getPockBookTotalIncomeAmount.postValue(amount)
            }
        }

    /**
     * 通过id删除记录
     */
    val loadStateOfPockBookDelete = MutableLiveData<Boolean>()

    fun deletePockBookById(id: Int) = launch {
        withIO {
            val amount = db.gePockBookDao().deleteById(id)
            loadStateOfPockBookDelete.postValue(true)
        }
    }
}

四、Activity类

 private val noteBookVM by viewModels<NoteBookViewModel>()
 noteBookVM.savePockBookInfo(it)
    //添加记录成功返回
    noteBookVM.loadStateOfPockBookInsert.observe {
      refreshAll()
   }

五、遇到的问题
1.接入ROOM,使用annotationProcessor 报错，换成kapt 即可，使用Room构建数据库出现RoomDatabase_Impl does not exist问题;
2.工具sqlitestudio-2.3.3 ，需要三个文件一起才可以打开db文件，预览数据库;
3.学习sql语句，了解Android sqllite 和其它数据库的不同，比如，不具有日期查询，需要时间戳（long）比较大小。