Android:Is there a better way to insert and/or update a database entry?

Question

I have a database containing a userId and a note. The user doesn't know if there already is a note in the DB so they write one and click the 'Submit' button. I want to insert this note if there is no note for the userId or update that userId's already existing note:

notesDb.open();
boolean updateResult = notesDb.updateMessage(
        userId,
        details_notes_input.getText().toString());

if(updateResult == true) {
    Log.d("databaseTester", "Updated entry into table");
} else {
    Log.d("databaseTester", "FAILED to update entry into table");
    long insertResult = notesDb.insertMessage(
            userId,
            details_notes_input.getText().toString());
    if(insertResult == -1){
        Log.d("databaseTester", "Failed to insert entry into table");
    } else {
        Log.d("databaseTester", "Inserted entry into table");
    }   
}
notesDb.close();

So, I'm pretty much attempting to 'update' an entry and if I fail then I attempt to 'insert' it. I don't know SQL very well, but I would think there would be a better way. Thanks.

score 13 · Answer 1 · answered Dec 16 '09 at 07:44

13

It sounds like you could use sqlite's REPLACE to solve this problem, using SQLiteDatabase.replace().

answered Dec 16 '09 at 07:44

Dan Lew

81,251
29
178
174

It didn't work for me. I tried _getHelper().getWritableDatabase().replace(TABLE_NAME, null, values);_ Any suggestions? – AlikElzin-kilaka Mar 25 '12 at 16:10
You can achieve this functionality through `ContentProvider` by using passing an extra parameter in your `ContentValues`. I wrote a blog post about it here: [SQLite INSERT OR REPLACE through ContentProvider | Buzzing Android](http://www.buzzingandroid.com/2013/01/sqlite-insert-or-replace-through-contentprovider/) – JesperB Jan 25 '13 at 15:42

score 1 · Answer 2 · answered Jan 17 '13 at 15:09

1

You can catch a constraint exception launched in case of previous ID existence and then update.

try {
    db.insertOrThrow("sometable", null, cv);
} catch (SQLiteConstraintException e) {
    db.update("sometable", cv,"sometable_uid =" + id, null);
}

answered Jan 17 '13 at 15:09

Laranjeiro

2,583
3
20
23

Using exceptions to manage control flow in this way is probably not a good idea. See http://stackoverflow.com/a/729404/504611. – Vicky Chijwani Aug 24 '14 at 20:36

score 0 · Answer 3 · answered Jul 04 '13 at 20:16

0

Try this

result = db.insertWithOnConflict("tablename ", null, cv, -1);

answered Jul 04 '13 at 20:16

user1154390

1,941
2
23
29

Android:Is there a better way to insert and/or update a database entry?

3 Answers3