Compose中Room @RawQuery返回Flow<List<MyEntity>>遇主线程数据库访问错误

问题核心 #

直接调用同步的@RawQuery方法会在当前线程执行数据库操作，而Compose/ViewModel层的调用默认在主线程，触发Room的主线程访问限制，导致Cannot access database on the main thread错误。

分步修复代码 #

1. Dao层修复（核心修改）

将数据库查询移至后台线程，同时修正原代码中的逻辑错误：

import androidx.room.RawQuery
import androidx.sqlite.db.SupportSQLiteQuery
import kotlinx.coroutines.flow.Flow

@RawQuery(observedEntities = [ItemEntity::class])
// 让Room直接返回Flow，自动处理后台线程查询
fun getItemsByQuery(query: SupportSQLiteQuery): Flow<List<ItemEntity>>

fun getItemsByFilters(subroomId: Long, filter:String = "", filter2:String = "", logic:String = ""): Flow<List<ItemEntity>> {
    val pl: MutableList<String> = mutableListOf()
    val al: MutableList<String> = mutableListOf(subroomId.toString())
    
    // 处理itemname过滤
    if (filter.isNotEmpty()) {
        val operator = if (filter.startsWith(">")) " LIKE ?" else " >= ?"
        pl.add("itemname $operator")
        al.add(if (filter.startsWith(">")) filter.substring(1) else filter)
    }
    // 处理itemtype过滤，修正原代码中误用filter的问题
    if (filter2.isNotEmpty()) {
        val operator = if (filter2.startsWith(">")) " LIKE ?" else " >= ?"
        pl.add("itemtype $operator")
        al.add(if (filter2.startsWith(">")) filter2.substring(1) else filter2)
    }
    
    // 修正原代码中始终用OR的逻辑错误
    val joinOperator = if (logic == "OR") " OR " else " AND "
    val placeholders = pl.joinToString(joinOperator)
    val args = al.toTypedArray()
    
    val query = SimpleSQLiteQuery(
        "SELECT * FROM item WHERE category IS NULL AND subroomId = ? AND ($placeholders) ORDER BY category, itemtype, itemname ASC",
        args
    )
    
    return getItemsByQuery(query)
}

如果你的Room版本不支持@RawQuery直接返回Flow，改用挂起函数+Flow构建器：

import androidx.room.RawQuery
import androidx.sqlite.db.SupportSQLiteQuery
import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.flow.Flow
import kotlinx.coroutines.flow.flow
import kotlinx.coroutines.flow.flowOn

@RawQuery(observedEntities = [ItemEntity::class])
suspend fun getItemsByQuery(query: SupportSQLiteQuery): List<ItemEntity>

fun getItemsByFilters(subroomId: Long, filter:String = "", filter2:String = "", logic:String = ""): Flow<List<ItemEntity>> = flow {
    // 构建query的代码同上
    val pl: MutableList<String> = mutableListOf()
    val al: MutableList<String> = mutableListOf(subroomId.toString())
    
    if (filter.isNotEmpty()) {
        val operator = if (filter.startsWith(">")) " LIKE ?" else " >= ?"
        pl.add("itemname $operator")
        al.add(if (filter.startsWith(">")) filter.substring(1) else filter)
    }
    if (filter2.isNotEmpty()) {
        val operator = if (filter2.startsWith(">")) " LIKE ?" else " >= ?"
        pl.add("itemtype $operator")
        al.add(if (filter2.startsWith(">")) filter2.substring(1) else filter2)
    }
    
    val joinOperator = if (logic == "OR") " OR " else " AND "
    val placeholders = pl.joinToString(joinOperator)
    val args = al.toTypedArray()
    
    val query = SimpleSQLiteQuery(
        "SELECT * FROM item WHERE category IS NULL AND subroomId = ? AND ($placeholders) ORDER BY category, itemtype, itemname ASC",
        args
    )
    
    emit(getItemsByQuery(query))
}.flowOn(Dispatchers.IO) // 指定数据库操作在IO线程执行

2. Repository层修复

补充缺失的return语句：

fun getitems(subroomId:Long, filter:String = "", logic:String = "", filter2:String = ""):Flow<List<ItemEntity>> {
    return when {
        filter.isEmpty() && filter2.isEmpty() -> inventoryDB.itemDao().getitems(subroomId)
        else -> inventoryDB.itemDao().getItemsByFilters(subroomId, filter, filter2, logic)
    }
}

3. ViewModel层简化

去掉多余的Flow包装，直接返回Repository的Flow：

fun items(subroomId: Long, filter:String = "", logic:String = "", filter2:String = ""): Flow<List<ItemEntity>> {
    return repository.getitems(subroomId, filter, logic, filter2)
}

4. Compose层保持不变（仅注意变量一致性）

确保key参数与更新的变量对应即可：

var filterVal by remember { mutableStateOf("") }
var filterValT by remember { mutableStateOf("") }
var logicV by remember { mutableStateOf(false) }

key(filter, filterT, logic) {
    filterVal = filter
    filterValT = filterT
    logicV = logic
}

val items by viewModel.items(
    parentId, 
    filterVal, 
    if (filterVal.isEmpty() || filterValT.isEmpty()) "" else logicV.toString(), 
    filterValT
).collectAsState(initial = emptyList())

关键说明 #

• Room的@RawQuery返回Flow时，会自动在后台线程执行查询并监听数据库变化，无需手动处理线程调度。
• 如果使用挂起函数，必须通过flowOn(Dispatchers.IO)将数据库操作切换到IO线程，避免主线程阻塞。

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