Room框架下从关联表填充@Ignore字段的更优实现方案

存在item和purchase两张关联数据表，purchase实体类包含被@Ignore标记的itemName字段，需要从item表获取对应itemName值填充该字段。
现有实现方案需要先从数据库读取全量item数据，再通过比对purchase.itemOwnerID与item.itemID完成字段赋值，全量加载商品数据存在明确性能隐患，现有实现代码如下：

现有UI层实现 #

LaunchedEffect(key1 = true) {
    sharedViewModel.requestAllPurchases()
    sharedViewModel.requestAllItems() // 全量请求所有商品数据
}

val allPurchases by sharedViewModel.allPurchases.collectAsState()
val allItems by sharedViewModel.allItems.collectAsState() // 收集全量商品数据

if (allPurchases is RequestState.Success && allItems is RequestState.Success) {
    (allPurchases as RequestState.Success<List<Purchase>>).data.forEach { purchase ->
        (allItems as RequestState.Success<List<Item>>).data.first { it.itemID == purchase.itemOwnerID } // 内存中比对关联
            .apply {
                purchase.itemName = itemName
                purchase.costPrice = salePrice
            }
    }
}

现有实体类定义 #

@Entity(tableName = "items", indices = [Index(value = ["itemName"], unique = true)])
data class Item(
    @PrimaryKey(autoGenerate = true)
    var itemID: Int,
    var categoryOwnerID: Int = 0,
    var itemName: String,
    var costPrice: Int,
    var salePrice: Int,
) {
    @Ignore
    var desiredQuantity: Int = 0
}

@Entity(tableName = "purchases")
data class Purchase(
    @PrimaryKey(autoGenerate = true)
    var purchaseID: Int,
    var itemOwnerID: Int,
    var quantity: Int,
    var soldPrice: Int,
) {
    @Ignore
    var itemName: String = ""
}

sealed class RequestState<out T> {
    object Idle : RequestState<Nothing>()
    object Loading : RequestState<Nothing>()
    data class Success<T>(val data: T) : RequestState<T>()
    data class Error(val error: Throwable) : RequestState<Nothing>()
}

现有Dao层实现 #

@Query("select * from items order by itemName asc")
fun getAllItems(): Flow<List<Item>>

@Query("select * from purchases order by purchaseID asc")
fun getAllPurchases(): Flow<List<Purchase>>

现有Repository层实现 #

val getAllItems: Flow<List<Item>> = itemDao.getAllItems()
val getAllPurchases: Flow<List<Purchase>> = itemDao.getAllPurchases()

现有SharedViewModel层实现 #

private var _allItems = MutableStateFlow<RequestState<List<Item>>>(RequestState.Idle)
val allItems: StateFlow<RequestState<List<Item>>> = _allItems

fun requestAllItems() {
    _allItems.value = RequestState.Loading
    try {
        viewModelScope.launch {
            repository.getAllItems.collect {
                _allItems.value = RequestState.Success(it)
            }
        }
    } catch (e: Exception) {
        _allItems.value = RequestState.Error(e)
    }
}

private var _allPurchases = MutableStateFlow<RequestState<List<Purchase>>>(RequestState.Idle)
val allPurchases: StateFlow<RequestState<List<Purchase>>> = _allPurchases

fun requestAllPurchases() {
    _allPurchases.value = RequestState.Loading
    try {
        viewModelScope.launch {
            repository.getAllPurchases.collect {
                _allPurchases.value = RequestState.Success(it)
            }
        }
    } catch (e: Exception) {
        _allPurchases.value = RequestState.Error(e)
    }
}

核心思路是将关联逻辑下推到数据库层执行，避免全量加载商品数据到内存做比对，Room原生支持SQL JOIN查询，直接在查询阶段拼接需要的字段，性能远高于内存比对方案。

第一步：定义关联查询结果模型 #

不需要修改原有Item和Purchase实体类，新建专门的返回模型承载采购记录+关联的商品字段：

data class PurchaseWithItemInfo(
    // 采购记录原有字段
    val purchaseID: Int,
    val itemOwnerID: Int,
    val quantity: Int,
    val soldPrice: Int,
    // 关联查询从item表取的字段
    val itemName: String,
    val costPrice: Int
)

第二步：Dao层新增JOIN查询 #

直接在SQL中做左连接，一次性查出需要的所有数据，不需要单独查询全量商品：

@Query("""
    SELECT p.*, i.itemName, i.salePrice as costPrice 
    FROM purchases p 
    LEFT JOIN items i ON p.itemOwnerID = i.itemID
    ORDER BY p.purchaseID ASC
""")
fun getAllPurchasesWithItemInfo(): Flow<List<PurchaseWithItemInfo>>

第三步：调整Repository层 #

新增对应查询的暴露：

val getAllPurchasesWithItemInfo: Flow<List<PurchaseWithItemInfo>> = itemDao.getAllPurchasesWithItemInfo()

第四步：调整ViewModel层 #

删除原来单独请求全量商品的逻辑，只需要监听关联查询结果即可：

private var _allPurchasesWithInfo = MutableStateFlow<RequestState<List<PurchaseWithItemInfo>>>(RequestState.Idle)
val allPurchasesWithInfo: StateFlow<RequestState<List<PurchaseWithItemInfo>>> = _allPurchasesWithInfo

fun requestAllPurchases() {
    _allPurchasesWithInfo.value = RequestState.Loading
    try {
        viewModelScope.launch {
            repository.getAllPurchasesWithItemInfo.collect {
                _allPurchasesWithInfo.value = RequestState.Success(it)
            }
        }
    } catch (e: Exception) {
        _allPurchasesWithInfo.value = RequestState.Error(e)
    }
}

第五步：简化UI层逻辑 #

删除原来收集全量商品、内存比对赋值的代码，直接使用关联查询返回的结果即可：

LaunchedEffect(key1 = true) {
    sharedViewModel.requestAllPurchases()
}

val allPurchasesState by sharedViewModel.allPurchasesWithInfo.collectAsState()

when(allPurchasesState) {
    is RequestState.Loading -> { /* 展示加载态 */ }
    is RequestState.Error -> { /* 展示错误态 */ }
    is RequestState.Success -> {
        val purchaseList = (allPurchasesState as RequestState.Success<List<PurchaseWithItemInfo>>).data
        // 直接使用purchaseList中的itemName、costPrice字段渲染UI，无需额外赋值
    }
    RequestState.Idle -> {}
}

方案优势 #

• 不需要全量加载item表所有数据，数据库仅返回采购记录关联到的商品字段，传输和加载的数据量大幅降低
• 关联逻辑在SQLite底层执行，比上层内存循环比对效率高1~2个数量级，表数据量越大性能差距越明显
• 移除了UI层手动遍历赋值的逻辑，不会出现商品信息更新后采购记录关联字段不同步的问题
• 精简了数据流链路，去掉冗余的全量商品数据收集逻辑，代码可维护性更高

内容的提问来源于stack exchange，提问作者Kinyo356