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

Answer 3

SQLiteDatabase.replace() does this, it basically calls:

insertWithOnConflict(table, nullColumnHack, initialValues, CONFLICT_REPLACE);

Too bad the documentation is not very clear.

Answer 4

The operation name for that is "upsert" and how I solve it is identifying the columns of your table that make a row UNIQUE.

Example: _id, name, job, hours_worked

The columns which we'll use are name and job.

private int getID(String name, String job){
    Cursor c = dbr.query(TABLE_NAME,new String[]{"_id"} "name =? AND job=?",new String[]{name,job},null,null,null,null);
    if (c.moveToFirst()) //if the row exist then return the id
        return c.getInt(c.getColumnIndex("_id"));
    return -1;
}

---

In your database manager class:

public void upsert(String name, String job){
    ContentValues values = new ContentValues();
    values.put("NAME",name);
    values.put("JOB",job);

    int id = getID(name,job);
    if(id==-1)
        db.insert(TABLE_NAME, null, values);
    else
        db.update(TABLE_NAME, values, "_id=?", new String[]{Integer.toString(id)});
}

Answer 5

SQLiteDatabase.replace() is probably what you are looking for. I haven't tried it but the doc says it returns the row ID of the newly inserted row, so it may work.

Answer 6

I had the same issue, but I realized when my object already has an Id it should be updated and when it does not have an Id it should be inserted so this is step by step what I did to resolve the issue:

1- in your object getId use Integer or initialize the Id how you see fit: here is my code

public Integer getId() {
    return id;
}

2- check the Id in your method for insert or update after you put everything in ContentValues:

if(myObject.getId()!= null ){
        int count = db.update(TABLE_NAME,myContentValues,ID + " = ? ",
                new String[]{String.valueOf(myObject.getId())});
        if(count<=0){
            //inserting content values to db
            db.insert(TABLE_NAME, null, myContentValues);
        }
    } else {
        db.insert(TABLE_NAME, null, myContentValues);
    }

what happens here is that I check for Id if does exist I update that row but if update method returns -1 it means there were no rows with that Id so I insert the row, and if it does not have an Id I insert it.

hope this helps.

Answer 7

What about replaceOrThrow(String table, String nullColumnHack, ContentValues initialValues)

Docs say... Convenience method for replacing a row in the database. Inserts a new row if a row does not already exist.

Basically it calls insertWithOnConflict

Answer 8

for me, none of the approaches are work if you don't have "_id" (Primary Key)

you should first call update, if the affected rows are zero, then insert it with ignore:

 String selection = MessageDetailTable.SMS_ID+" =?";
 String[] selectionArgs =  new String[] { String.valueOf(md.getSmsId())};

 int affectedRows = db.update(MessageDetailTable.TABLE_NAME, values, selection,selectionArgs);

 if(affectedRows<=0) {
     long id = db.insertWithOnConflict(MessageDetailTable.TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_IGNORE);
 }

Answer 9

You can do like this.

        sqliteDatabase?.let {
        // 1. use sql to do this
        // val sql = "insert or replace into $table (key, value) values ('$key', '${obj.toString()}')"
        
        // 2. use update and ContentValues
        // it.execSQL(sql)
        // it.replace(table, "", values)

        // 3. try to update if affected row count is 0, do insert
        val updateRow = it.update(table, values, "$column_key = ?", arrayOf(key))
        Log.d(TAG, "update row: $updateRow")
        if (updateRow == 0) {
            val rowId = it.insertWithOnConflict(table, "", values, SQLiteDatabase.CONFLICT_IGNORE)
            Log.d(TAG, "insert row: $rowId")
        }
    }