英文:
Why the sql statement is wrong and the room does not report an error
问题
我现在知道SQL语句不能将列名作为参数。但为什么Room没有报错，仍然成功查询结果。只是它们不是按id倒序排列，而是按顺序排列。
日志输出也是正确的SQL语句。
```kotlin
// AppDatabase
Room.databaseBuilder(...).setQueryCallback({ sqlQuery, bindArgs ->
Log.d("Android Room", "SQL Query: $sqlQuery SQL Args: $bindArgs")
},
...
// logcat
SQL Query: SELECT * FROM ( SELECT * FROM t_user ORDER BY ? DESC ) LIMIT 60 OFFSET 0 SQL Args: [id]
你能告诉我Room如何处理这个SQL语句吗？
<details>
<summary>英文:</summary>
Dao
@Query("SELECT * FROM t_user ORDER BY :order DESC")
fun pagingSource(order: String): PagingSource<Int, User>
I now know that sql statements cannot take columnName as a parameter. But why Room didn't report an error, still succeeded in querying the results.It's just that they are not arranged in reverse order according to id, but in order.
The log print is also the correct sql statement.
```kotlin
// AppDatabase
Room.databaseBuilder(...).setQueryCallback({ sqlQuery, bindArgs ->
Log.d("Android Room", "SQL Query: $sqlQuery SQL Args: $bindArgs")
},
...
// logcat
SQL Query: SELECT * FROM ( SELECT * FROM t_user ORDER BY ? DESC ) LIMIT 60 OFFSET 0 SQL Args: [id]
Can you tell me how Room handles this sql statement?
答案1
得分: 1
正确，但在您的情况下，参数不被解释为列名，而被解释为一个字面字符串，就像'id'一样。由于字面字符串和表达式在ORDER BY子句中是有效且被接受的，因此您的查询被解析和解释为：
select * from t_user order by 'id';
这在语法上是不错误的，但当然不会执行所期望的排序。详见演示。
英文:
> I now know that sql statements cannot take columnName as a parameter
Correct, but in your case the parameter is not interpreted as the name of a column, but as a literal string, like 'id' and since literal strings and expressions are valid and accepted in the ORDER BY clause, your query is parsed and interpreted as:
select * from t_user order by 'id';
which is not syntactically wrong, but of course does not perform the desired ordering.<br/>
See the demo.<br/>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论