how to achieve sqlite query in room database?

Question

This is SQLite query i want to use in room database here is the first to try insert data and if data is already exist then update the data by using id

INSERT INTO books(id, title, author, year_published)  VALUES(@id, @title, @author, @year_published)
 ON DUPLICATE KEY UPDATE title = @title,  author = @author;

https://stackoverflow.com/questions/48519896/room-update-or-insert-if-not-exist-rows-and-return-count-changed-rows — Raghunandan, Dec 12 '19 at 10:49
not sure but you can insert or update if the values exist. check this https://developer.android.com/reference/android/arch/persistence/room/RawQuery if it helps — Raghunandan, Dec 12 '19 at 10:54
@Parth - have you tried this as well - https://developer.android.com/reference/android/arch/persistence/room/RawQuery — Anuj Sharma, Dec 12 '19 at 11:08

score 0 · Answer 1 · answered Dec 12 '19 at 11:13

Room supports @RawQuery annotation to construct queries at run-time. So you need:

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

And create your query in old school style:

String queryString = "here is your query";
List<Object> args = new ArrayList(); // here is your args for your query

And ther performe your query:

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

score 0 · Answer 2 · answered Dec 12 '19 at 11:30

You can achieve this with OnConflictStrategy.REPLACE

@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertBooks(vararg books: Book)

And you have to specify the id id as primary key

@Entity(tableName = "book_table")
data class Book(
    @PrimaryKey val id: String,
    val title: String,
    val author: String,
    val year_published: Int
)

how to achieve sqlite query in room database?

2 Answers2