在会议室查询中给定特定参数的情况下，如何从会议室数据库中选择特定列? [英] How to select a specific column from room database given a specific parameter in room query?

问题描述

我该怎么办?

我想在我的房间查询中选择一个特定的列.此列将作为查询函数中的参数指定.

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

这篇关于在会议室查询中给定特定参数的情况下，如何从会议室数据库中选择特定列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！