Use Insert or Replace in ContentProvider

Question

When I want to check and see if there is something in my ContentProvider , what I usually do is something like 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 this means that I always need to make a possible unnecessary database call slowing down, especially the more checks in the database that I need to do. There should be a better way to handle this, so I don't always have to ask first.

I reviewed this question

Android Contentprovider - update in insert method

but with insertWithOnConflict it only checks the identifier of the primary key, and in my case this will not work, because I do not check the identifier, but a unique row from the server database.

Is there something I can do with the Content Provider, so I don’t always have to make a request to check if an element exists in it?

Answer 1

You may have a UNIQUE constraint for columns other than identifier 1. Example:

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

Using 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; } } 

Hope this helps. Hooray!

Answer 2

Edie Bolos answer can simply be 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 to the table creation query.

Answer 3

Edie Bolos answer may again be written as

 private void insertOrUpdateById(SQLiteDatabase db, Uri uri, String table, ContentValues values, String column) throws Exception { long id = db.insertWithOnConflict(table, column, values, SQLiteDatabase.CONFLICT_REPLACE); if(id < 0){ throw new Exception(); } }