Room database onConflict = OnConflictStrategy.REPLACE not working

Question

I am working on Room database and trying to insert list of items(eg. list of Quotes which contains author name and a quote in my case).

Following is the code I am using:

// view model
BaseApp.daoInstance?.appDao()?.insertQuotes(response!!)

// dao
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertQuotes(listData: MutableList<Quote>)

When I try to insert the same data again, it always inserts as a new data instead of replacing with the current items.

I have researched a lot for this OnConflictStrategy.REPLACE but could not find any proper answer.

Is there anyone facing the same issue and found solution or am I doing anything wrong?

Thank you in advance...!!!

Answer 1

Room, will not check and compare if you have the quote already in the DB. What it will do is look if the primary key already exists in the DB if it does, Room will replace all old data with the new one.

In your case, you are not specifying an ID so the DB is generating a unique one for you. What you should do is create a Query that will search for this quote in the DB something like this:

@Query("SELECT * from quote_table WHERE author = :author AND quote = :quote")
List<Quote> getQuoteByAuthorAndQuote(string author, string quote);

This should return a list with a single quote if one is found and empty if it does not exist.

If you would like to override the old one just update the data in the Quote POJO and insert it to the DB using Room.

Answer 2

Have you tried to index your main column and mark it as unique?

@Index(value = {"quote"}, unique = true)}