安卓聊天室的可选查询参数

Question

我有以下带有查询的DAO：

@Dao
public interface BaseballCardDao {
@Query(
"SELECT * FROM baseball_cards " +
"WHERE brand LIKE :brand " +
"  AND year = :year " +
"  AND number LIKE :number " +
"  AND player_name LIKE :playerName " +
"  AND team LIKE :team"
)
LiveData<List<BaseballCard>> getBaseballCards(
String brand, int year, String number, String playerName, String team
);
}

String参数是"可选的"，因为我可以通过LIKE运算符传递"%%"来匹配所有行。但是我不能用year做到这一点，因为它是一个int.一种解决方案是添加两种不同的@Query方法，一种带有int year参数，另一种不使用。有没有更优雅的方法可以使用 Room 的@Query创建可选参数？

Answer 1

这是一个迟到的答案，但正如我最近面对的那样，我想为那些正在寻找它的人分享我简单(但愚蠢！(的技巧。

正如@CommonsWare所说，我们可以添加一个检查 null 的OR语句，然后简单地使我们的可选参数可为 null 并为它们传递null。 例如，您的查询如下所示：

@Dao
public interface BaseballCardDao {
@Query(
"SELECT * FROM baseball_cards " +
"WHERE (:brand IS NULL OR brand LIKE :brand)" +
"  AND (:year IS NULL OR year = :year)" +
"  AND (:number IS NULL OR number LIKE :number)" +
"  AND (:playerName IS NULL OR player_name LIKE :playerName)" +
"  AND (:team IS NULL OR team LIKE :team)"
)
LiveData<List<BaseballCard>> getBaseballCards(
@Nullable String brand, @Nullable Integer year, @Nullable String number, @Nullable String playerName, @Nullable String team
);
}

或者使用 kotlin 和可选参数进行更具声明性的：

@Query(
"""SELECT * FROM baseball_cards 
WHERE (:brand IS NULL OR brand LIKE :brand) 
AND (:year IS NULL OR year = :year) 
AND (:number IS NULL OR number LIKE :number) 
AND (:playerName IS NULL OR player_name LIKE :playerName)
AND (:team IS NULL OR team LIKE :team)"""
)
fun getBaseballCards(
brand: String? = null,
year: Int? = null,
number: String? = null,
playerName: String? = null,
team: String? = null
): LiveData<List<BaseballCard>>

编辑： 请注意，此解决方案对于不可为空的字段很有用。如果字段可为空，并且您想要查找没有字段值的记录，则这不是正确的查询方法，您可以考虑创建动态查询。