Android room inserting with a query

Question

I know inserting data into SQlite room library can be done through @Insert annotation, but I (out of curiosity) tried inserting values through SQL statement but Android studio showed me error stating column names can't be resolved. Is it a way of forcing developer to use @Insert annotation or if I am doing something wrong here? Thanks!

Please review following screenshot -

Solution 1

If you omit the column list that is being highlighted and thus provide values for all columns e.g. :-

@Query("INSERT INTO TestTable VALUES(null,:a,:b)")
List<TestTable> getall(String a, String b, String columna, String columnb);

Android Studio doesn't complain but then you get a compiler error of :-

error: INSERT query type is not supported yet. You can use:SELECT, DELETE, UPDATE

So perhaps better sooner than later.

Solution 2

@Entity
data class ModulesRoom(
    @PrimaryKey(autoGenerate = false)
    var id: Int = 0,
    var nav: String = "",
    var name: String = "",
    var imageurl: String = ""
)
for List Object
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun saveEmployees(modules: List<ModulesRoom>)

for single obbjet
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun saveEmployees(modules: ModulesRoom)

Ussage 
val mModules = Modules() 
mModules.id = values
mModules.nav = values 
saveEmployees(mModules)

Comments

[Vijayendra]

Thanks for your answer @MikeT. But what if I had a conditional insertion of user based on his age (assuming I had 'age' column in table 'user') or timestamp if my inserted time is after a timestamp, calculated through some other query. I mean complex queries then might require reference to column names. I hope it won't show error in column names if used after VALUES data. Any thoughts? Thanks again!

[MikeT]

@DevBoy not really any thoughts, personally I prefer the old non-ROOM way. So only dabble with ROOM on occasions (was playing around with copying DB from assets so had a project with ROOM open, hence why I tried the above).

[Vijayendra]

I see. I will try to find answer to a complex case. May be I will post a separate question for that with proper example. For now I accept your answer. Cheers!

[Mateen Chaudhry]

i want to insert only one column record can you tell please how i can do that with room? i am doing this but it is not working @Query("Insert into SETTINGS_TABLE (KEY_ID) values ('0') ")

[JazzyJ]

I just tried it today and it works for me. Android rooms version 2.25. Use null in the insert query statement of you have a primary key