How do i delete specific rows in SQLite Database

Question

I am new to android and i am having trouble understanding the delete function in sqlite database.I have created my database using the string

private static final String TABLE_NAME="NameInfo";
    private static final String POSITION_DB ="_id";
    private static final String Name_DB ="name";
    private static final String JSONOBJECT_DB="json";
    private static final String TIMESTAMP_DB="time";
    private static final String USERRATING_DB="userrating";
    private static final String DATABASE_NAME ="database.db";    
private final String createDb ="create table if not exists "+TABLE_NAME +" ("
                + POSITION_DB + " integer primary key, "
                + NAME_DB + " text not null, "
                + JSONOBJECT_DB + " text not null, "
                + USERRATING_DB + " text not null, "
                +TIMESTAMP_DB + " text not null); ";

Now when i start my app i want that all rows that were added more than 2 days ago should be deleted

so i am planning to do something like this

long currentdate =new date().getTime();

and than check the difference between currenttime-Long.Valueof(TIMESTAMP_DB) field for each rows of the table and if it is more than 2*24*60*60 than delete that row

Can someone please tell me how can i use the below function to achieve the above

public int delete (String table, String whereClause, String[] whereArgs)

i am not sure what should i write in whereClause and whereArgs.

I would be really grateful if someone can tell me a even better and simple approach than this.

PS i also tried doing by execSQL statement but was not able to write the complete query by database.execSQL("Delete * from "+TABLE_NAME+" WHERE = "+currentdate - Long.ValueOf(?) >2*24*60*60 ;")

Thanks in advance.

Answer 1

You could use a query like this:

db.execSQL("DELETE FROM " + TABLE_NAME +
           " WHERE " + currentdate + " - " + TIMESTAMP_DB + " > 2*24*60*60");

For the update method, write the SQL expression in the WHERE clause in the whereClause parameter; whereArgs is needed only for string values:

db.update(TABLE_NAME, currentdate + " - " + TIMESTAMP_DB + " > 2*24*60*60", null);

Answer 2

You can also do like this::

db.execSQL(String.format("DELETE FROM %s WHERE %s = %d",
                Table_NAME,Table_ColumnName,Integer.parseInt(Value)));

It will execute the statement Directly.

and as you have asked WhereClause means the ColumnNames and expression that you want to use. and Where args is String array so you need to pass argument values for Expression that you have written.

db.delete(Table_NAME, ColumnName+" = ", String[]{Value});

Answer 3

This is very "huge" question and can have more working approaches depend on your personal requirements e.q security, performance etc.

At first i recommend to you use parametrized statements instead of your "harcoded" which are unsafe and not much human readable, so use placeholders e.q.

select * from test where id = ?

Second, for delete rows from table use API build-in function delete(). And as @Chintan Rathod pointed out, your delete statement is not valid statement.

Now to your question. Since you want to delete rows due to specified timestamp i suggest you to insert each row with specified date in certain date format (you can simply use SimpleDateFormat. Then there are more approaches how you can do it. I prefer at first query a table and place simple if condition if between actual date and date stored in row is difference 2 days then save his rowId. Finally, you have row's ids you want to delete.

But, right now you don't know exactly how many rows you want to delete so you need to:

1. delete records in loop (API is less than 11)
2. Create dynamically query with IN clause
3. Since Android API 11, there is method that allows delete multiple records. Here you can create "dynamic" statement due to size of ids (stored in some dynamic array).

Examples:

First approach:

List<Integer> ids = new ArrayList<Integer>();
...
for (int id: ids) {
   db.delete("table", "_id = ?", new String[] {String.valueOf(id)});
}

Second approach:

List<Integer> ids = new ArrayList<Integer>();
...
StringBuilder b = new StringBuilder("delete from table where _id IN(");
String[] whereArgs = new String[ids.size()];
int index = 0;
for (int id: ids) {
   whereArgs[index] = String.valueOf(id);
   b.append("?");
   if (index < ids.size() - 1) {
      b.append(",");
   }
   index++;
}
b.append(")");

db.execSQL(b.toString(), whereArgs);

Third approach:

Same as above but you can use SQLiteStatement:

SQLiteDatabase db; // instantiated SQLiteDatabase
SQLiteStatement stm = db.compileStatement(b.toString());
stm.executeUpdateDelete();

Note: Since you want to delete multiple records (sometimes you need to delete 10 000 and more rows) try to think about TRANSACTIONS which rapidly increase performance and security.

Answer 4

public void deleteContact(Integer id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("AddressList",
                "id = ? ",
                new String[]{Integer.toString(id)});
}

or

you can use raw SQL query to Delete row

public void deleteContact(Integer id) {
  SQLiteDatabase database = this.getWritableDatabase();
 String s = "DELETE FROM AddressList WHERE item_id=" + id;
database.execSQL(s);
}

here AddressList is your TABLE name