android - 可选查询参数不适用于 Room

Question

假设我们有以下数据库条目:

@Entity
data class Dog(
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "breed") val breed: String?
)

如您所见，并非每只狗都定义了品种。现在我们要查询以根据品种搜索所有狗。

@Dao
interface DogDao {

    @Query("SELECT * FROM dogs WHERE breed = :breed")
    fun getByBreed(breed: String?): List<Dog>
}

有时我们想搜索具有特定品种的狗，有时我们想搜索没有定义品种的狗。问题是在第二种情况下，上面的查询将不起作用。为什么？当 getByBreed(breed:) 方法的 breed 参数为空时，Room 会将此查询转换为如下内容:

SELECT * FROM dogs WHERE breed = NULL

不幸的是，SQLite 查询空值应该是这样的:

SELECT * FROM dogs WHERE breed IS NULL

问题是，如何定义接受可选参数的查询？

Answer 1

您应该查看 SQLite 运算符。直接取自此link .

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.

所以你应该拥有并且将等同于你拥有的是:

@Dao
interface DogDao {
    @Query("SELECT * FROM dogs WHERE breed IS :breed")
    fun getByBreed(breed: String?): List<Dog>
}