如何在运行时动态查询房间数据库？

Question

问题

是否可以在运行时构建查询？

用例

@Query("SELECT * FROM playlist " +
        "WHERE playlist_title LIKE '% :playlistTitle %' " +
        "GROUP BY playlist_title " +
        "ORDER BY playlist_title " +
        "LIMIT :limit")
 List<IPlaylist> searchPlaylists(String playlistTitle, int limit);

limit 部分是可选的。也就是说，它应该能够执行具有或不具有限制的相同查询。

更复杂的用例

在前面的情况下，可以制作两个静态查询，一个带有 limit 部分，一个没有带有 limit 部分，每次可以使用适当的查询。但是有时我们可能需要处理更复杂的情况，例如构建筛选器。

在这种情况下，与上一个示例不同，将有多个可选部分。对于一张书表，我们可能需要根据书籍所属的类别、作者姓名、价格范围、出版日期等进行过滤。几乎不可能制作包含所有这些部分组合的静态查询。

- Anderson K

---

“可选参数”是什么意思？暂时不考虑 Room，您如何使用传统的 Android SQLite API 实现可选参数？ - CommonsWare

@CommonsWare 我的意思是我想动态地设置或不设置查询的限制参数。 - Anderson K

好的。暂时忽略 Room，你会如何使用 SQLiteDatabase 实现这个功能？如果答案是“我会有两个不同的查询字符串”，那么为什么不使用两个不同的 @Query 注释呢？由于注释可以引用静态字段（据我所知），原则上，您甚至可以通过一次定义查询字符串的核心部分并从每个注释中引用它来减少冗余。 - CommonsWare

我明白你的意思，我正在进行重构，由于这个房间非常新，我正在尝试看是否有更好的方法来完成这个任务，否则我将创建两种方法。 - Anderson K

Answer 1

Room支持@ RawQuery 注释，以在运行时构造查询。

步骤1：创建DAO方法

使用@RawQuery 注释标记DAO方法，而不是普通的@Query 。

@Dao
interface BooksDao{
    @RawQuery
    List<Book> getBooks(SupportSQLiteQuery query);
}

步骤二：构建查询

Room使用预编译语句来保证安全性和编译时验证。因此，在构建查询时，我们需要将查询字符串和绑定参数分别存储。

在本例中，我使用变量queryString表示查询字符串，args表示绑定参数。

^{（请注意，我使用文本编辑器编写代码。因此可能存在拼写错误或简单的语法错误。如果您发现任何问题，请在评论中告诉我或编辑文章。）}

// Query string
String queryString = new String();

// List of bind parameters
List<Object> args = new ArrayList();

boolean containsCondition = false;

// Beginning of query string
queryString += "SELECT * FROM BOOKS";

// Optional parts are added to query string and to args upon here

if(!authorName.isEmpty()){
    queryString += " WHERE";
    queryString += " author_name LIKE ?%";
    args.add(authorName);
    containsCondition = true;
}

if(fromDate!=null){
    
    if (containsCondition) {
        queryString += " AND";
    } else {
        queryString += " WHERE";
        containsCondition = true;
    }

    queryString += " publication_date AFTER ?";
    args.add(fromDate.getTime());
}

if(toDate!=null){
    
    if (containsCondition) {
        queryString += " AND";
    } else {
        queryString += " WHERE";
        containsCondition = true;
    }

    queryString += " publication_date BEFORE ?";
    args.add(toDate.getTime());
}

// End of query string
queryString += ";";

第三步：执行查询

SimpleSQLiteQuery query = new SimpleSQLiteQuery(queryString, args.toArray());
List<Book> result = booksDao.getBooks(query);

备注

• RawQuery 和普通的 Query 一样，支持返回原始游标、实体、POJO 和带嵌入字段的 POJO
• RawQuery 支持关系

- Bertram Gilfoyle

---

@RawQuery是否存在SQL注入攻击的风险，从而构成安全隐患？ - AJW

不，它并不是像名称所暗示的那样简单的文本查询。它在内部使用绑定变量。更准确地说，它使用的是SimpleSQLiteQuery。 - Bertram Gilfoyle

我想建议一些课程，使这项工作更容易。期待您的反馈。UpdateRawQueryBuilder SimpleQueryBuilder - tim4dev

@tim4dev 谢谢。虽然还没有测试，但乍一看看起来不错。 - Bertram Gilfoyle

这应该是被接受的答案，因为它解决了原始问题。 - svprdga

这样做不行吗：@Query(":query") suspend fun selectByQuery(query: String): List<Book>？ - Leonardo Sibela

Answer 2

根据我的(简短)使用Room的经验，这是不可能的，这不是因为Room的限制，而是由于SQLite的限制，正如@CommonsWare隐含地评论的那样。你需要两个查询，并且在你的DAO中需要两种方法。

我会像这样写：

@Query("SELECT * FROM playlist " +
    "WHERE playlist_title LIKE '% :playlistTitle %' " +
    "GROUP BY playlist_title " +
    "ORDER BY playlist_title " +
    "LIMIT :limit")
List<IPlaylist> searchPlaylists(String playlistTitle, int limit);

@Query("SELECT * FROM playlist " +
    "WHERE playlist_title LIKE '% :playlistTitle %' " +
    "GROUP BY playlist_title " +
    "ORDER BY playlist_title ")
List<IPlaylist> searchPlaylists(String playlistTitle);

然后在其他地方进行旁路：

if (limit.isPresent()) {
   return playlistDao.searchPlaylists(title, limit.get());
} else {
   return playlistDao.searchPlaylists(title);
}

那是我目前能想到的最佳选择。

- Juanky Soriano

---

你好，这在我的情况下不起作用。特别是这一行 "WHERE playlist_title LIKE '% :playlistTitle %' " - Bipin Bharti

当我使用这个方法时，出现了“playlistTitle”未使用的错误。 - Bipin Bharti

这是一个很好的回答。但自从Room引入了@RawQuery注释后，它已经过时了。 - Bertram Gilfoyle

你需要使用||将房间查询字符串连接起来，因此变为：LIKE '%' || :playlistTitle || '%'。同时也要使用||将所有内容连接起来。 - Vikram

Answer 3

不必写多个查询语句，我建议在limit子句中传递负数值。因为如果查询有更改，我需要更新两个查询语句，这会增加出错的可能性。

官方文档 -> 如果LIMIT表达式计算结果为负值，则返回的行数没有上限。你可以在这里找到它https://sqlite.org/lang_select.html ，并阅读limit子句部分。

所以我会像这样做：

@Query("SELECT * FROM playlist " +
    "WHERE playlist_title LIKE '% :playlistTitle %' " +
    "GROUP BY playlist_title " +
    "ORDER BY playlist_title " +
    "LIMIT :limit")
List<IPlaylist> searchPlaylists(String playlistTitle, int limit);

当您不想应用筛选器时，请传递负值。

return playlistDao.searchPlaylists(title, limit.isPresent() ? limit.get() : -1)

在我的情况下它正在工作。

更新 [2018年12月21日]

如果您正使用 Kotlin，请使用默认值。

@JvmOverloads
@Query("SELECT * FROM playlist " +
        "WHERE playlist_title LIKE '% :playlistTitle %' " +
        "GROUP BY playlist_title " +
        "ORDER BY playlist_title " +
        "LIMIT :limit")
fun searchPlaylists(playlistTitle: String, limit: Int = -1): List<IPlaylist>

@JvmOverloads 使其与 Java 兼容。它为 Java 生成了两个单独的方法。

- Moinkhan

---

或者将Dao设置为抽象类，并使用限制参数设置为-1将第二个方法委托给第一个方法。 - Eugen Pechanec

Answer 4

使用SupportSQLiteQuery。

https://developer.android.com/reference/android/arch/persistence/db/SupportSQLiteQuery

最新版本1.1.1现在使用SupportSQLiteQuery。

带有类型绑定的查询。使用这个 API 要比 rawQuery(String, String[]) 更好，因为它允许绑定类型安全参数。

@Dao
     interface RawDao {
         @RawQuery(observedEntities = User.class)
         LiveData<List<User>> getUsers(SupportSQLiteQuery query);
     }

使用方法：

     LiveData<List<User>> liveUsers = rawDao.getUsers( new 
SimpleSQLiteQuery("SELECT * FROM User ORDER BY name DESC"));

将您的gradle更新至1.1.1版本。

implementation 'android.arch.persistence.room:runtime:1.1.1'
implementation 'android.arch.lifecycle:extensions:1.1.1'
annotationProcessor "android.arch.persistence.room:compiler:1.1.1"

注意：如果您升级到1.1.1版本，并且使用的是String而不是SupportSQLiteQuery，您将会收到以下错误信息： “RawQuery不再允许传递字符串。请使用android.arch.persistence.db.SupportSQLiteQuery。” 按照上述使用SupportSQLiteQuery的方式可以解决此问题。 请注意：确保传递SupportSQLiteQuery查询参数，否则会收到以下错误信息： “RawQuery方法应该只有一个参数，并且参数类型应该是String或SupportSQLiteQuery”。

- live-love

---

@RawQuery是否存在SQL注入攻击的风险，从而构成安全隐患？ - AJW

@AJW 如果在使用SupportSQLiteQuery时没有if语句，那么根据SupportSQLiteQuery的文档，会在继续执行之前对参数进行验证。因此，SimpleSQLiteQuery是安全的，因为它是SupportSQLiteQuery的子类。 - MatPag

使用您上面提到的SupportSQLiteQuery，@live-love说：“最好使用此API而不是rawQuery(String, String[])，因为它允许绑定类型安全参数。” 您所说的“类型安全参数”是什么意思？这是否意味着使用独立的RawQuery存在风险，而使用SupportSQLLiteQuery可以消除这种风险？ - AJW

android.arch已经被弃用。 - G. Ciardini

Answer 5

Room 中没有可选参数的概念，但是有 @RawQuery 注解，您可以将查询作为字符串传递，从而可以在运行时构建 SQL 查询。我认为这对您有用。

以下是官方文档中的示例：

@Dao
 interface RawDao {
     @RawQuery
     User getUser(String query);
 }

以下是如何使用它的方法：

User user = rawDao.getUser("SELECT * FROM User WHERE id = 3 LIMIT 1");

重要提示: RawQuery方法必须返回非空类型

重要提示: 该功能仅适用于Room 1.1.0-alpha3版本

- Mladen Rakonjac

---

是的，但问题在于它仅适用于读取查询。那么你如何处理写入查询？ - Claude Hangui

@RawQuery是否存在SQL注入攻击的风险，从而构成安全隐患？ - AJW

Answer 6

让它更简单。我将通过使用两个变量的where子句来展示示例。像这样做：

@Query("SELECT * FROM Student WHERE stdName1= :myname AND stdId1=:myid")
List<Student> fetchAllData(String myname,int myid);

stdName1和stdId1是列名。

- Syed Danish Haider

---

谢谢你们两个。这个小字符帮了我很多。 - Sakhawat Hossain

Answer 7

对于Kotlin-Room-ViewModel

@Query("SELECT * FROM schedule_info_table where schedule_month = :monthValue ORDER BY schedule_date_time ASC")
fun getThisMonthSchedules(monthValue: Int): Flow<List<SchedulesInformation>>

- Guru raj

Answer 8

@Anderson K和@Juanky Soriano， 我同意@CommonsWare的观点，

虽然Room库有一些限制，但我们仍然可以通过使用支持SQLite数据库的@query()来编写完全动态的查询。

String mQuery = "SELECT * FROM foobar WHERE columnName1 IN ('value_1','value_2') and columnName2 In('value_3','value_4')";

AppDatabase appDatabase = Room.databaseBuilder(getApplicationContext(),
        AppDatabase.class, "database-name").build();

Cursor mCursor = AppDatabase.getAppDatabase(context).getOpenHelper().getReadableDatabase().query(myQuery);

现在，您可以将光标逐行数据转换为您的POJO类。

- TejaDroid

Answer 9

@Query("select * from task where state = :states and sentdate between :fromdate and :todate")
  List<Task> getFilterAll(String states, String fromdate, String todate);

在这里，我们需要使用列名 state。每当需要实现自定义查询时，只需通过活动或片段将值通过参数传递到接口中，我们将在查询中应用。就像上面代码中的示例（:fromdate，:todate）

冒号是必须的。在开始使用查询之前，我们将在 : 符号之前提到要使用哪个参数。

- mahendren

Answer 10

根据文档，原始查询已被弃用。

- vinay shetty

---

Android.arch架构组件包已被弃用，RawQuery在androidx中可用。 - Zac