Room数据库如何用可空列表过滤？空值代表‘接受所有’

问题描述 #

我正在构建带有可选过滤器的Room数据库查询，既可以传入一组要接受的值，也可以传入null——表示“接受所有内容”。方法签名和查询语句如下：

@Dao
interface DetectionDao {
    ...

    @Query("SELECT * FROM DETECTION " +
            "WHERE :sessionIds IS NULL OR sessionId IN (:sessionIds) " +
            "AND :sources IS NULL OR source in (:sources) " +
            "AND :labels IS NULL OR label in (:labels)"
    )
    fun getFilteredDetections(sessionIds: List<String>?, sources: List<String>?, labels: List<String>?): List<Detection>
}

但调用该方法传入null时，出现如下错误：

java.lang.NullPointerException: Attempt to invoke interface method 'int java.util.List.size()' on a null object reference
    at com.dji.videostreamdecodingsample.DetectionDao_Impl.getFilteredDetections(DetectionDao_Impl.java:500)

这表明Room无法识别该参数为可空类型，请问是否有简便的解决方法？

解决方法 #

方案1：用空集合替代null，调整查询逻辑 #

Room对空集合的处理更稳定，你可以把方法参数改为非空List，调用时用空List代替null，同时修改查询语句判断集合是否为空：

@Dao
interface DetectionDao {
    @Query("SELECT * FROM DETECTION " +
            "WHERE (:sessionIds IS EMPTY OR sessionId IN (:sessionIds)) " +
            "AND (:sources IS EMPTY OR source IN (:sources)) " +
            "AND (:labels IS EMPTY OR label IN (:labels))"
    )
    fun getFilteredDetections(sessionIds: List<String>, sources: List<String>, labels: List<String>): List<Detection>
}

// 调用示例
detectionDao.getFilteredDetections(emptyList(), emptyList(), emptyList()) // 查询所有数据
detectionDao.getFilteredDetections(listOf("session_001"), emptyList(), listOf("person")) // 过滤session和标签

方案2：结合Kotlin默认参数简化调用 #

利用Kotlin的默认参数特性，给每个过滤器参数设置空集合默认值，让调用更简洁：

@Dao
interface DetectionDao {
    @Query("SELECT * FROM DETECTION " +
            "WHERE (:sessionIds IS EMPTY OR sessionId IN (:sessionIds)) " +
            "AND (:sources IS EMPTY OR source IN (:sources)) " +
            "AND (:labels IS EMPTY OR label IN (:labels))"
    )
    fun getFilteredDetections(
        sessionIds: List<String> = emptyList(),
        sources: List<String> = emptyList(),
        labels: List<String> = emptyList()
    ): List<Detection>
}

// 调用示例
detectionDao.getFilteredDetections() // 无过滤，查询所有
detectionDao.getFilteredDetections(labels = listOf("car")) // 仅过滤标签

方案3：保留可空参数，用COALESCE处理null #

如果必须保留可空参数，可以通过COALESCE函数将null转换为空集合，同时调整查询逻辑（需确保Room版本支持emptyList()函数）：

@Dao
interface DetectionDao {
    @Query("SELECT * FROM DETECTION " +
            "WHERE (COALESCE(:sessionIds, emptyList()) IS EMPTY OR sessionId IN (:sessionIds)) " +
            "AND (COALESCE(:sources, emptyList()) IS EMPTY OR source IN (:sources)) " +
            "AND (COALESCE(:labels, emptyList()) IS EMPTY OR label IN (:labels))"
    )
    fun getFilteredDetections(sessionIds: List<String>?, sources: List<String>?, labels: List<String>?): List<Detection>
}

方案4：RawQuery动态构建查询（复杂场景备选） #

如果上述方案都不满足需求，可以用@RawQuery手动拼接查询语句，灵活处理可选条件：

@Dao
interface DetectionDao {
    @RawQuery
    fun getFilteredDetections(query: SupportSQLiteQuery): List<Detection>
}

// 构建查询的工具函数
fun buildFilteredQuery(sessionIds: List<String>?, sources: List<String>?, labels: List<String>?): SupportSQLiteQuery {
    val queryBuilder = StringBuilder("SELECT * FROM DETECTION WHERE 1=1")
    val args = mutableListOf<Any>()

    sessionIds?.takeIf { it.isNotEmpty() }?.let {
        queryBuilder.append(" AND sessionId IN (${it.joinToString(",") { "?" }})")
        args.addAll(it)
    }

    sources?.takeIf { it.isNotEmpty() }?.let {
        queryBuilder.append(" AND source IN (${it.joinToString(",") { "?" }})")
        args.addAll(it)
    }

    labels?.takeIf { it.isNotEmpty() }?.let {
        queryBuilder.append(" AND label IN (${it.joinToString(",") { "?" }})")
        args.addAll(it)
    }

    return SimpleSQLiteQuery(queryBuilder.toString(), args.toTypedArray())
}

// 使用示例
val query = buildFilteredQuery(null, listOf("camera_front"), null)
val filteredDetections = detectionDao.getFilteredDetections(query)

这种方式灵活性最高，但需要自己处理SQL拼接和参数绑定，不过使用参数化查询可以避免SQL注入风险。

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