SQLiteDatabase insert or replace if changed

Question

I'm looking for a way to fill/update my SQLiteDatabase table. On some occasions I need to be notified (by certain return value). Each row contains a url of a file that needs to be downloaded later.

row does not exist yet: insert it and notify me that this row should be listed for download.
row does exist but the one I'm trying to insert contains the same values as the one already in the db: do nothing, do not notify me that this should be downloaded.
row does exist and the one I'm trying to insert contains different values as the one already in the db: replace this row, notify me that this should be downloaded.

I've been looking for the answer but can't seem to find something good. Should I use insertWithOnConflict(), replace(), replaceOrThrow(), ...

Thanks!

Why not simply perform a query first and then insert or update depending on the result? — Thijs Mergaert
– Thijs Mergaert, Commented Oct 2, 2013 at 9:59
@ThijsMergaert I'm hoping to find a way that handles this as quick as possible. It's possible that I'll have about 1000 rows each time — dumazy
– dumazy, Commented Oct 2, 2013 at 10:03
@dumazy The closest thing to what you're trying to do is using insertWithOnConflict() with CONFLICT_REPLACE as conflict algorithm, combined with a column with a UNIQUE constraint, but that will only respect the 1st and 3rd cases of the ones you listed. In the 2nd case, it will delete the old row and insert a new one anyway, at least that's how I read the documentation. See link. But even that mentions that its behaviour might change in the future. — Thijs Mergaert
– Thijs Mergaert, Commented Oct 2, 2013 at 11:56

laalto · Accepted Answer · 2014-02-11 20:36:49Z

2

Do it in two steps wrapped in a single sqlite transaction:

Query by rowid. If no rowid or or no result found, insert new and download.
Compare url of new row and row retrieved in step 1. Only if the urls are different, update the row and download, otherwise no-op.

edited Feb 11, 2014 at 20:36

answered Oct 2, 2013 at 10:02

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dumazy Over a year ago

Might this cause performance problems if I have about a 1000 rows?

laalto Over a year ago

If you're worried about performance, bundle up multiple query-inserts in the same transaction (say, 100 in each) to spend less time waiting for I/O.

Stathis Andronikos · Accepted Answer · 2013-10-02 10:13:27Z

First, you query if value exist in DB with cursor & if it's null you make an insert otherwise you make an update if the required field is different from what you have already. In any other case do nothing // Some pseudocode

Cursor cursor=_db.query("TABLE", null, TABLEID + "=?", new String[]{id}, null, null, null); if(cursor.getCount()<1) // row Not Exist { cursor.close(); ....assign values SQLiteDatabase _db.insert("TABLE_Name", null, Table_object); download ...ok } cursor.moveToFirst(); if( cursor[field] != "same_value" ) { SQLiteDatabase _db.update("TABLE_Name", null, Table_object); download ...ok }

Collectives™ on Stack Overflow

SQLiteDatabase insert or replace if changed

2 Answers 2

2 Comments

Comments

Hot Network Questions