Android Room的可选查询参数 [英] Optional query parameters for Android Room

问题描述

我对以下DAO进行查询:

I have the following DAO with a query:

@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创建可选参数?

The String parameters are "optional" in the sense that I can pass "%%" to match all rows due to the LIKE operator. But I cannot do this with year since it is an int. One solution is to add two different @Query methods, one with the int year parameter and the other without. Is there a more elegant way to create an optional parameter with Room's @Query?

推荐答案

这是一个很晚的答案，但是正如我最近面对的那样，我想与那些正在寻找它的人分享我的简单技巧(但很愚蠢！).

It is a late answer but as I have faced it recently, I wanted to share my simple (but silly!) trick for those who are looking for it.

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

As @CommonsWare has said, we can add an OR statement that checks for null to it and then simply make our optional parameters nullable and pass null for them. For example, your query would look like:

@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和可选参数进行更多声明:

Or more declarative using kotlin and optional parameters:

@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>>

这篇关于Android Room的可选查询参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！