Execute raw update query in Room Database

zaitsman 0
Viewed 18

I have a Room database where I want to run a basic UPDATE Table SET Column = ? WHERE Column2 = ?.

I can execute my query like so

val db = ... /// my room database

val sqlDb = db.openHelper.writableDatabase
sqlDb.execSQL(query.toString(), arrayOf(valueFor1, valueFor2))

However this didn't sit so great with me as I am not clear on whether I am meant to close the sqlDb after each query or I can leave it and let Room re-use it, as well as this feels like dropping down to lower-level API.

I tried using Room's own query method like so:

db
.query(SimpleSQLiteQuery(query.toString(), arrayOf(valueFor1, valueFor2)))
.close()

However, no update happens to the data in my db.

Is there any way for me to execute this query from RoomDatabase object directly?

Note: I do NOT want to use DAO at all. My query is dynamically created by inspecting columns in the db and I have about 100 tables in my database, I don't want to have to add this to every DAO.

android-room
1 Answers

In an @Dao annotated interface have a function:-

@Query("UPDATE Table SET Column=:newValue WHERE Column2=existingValue")
fun myUpdate(existingValue: String, newValue: String)

In the @Database annotated class you define an abstract function to get the @Dao annotated class. Which allows you to then get the Dao interface/abstract class and use the functions.

Then you would use something like:-

val db = ... /// my room database
val dao = getTheDao()
dao.myUpdate("OLD","NEW")
MikeT0
Related