在会议室查询中给定特定参数的情况下,如何从会议室数据库中选择特定列?
我该怎么办?
我想在我的房间查询中选择一个特定的列.此列将作为查询函数中的参数指定.
I want to select a specific column in my room query. This column will be specified by as a parameter in the query function.
想象一下我的数据库如下:
Imagine my database looks like the following:
+----+---------+---------+---------+---------+
| ID | Column1 | Column2 | Column3 | Column4 |
+----+---------+---------+---------+---------+
| 1 | 13 | 45 | 77 | 12 |
+----+---------+---------+---------+---------+
| 2 | 5 | 34 | 67 | 7 |
+----+---------+---------+---------+---------+
| 3 | 8 | 33 | 69 | 12 |
+----+---------+---------+---------+---------+
我希望能够指定任何列,并为所有行返回指定列中的值.
I would like to be able to specify any of the columns and return the value in the specified column for all rows.
@Query("SELECT " + desiredSubcategory + " AS subcategoryValue FROM subcategory_table")
List<Subcategory> getSubcategory(String desiredSubcategory);
class Subcategory {
Float subcategoryValue;
public void setSubcategoryValue(Float subcategoryValue) {
this.subcategoryValue = subcategoryValue;
}
}
根据这篇文章:房间:传递列名称作为DAO方法中的参数 此功能是不可能做到的.但是,这是一年前的事情.
Edit 1: According to this post: Room: pass columns Name as parameter in DAO Method this feature is impossible to do. However, this was a year ago.
您可以使用CASE之类的公用表表达式进行操作
You can do with Common Table Expression with CASE's like
@Query("WITH parms(c) AS (SELECT :columnName) " +
"SELECT COALESCE(" +
"CASE " +
"WHEN (SELECT c FROM parms) = 'column1' THEN column1 " +
"WHEN (SELECT c FROM parms) = 'column2' THEN column2 " +
"WHEN (SELECT c FROM parms) = 'column3' THEN column3 " +
"WHEN (SELECT c FROM parms) = 'column4' THEN column4 " +
"END" +
",0) AS subcategoryValue " +
"FROM subcategory_table" +
";")
float[] getSubcategory(String columnName);
不需要Subcategory类,效率较低,但是如果需要,可以将float[] getSubcategory(String columnName);
更改为List<Subcategory> getSubcategory(String columnName);
The Subcategory class not needed and is less efficient, but could use if wanted change float[] getSubcategory(String columnName);
to List<Subcategory> getSubcategory(String columnName);