Android SQLite Insert or Update

Question

as can be seen in the documentation the syntax to make insert or update is : INSERT OR REPLACE INTO <table> (<columns>) VALUES (<values>), my question is there any function that merge the following ?

public long insert (String table, String nullColumnHack, ContentValues values)  public int update (String table, ContentValues values, String whereClause, String[] whereArgs) 

or it has to be done with a prepared SQL statement and rawQuery?

What's the best practices to do an insert or update in Android?

Answer 1

I believe that you are asking how to INSERT new rows or UPDATE your existing rows in one step. While that is possible in a single raw SQL as discussed in this answer, I found that it easier to do this in two steps in Android using SQLiteDatabase.insertWithOnConflict() using CONFLICT_IGNORE for conflictAlgorithm.

ContentValues initialValues = new ContentValues(); initialValues.put("_id", 1); // the execution is different if _id is 2 initialValues.put("columnA", "valueNEW");  int id = (int) yourdb.insertWithOnConflict("your_table", null, initialValues, SQLiteDatabase.CONFLICT_IGNORE); if (id == -1) {     yourdb.update("your_table", initialValues, "_id=?", new String[] {"1"});  // number 1 is the _id here, update to variable for your code } 

This example assumes that the table key is set for column "_id", that you know the record _id, and that there is already row #1 (_id=1, columnA = "valueA", columnB = "valueB"). Here is the difference using insertWithOnConflict with CONFLICT_REPLACE and CONFLICT_IGNORE

• CONFLICT_REPLACE will overwrite existing values in other columns to null (ie. columnB will become NULL and the result will be _id=1, columnA = "valueNEW", columnB = NULL). You lose existing data as result and I do not use it in my code.
• CONFLICT_IGNORE will skip the SQL INSERT for your existing row #1 and you will SQL UPDATE this row in the next step preserving the content of all other columns (ie. the result will be _id=1, columnA = "valueNEW", columnB = "valueB").

When you attempt to insert new row #2 which does not exist yet, the code will only execute the SQL INSERT in the first statement insertWithOnConflict (ie. the result will be _id=2, columnA = "valueNEW", columnB = NULL).

Beware of this bug which is causing SQLiteDatabase.CONFLICT_IGNORE to malfunction on API10 (and probably API11). The query is returning 0 instead of -1 when I test on Android 2.2.

If you do not know the record key _id or you have a condition that will not create a conflict, you can reverse the logic to UPDATE or INSERT. This will keep your record key _id during UPDATE or create a new record _id during INSERT.

int u = yourdb.update("yourtable", values, "anotherID=?", new String[]{"x"}); if (u == 0) {     yourdb.insertWithOnConflict("yourtable", null, values, SQLiteDatabase.CONFLICT_REPLACE); } 

The above example assumes that your just want to UPDATE timestamp value in the record for example. If you call insertWithOnConflict first, INSERT will create new record _id due to the difference in the timestamp condition.

Answer 2

this is your method SQLiteDatabase.insertWithOnConflict(). to understand what it does refer to this document on sqlite