Android sql update or insert

Question

I am trying to find the most efficient logic for the next situation.

I want to implement on my Android app storing in sql most used functions/actions by users.

dbase structure:

| _id | act_name (text unique) | counter (integer) |

code: (src https://stackoverflow.com/a/20568176/8006698)

int rowID = (int) db.insertWithOnConflict(TABLE_NAME, null, cv,SQLiteDatabase.CONFLICT_IGNORE);
if (rowID != -1){
     myLog("Written to base with id = " + rowID);
} else {
     db.execSQL("UPDATE stats SET counter=counter + 1 WHERE act_name = '" + mAction+"'");
     myLog("stats updated");
}

I was surprised, that 'execSQL' doesn't return value as 'update' method. I would use:

int rowID = db.update(...);
if (rowID == 0) {   
    //if not updated then insert
    long insertedID = db.insert(...);
    myLog("Inserted to base ");
} else
    myLog("base updated");

but can't increase here count directly as 'counter=counter + 1' so I've to use 'insertWithOnConflict'. I'm not sure, is it good practice to use it. Waiting for your ideas.

so all you are asking is a way to update sql with 'counter += 1' ? — Jisu Hong, Jul 23 '17 at 13:34

score 0 · Answer 1 · answered Jul 24 '17 at 12:22

0

Replace this

 db.execSQL("UPDATE stats SET counter=counter + 1 WHERE act_name = '" + mAction+"'");

Use this

 ContentValues values=new ContentValues();
 values.put("counter",1)
 int rowCount=db.update("stats",values,"act_name=?",new String[]{mAction});

you get update count and do whatever you want.

answered Jul 24 '17 at 12:22

Santhosh

160
7

How can I increase a value of counter by 1 (++counter) by using contentvalues? – Timur Otajonov Jul 27 '17 at 06:41
Before update query call you need to query counter from table and get counter and update value counter+1. Like this, int counter=//select your counter value, values.put("counter",counter+1); – Santhosh Jul 27 '17 at 06:55
Thanks for answer! My research was like could I update counter by using one sql query, and get result from sql like "Hey, I updated your base" )). So, I still can't find logic more optimal than this: int rowcount = (int) db.insertWithOnConflict(myTable, null, contentValue, CONFLICT_IGNORE); if (rowCount == -1){ db.execSQL("update query...'"); } – Timur Otajonov Jul 27 '17 at 16:08

Android sql update or insert

1 Answers1