Use Insert or Replace in ContentProvider

Question

When I want to check and see if something exists in my ContentProvider what I usually do is something similar to this

Cursor c = getContentResolver().query(table,projection,selection,selectionArgs,sort);

if(c != null && c.moveToFirst()){

    //item exists so update

}else{

    //item does not exist so insert

}

but that means I always have to make possibly an unnecessary Database call slowing things down especially the greater number of checks on the database I need to do. There has to be a better way to handle this so I dont always have to query first.

I looked at this question

Android Contentprovider - update within an insert method

but using insertWithOnConflict only checks the primary key id and in my case that will not work because what I am checking is not the id but a unique string from a server database.

so is there something I can do with the Content Provider so I dont always have to make a query to check if the item exists in it already?

Are you aware of the "UPSERT" command **REPLACE**? https://www.sqlite.org/lang_replace.html - It inserts a new record if not existing or it updates an existing one. — Phantômaxx, May 02 '14 at 08:51
@BobMalooga No I am not aware of this but can you use it in a `ContentProvider`? all a content provider has is insert, update, replace or delete — tyczj, May 02 '14 at 13:26
@tyczi No... sorry, I wasn't aware of the ContentProvider limits. I thought you could use a rawQuery or an execSQL to get and set data, respectively. Now it appears that a ContentProvider unfortunately doesn't offer these facilities. So - I learned somethig about ContentProviders (never used yet - and probably will skip in the future). And you are now aware that SQLite offers the "upserts". ;) — Phantômaxx, May 02 '14 at 13:38
@BobMalooga well there is `rawQuery` http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery(java.lang.String, java.lang.String[], android.os.CancellationSignal) and `execSQL` http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL(java.lang.String) I just have to use `getWritableDatabase()` from the `SQLiteOpenHelper` class. I am certainly not seasoned in advances SQL stuff, just basic query and inserts etc. — tyczj, May 02 '14 at 13:46
but unfortunately not in ContentProviders http://developer.android.com/guide/topics/providers/content-provider-basics.html. I only use rawQueries and execSQL because I'm way too connected to SQL (having worked for ages with Access and SQL Server in VB and VB.NET) - it gives me more freedom (??). — Phantômaxx, May 02 '14 at 13:51
I usually do an update, then if the number of affected rows returned is 0, I do an insert. This works well if you know you'll usually be updating. If you're usually inserting you can do the operations the other way around with an ON CONFLICT clause on the table to IGNORE conflicts. — myanimal, May 02 '14 at 14:27

score 23 · Accepted Answer · edited May 02 '14 at 15:30

You can have UNIQUE constraint on columns different than ID one. Example:

CREATE TABLE TEST (_id INTEGER PRIMARY KEY AUTOINCREMENT, server_id INTEGER NOT NULL, name TEXT, UNIQUE(server_id))

Having this table, in the insert method of your Content Provider you can do something like this:

@Override
    public Uri insert(Uri uri, ContentValues contentValues) {
        final SQLiteDatabase db = mDatabase.getWritableDatabase();
        final int match = mUriMathcer.match(uri);
        switch (match) {
            case TEST:
                insertOrUpdateById(db, uri, "TEST",
                        contentValues, "server_id");
                getContext().getContentResolver().notifyChange(uri, null, false);
                return Contract.Test.buildTestUri(contentValues.getAsString("server_id"));
            default:
                throw new UnsupportedOperationException("Unknown uri: " + uri);
        }
    }

/**
 * In case of a conflict when inserting the values, another update query is sent.
 *
 * @param db     Database to insert to.
 * @param uri    Content provider uri.
 * @param table  Table to insert to.
 * @param values The values to insert to.
 * @param column Column to identify the object.
 * @throws android.database.SQLException
 */
private void insertOrUpdateById(SQLiteDatabase db, Uri uri, String table,
                                ContentValues values, String column) throws SQLException {
    try {
        db.insertOrThrow(table, null, values);
    } catch (SQLiteConstraintException e) {
        int nrRows = update(uri, values, column + "=?",
                new String[]{values.getAsString(column)});
        if (nrRows == 0)
            throw e;
    }
}

I hope it helps. Cheers!

This is an ingenius idea - exactly what I have been looking for. Thankyou, very appreciated. I upvoted you. — DEzra, Mar 30 '15 at 10:47
What should I do if I want to return *Uri* while inserting record, Uri must be created with `Inserted Id`. You has taken `server_id` but I want `_id` — Pratik Butani, Aug 12 '16 at 06:35

score 6 · Answer 2 · edited Aug 22 '16 at 10:51

6

Edy Bolos answer can be simply written as

CREATE TABLE TEST ( _id INTEGER PRIMARY KEY AUTOINCREMENT, server_id INTEGER NOT NULL, name TEXT UNIQUE ON CONFLICT REPLACE);

Add UNIQUE ON CONFLICT REPLACE in create table query.

edited Aug 22 '16 at 10:51

Piyush

1,528
2
24
39

answered Jan 12 '16 at 09:24

Neeraj Nama

1,562
1
18
24

5

It's not the same, because `ON CONFLICT REPLACE` will replace the whole row, potentially losing some columns, whereas my solution updates the columns that you specify, without losing data in other columns. – Eduard B. Jun 23 '16 at 10:38

score 0 · Answer 3 · answered Jul 12 '18 at 10:27

0

Use REPLACE INTO instead of INSERT INTO to solve the issue

answered Jul 12 '18 at 10:27

vishnuc156

940
12
10

Use Insert or Replace in ContentProvider

3 Answers3