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?

Veriee

Are you aware of the "UPSERT" command REPLACE? sqlite.org/lang_replace.html - It inserts a new record if not existing or it updates an existing one. – Amplitude

@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 – Veriee

@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". ;) – Amplitude

@BobMalooga well there is rawQuery developer.android.com/reference/android/database/sqlite/…, java.lang.String[], android.os.CancellationSignal) and execSQL developer.android.com/reference/android/database/sqlite/… 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. – Veriee

but unfortunately not in ContentProviders developer.android.com/guide/topics/providers/…. 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 (??). – Amplitude

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. – Riot

Answer 1

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!

Provoke

This is an ingenius idea - exactly what I have been looking for. Thankyou, very appreciated. I upvoted you. – Farming

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 – Turbid

Answer 2

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.

Linlithgow

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. – Provoke

Answer 3

Use REPLACE INTO instead of INSERT INTO to solve the issue

Ungracious