Optional query parameters for Android Room

Question

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
    );
}

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?

Answer 1

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.

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
    );
}

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

Edit: Please consider that this solution is useful for non-nullable fields. If the field is nullable and you want to look for the records which don't have a value for the field, this is not the correct way of querying and you may consider dynamic query creation.