How to update ,insert and delete the rows in Sqlite?

Question

I am new to android development and i have gone through some tutorials and i wrote an application using sqlite Db.

I have couple of issues as mentioned below 1.How to update rows in my DB? 2.Delete the updated rows? 3.How to insert new rows?

//below code is for updating the rows

SQLiteDatabase db = dbHandler.getWritableDatabase();
for (int i = 0; i <= 100000; i = i + 100) {
    ContentValues cValues = new ContentValues();
    cValues.put(DbHandler.COLUMN_ID, i + 1);
    cValues.put(DbHandler.COLUMN_NAME, "Test");
    cValues.put(DbHandler.COLUMN_SERIALNUMBER, String.valueOf(i + 1));
    cValues.put(DbHandler.COLUMN_COUNTALL, 10);
    cValues.put(DbHandler.COLUMN_COUNTHIGH, i + 1);
    cValues.put(DbHandler.COLUMN_COUNTLOW, i + 1);
    cValues.put(DbHandler.COLUMN_RAWDATA, i + 1);
    db.update(DbHandler.TABLE_USERS, cValues, DbHandler.COLUMN_ID,new String[]{String.valueOf(i)});
}
db.close();

Delete the specific rows - Below code is clearing total the DB instead of specific rows

for (int i = 0; i <= 100000; i = i + 100) {
    db.delete(DbHandler.TABLE_USERS, i+"", null);
}

Answer 1

If you want to insert rows to the table, use ContentValues (just like your update code) and the insert() method:

SQLiteDatabase db = dbHandler.getWritableDatabase();
for (int i = 0; i < 3; i++) {
    ContentValues cValues = new ContentValues();
    cValues.put(DbHandler.COLUMN_ID, i + 1);
    cValues.put(DbHandler.COLUMN_NAME, "Test");
    cValues.put(DbHandler.COLUMN_SERIALNUMBER, String.valueOf(i + 1));
    cValues.put(DbHandler.COLUMN_COUNTALL, 10);
    cValues.put(DbHandler.COLUMN_COUNTHIGH, i + 1);
    cValues.put(DbHandler.COLUMN_COUNTLOW, i + 1);
    cValues.put(DbHandler.COLUMN_RAWDATA, i + 1);
    db.insert(DbHandler.TABLE_USERS, null, cValues);
}
db.close();

The above code will insert 3 rows in the table:

1 Test 1 10 1 1 1
2 Test 2 10 2 2 2
3 Test 1 10 3 3 3

Now, say that you want to update the column COLUMN_COUNTHIGH to 100 and the column COLUMN_COUNTLOW to 0 of the row with id = 2, then you use the update() method:

SQLiteDatabase db = dbHandler.getWritableDatabase();
ContentValues cValues = new ContentValues();
cValues.put(DbHandler.COLUMN_COUNTHIGH, 100);
cValues.put(DbHandler.COLUMN_COUNTLOW, 0);
db.update(DbHandler.TABLE_USERS, cValues, DbHandler.COLUMN_ID + " = ?", new String[] {String.valueOf(2)});
db.close();

So the update() method's arguments are:
the table's name
the ContentValues
the WHERE clause which contains the column names and the placeholders ? that will decide which row(s) will be updated
and the value(s) of the column(s) which will be set at the place of ?
If you want to delete say the row with id = 1, use the delete() method:

db.delete(DbHandler.TABLE_USERS, DbHandler.COLUMN_ID + " = ?", new String[] {String.valueOf(1)}); 

The arguments are similar as of those of the update() method (without the ContentValues).