房间数据库中的硬编码布尔查询

我正在构建一个 Android 应用程序，它显示用户的潜在匹配列表。用户可以点击其中一个来喜欢这个用户，我在本地保存所有这些喜欢。

我可以编写一个查询来获得这样的匹配列表:

@Query("SELECT * FROM match WHERE liked = :liked ORDER BY match DESC LIMIT :limit")
fun getMatches(limit: Int = 6, liked: Boolean = true): Flowable<List<Match>>

我知道这样很好。然而，我并没有预见到任何将 liked设置为 false 的场景，因此我很好奇是否有办法对我的布尔条件进行硬编码？如果我尝试:

@Query("SELECT * FROM match WHERE liked = true ORDER BY match DESC LIMIT :limit")

我在编译时得到以下错误:

Error:(8, 0) Gradle: error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: true)

如何在查询字符串中硬编码这个布尔值？

我也试过:

  • 用单引号包装条件
    • @Query("SELECT * FROM match WHERE liked = 'true' ORDER BY match DESC LIMIT :limit")
47006 次浏览
小开
最佳答案

SQLite does not have a boolean data type. Room maps it to an INTEGER column, mapping true to 1 and false to 0.

So, I would expect this to work:

@Query("SELECT * FROM match WHERE liked = 1 ORDER BY match DESC LIMIT :limit")

Bear in mind that this behavior is undocumented. However, it shouldn't change — at least not without alarm klaxons sounding — as we'd need to use migrations to deal with any changes.

小开

CommonWare's approach does work and also answers the OPs question directly; however, I'm not a fan of making such an assumption about the database. The assumption should be safe, but it may create unexpected work down the road if Room ever decides to change it's boolean implementation.

I'd suggest that the better approach is to not hardcode the boolean 1 or 0 into the query. If the database is behind a repository, it is still possible for the repository to expose a graceful API. Personally, I think shielding the larger codebase from the database implementation is a good thing anyways.

Dao Method (copied from OP's question)

@Query("SELECT * FROM match WHERE liked = :liked ORDER BY match DESC LIMIT :limit")
fun getMatches(limit: Int = 6, liked: Boolean = true): Flowable<List<Match>>

Repository

class Repository {
public Flowable<List<Match>> getLikedMatches() {
return dao.getMatches(6, true);
}
}

Of course, this is an opinionated option in that it assumes a certain architectural style. However, it does not make assumptions about the internal database. Even without the repository shielding the database, the call can be made into the database by passing true everywhere - also without making assumptions as to the underlying data.

小开

You don't have to compare boolean column to a value. Just use the column value itself as a boolean expression. You can easily change your query to SELECT * FROM match WHERE liked ORDER BY match DESC LIMIT :limit. If you want to compare to false value you can use following expression: where not liked.

小开 
@Query("SELECT * FROM searched_data_table WHERE favourit_history==1 ORDER BY lang_id DESC")

Use this query to search data from the table this will give you data in descending order with respect to your key value


提交答案