机房数据库全动态查询 [英] Room database full dynamic query

问题描述

我有使用对象硬币的房间数据库.

I Have room database with Object coin.

我想使用参数进行动态查询.

I want to make a dynamic query with parameter.

当我使用参数作为值时，它的工作原理非常好，就像这样:

When i use the parameter for a value, it works great, like this:

@Query("select * from coin ORDER BY percent_change_24h asc limit :numberOfCoins")
fun getAllTop(numberOfCoins: Int): Flowable<List<CoinDB>>

但是当我想为WHERE子句使用参数时，那是行不通的. 这是我的查询:

But when i want to use a parameter for the WHERE clause, that does not work. here is my query:

@Query("select * from coin ORDER BY :order asc limit :numberOfCoins")
fun getAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>

我这样称呼它:

AppDatabase.getInstance(this).coinDao().getAllTop("percent_change_24h",5)

使用隐式WHERE子句调用相同的查询可以正常工作(例如:)

Calling the same query with implicit WHERE clause work fine (like this:)

@Query("select * from coin ORDER BY percent_change_24h asc limit :numberOfCoins")
fun getAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>

推荐答案

您不能使用绑定变量(参数)来引用ORDER BY子句中的列.但是，您可以在表达式中使用bind变量，如下所示:

You can't use bind variables (parameters) to reference columns in the ORDER BY clause. However, you can use the bind variable in an expression like so:

@Query("select * from coin ORDER BY
CASE :order
WHEN 'percent_change_24h' THEN percent_change_24h
WHEN 'other_column_name' THEN other_column_name
END asc limit :numberOfCoins")
fun getAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>

您需要为要排序的每个列/表达式在CASE语句中添加一个单独的WHEN子句，对于:order绑定变量不存在的情况，您可能需要或想要添加ELSE子句符合您的任何标准案例.

You will need to add a separate WHEN clause to the CASE statement for each column/expression you want to sort by, and you may need or want to add an ELSE clause for the situations where the :order bind variable doesn't match any of your standard cases.

对绑定变量的限制也适用于where子句和投影(选择列表).在您的示例中，绑定变量具有自己的值:String或Int分别用于:order和:numberOfCoins.

The restriction on bind variables also holds true for the where clause and the projection (select list). Bind variable have their own values in your examples either String or Int for :order and :numberOfCoins respectively.

这篇关于机房数据库全动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！