RawQuery

Marks a method in a Dao annotated class as a raw query method where you can pass the query as a androidx.room.RoomRawQuery or androidx.sqlite.db.SupportSQLiteQuery.

@Dao
interface RawDao {
    @RawQuery
    fun getSongViaQuery(query: RoomRawQuery): Song
}

// Usage of RawDao
val query = RoomRawQuery(
    sql = "SELECT * FROM Song WHERE id = ? LIMIT 1",
    onBindStatement = { it.bindLong(1, songId) }
)
val song = rawDao.getSongViaQuery(query)

Room will generate the code based on the return type of the function and failure to pass a proper query will result in a runtime failure or an undefined result.

If you know the query at compile time, you should always prefer Query since it validates the query at compile time and also generates more efficient code, since Room can compute the query result at compile time (e.g. it does not need to account for possibly missing columns in the result).

On the other hand, @RawQuery serves as an escape hatch where you can build your own SQL query at runtime but still use Room to convert it into objects.

@RawQuery methods must return a non-void type. If you want to execute a raw query that does not return any value, use androidx.room.RoomDatabase.query methods.

@RawQuery methods can only be used for read queries. For write queries, use androidx.room.RoomDatabase.openHelper.

Observable Queries:

@RawQuery methods can return observable types but you need to specify which tables are accessed in the query using the observedEntities field in the annotation.

@Dao
interface RawDao {
    @RawQuery(observedEntities = Song::class)
    fun getSongs(query: RoomRawQuery): Flow<List<Song>>
}

// Usage of RawDao
val liveSongs = rawDao.getSongs(
    RoomRawQuery("SELECT * FROM song ORDER BY name DESC")
)

Returning POJOs:

@RawQuery can also return plain old java objects, similar to Query methods.

data class NameAndReleaseYear (
    val name: String,
    @ColumnInfo(name = "release_year")
    val year: Int
)

@Dao
interface RawDao {
    @RawQuery
    fun getNameAndReleaseYear(query: RoomRawQuery): NameAndReleaseYear
}

// Usage of RawDao
val result: NameAndReleaseYear = rawDao.getNameAndReleaseYear(
    RoomRawQuery(
        sql = "SELECT * FROM song WHERE id = ?",
        onBindStatement = { it.bindLong(1, songId) }
    )
)

POJOs with Embedded Fields:

@RawQuery methods can return POJOs that include Embedded fields as well.

data class SongAndArtist (
    @Embedded
    val song: Song,
    @Embedded
    val artist: Artist
)

@Dao
interface RawDao {
    @RawQuery
    fun getSongAndArtist(query: RoomRawQuery): SongAndArtist
}

// Usage of RawDao
val result: = rawDao.getSongAndArtist(
    RoomRawQuery("SELECT * FROM Song, Artist WHERE Song.artistId = Artist.id LIMIT 1")
)

Relations:

@RawQuery return types can also be objects with Relation.

data class AlbumAndSongs {
    @Embedded
    val album: Album,
    @Relation(parentColumn = "id", entityColumn = "albumId")
    val songs: List<Song>
}

@Dao
interface RawDao {
    @RawQuery
    fun getAlbumAndSongs(query: RoomRawQuery): List<AlbumAndSongs>
}

// Usage of RawDao
val result = rawDao.getAlbumAndSongs(
    RoomRawQuery("SELECT * FROM album")
)

Summary

RawQuery(observedEntities: Array<KClass<*>> = [])

val observedEntities: Array<KClass<*>>

@Dao
interface RawDao {
  @RawQuery(observedEntities = Song::class)
  fun getSongs(query: RoomRawQuery): Flow<List<Song>>
}
val liveSongs: = rawDao.getSongs(
    RoomRawQuery("SELECT * FROM song ORDER BY name DESC")
)