Query

#Introduction

A common thing to do with a database is to read data. For that, we add @Query functions on our DAO.

Those do not have to be especially complex.

The select() functions in the samples are delightfully simple:

@Query("SELECT * FROM Person")
suspend fun select(): List<Person>

However, SQL queries with SQLite can get quite sophisticated. Room tries to support much of the standard SQL syntax and adds its own layer to interpret your @Query function’s arguments and return types.

#Adding Parameters

As we saw with functions like selectWhereId(), you can map function arguments to query parameters by using : syntax.

Put : before the argument name; its value will be bound into the query:

@Query("SELECT * FROM Party WHERE id = :id")
suspend fun selectWhereId(id: Long): Message?

#where clause

Typically, function arguments are injected into the where clause, as in the previous example.

Room has special support for in with a vararg or List parameter:

@Query("select * from person where name in (:names)")
fun selectWhereNameIn(vararg names: String): List<Person>

Here, in (:names) is expanded by Room to include all values supplied in the argument, returning all entities matching any of those values.

#Other clauses

Wherever SQLite allows ? placeholders, Room should allow function arguments.

For example, parameterize a limit clause:

@Query("select * from Person limit :limit")
fun loadFirst(limit: Int): List<Person>