CODEWITHSUNDEEP
androidandroid-sqlite
Martin
Martin

Reputation: 2904

Android SQLITE: insertWithOnConflict() duplicates insertion

I want to insert list of objects into my SQLite db. If I do query for each object to check if its present, it takes alot of time for long list (20-30sec).

I used function 'insertWithOnConflict()' to do that faster.

I thought that it will try to insert a row and if record with same value of column entered to function already exists, it will just replace its values (like update) otherwise it will insert new row.

But I checked my DB and it is creating duplicated objects. Any solution for this? Important information 'COLUMN_OBJECT_ID' is not 'PRIMARY_KEY'. 'PRIMARY_KEY' is autoincrement. But 'COLUMN_OBJECT_ID' has to be unique in this case. So I want to update row with same 'COLUMN_OBJECT_ID' because data inside can change.

Code:

fun saveObjectsToCache(objects: List<Item>) {
        val db = getDb()
        db.transaction {
            objects.forEach {item->
                val cv = ContentValues()
                cv.apply {
                    put(COLUMN_OBJECT_ID, item.id)
                    put(COLUMN_OBJECT_DATA, item.js.toString())
                }
                insertWithOnConflict(TABLE_OBJECT_CACHE, COLUMN_OBJECT_ID, cv, SQLiteDatabase.CONFLICT_REPLACE)
            }
        }
    }

Upvotes: 0

Views: 308

Answers (1)

Tal Mantelmakher
Tal Mantelmakher

Reputation: 1002

Looks like you forgot to add a unique constraint on COLUMN_OBJECT_ID. Because of this, there is no conflict when you add a row with the same COLUMN_OBJECT_ID.

Make COLUMN_OBJECT_ID unique and it should work.

Additionally, if COLUMN_OBJECT_ID is unique, and is what you use for checking existing values in the table, you should probably make it the primary key, instead of the auto increment one you are using right now as the primary key. It will be more efficient both in memory usage, and in performance

Upvotes: 1

Related Questions