Android Room inserts duplicate entities

Question

I'm using Android's Room library for the database interaction in an application and I'm sort of stumped on how to prevent duplicate entries from being inserted into the database.

I feel like I must be missing something because this seems like it should be simple to do. I've searched Google for various combinations of words relating to the subject to no avail.

I'm essentially using what one of the samples does for inserting and querying.

The Entity:

@Entity(tableName = "cameras")
public class CameraEntity {
    @PrimaryKey(autoGenerate = true)
    private int id;
    private Integer accountId;
    private Integer dvrId;
    private String vendorId;
    ...
}

The DAO:

@Dao
public interface CameraDao {

    @Query("SELECT * FROM cameras")
    Flowable<List<CameraEntity>> getCameras();

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void insertAll(List<CameraEntity> values);
}

In terms of the Room library, is there any way to set some rules on when data should be inserted? One post I read mentioned that the auto increment ID is causing each item to be unique in terms of the primary key. If that's true, how are others using this library accounting for that?

Thanks!

Adversaria

"the auto increment ID is causing each item to be unique in terms of the primary key" -- correct. "how to prevent duplicate entries from being inserted into the database" -- how are you defining "duplicate entries"? We have no way of knowing, in part because your CameraEntity listing is redacted. – Lipoid

Good point. I added a few more fields for the sake of discussions. I would define duplicate entries as anything with a matching vendorId (String in this case). – Adversaria

Then make that be the primary key. Or, add a unique index. – Lipoid

@Lipoid Awesome, the unique index seems to do the trick! Thanks! I figured it would be something simple I missed like that. – Adversaria

Answer 1

Only use an auto-generated primary key if that is really what you need as a primary key. If your data has a natural primary key, use that, and it determines the uniqueness, in terms of what REPLACE will do.

If you want an auto-generated primary key, but you also want some other column (or combination of columns) to be unique, add a unique index on the column(s), and that will also affect REPLACE.

Lipoid

OnUpdate = CASCADE will replace the duplicate entries and this link explains how can we apply uniqueness to multiple columns developer.android.com/training/data-storage/room/… – Intermezzo

@Lipoid how can i allow duplicate entries, my room-database is auto-replacing any duplicate ... please see [this]:#49728857 – Paluas

Unique index URL is outdated--tried to edit it, but was told there are too many pending edits, so it's here: developer.android.com/training/data-storage/room/…. – Fattish

Answer 2

I stuck this situation on first application with room database. I need something to if that column data exist update other data in row else insert to table

I've one primary key and one uid(here is accountId or dvrId) that are also like primary key and should not be repeated.

For doing this you have to create indices for Entity and put all columns that you don't want replace in it like this

@Entity(tableName = "cameras", indices = [Index(value = ["accountId","dvrId"], unique = true)])
public class CameraEntity {
    @PrimaryKey(autoGenerate = true)
    private int id;
    private Integer accountId;
    private Integer dvrId;
    private String vendorId;
}

And don't forget choose REPLACE strategy

@Insert(onConflict = OnConflictStrategy.REPLACE)
void insertAll(List<CameraEntity> values);

Now if any of id and accountId and dvrId exist in the same column data will update otherwise data will insert at new row

Hope it help

Medicate

i have to update only few filed on my table , is this possible using your answer ? – Zsolway

Thank you. I think this should be accepted. – Phelgon