1

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.

Improve this question
3
  • 2
    delete never have * in its query string.. Commented Jul 29, 2013 at 6:30
  • oh thanks a lot Chintan i used * because i wanted to remove the entire row.Can you plesae tell me what should i used instead Commented Jul 29, 2013 at 6:34
  • please read my answer my friend. Delete will remove a row at a time if condition is true. you can not remove/delete a field of a row :) Commented Jul 29, 2013 at 6:35

4 Answers 4

Reset to default
2

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Can something like this work as well db.execSQL(String.format("DELETE FROM %s WHERE %s < %d", Table_NAME,Table_ColumnName,currentDate-2*24*60*60))
The only doubt i have in the above query is that since subraction wont happen if we write like currentdate+"-"+TIMESTAMP_DB as it will take them as a string
Appending the value of the currentdate variable to the SQL string will not result in a string value in the SQL expression; the result would be something like 12345678 - time > 2*24*60*60, which is perfectly OK.
1

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});
Improve this answer

5 Comments

thanks armaan i think something like this can work db.execSQL(String.format("DELETE FROM %s WHERE %s < %d", Table_NAME,Table_ColumnName,currentDate-2*24*60*60));
first calculate date in any variable and then pass it in Query string so it would be easier.
wrote a function like this public void deleteOldRows(SQLiteDatabase database){ long nowTime = new Date().getTime(); long difference = nowTime -2*24*60*60; database.execSQL(String.format("DELETE FROM %s WHERE %s < %d", TABLE_NAME,TIMESTAMP_DB,String.valueOf(difference))); }
Here you have used difference in String does it checks the condition well??
because using '<' in string will not work i think. you will need long or double values for it. check if it works.
1

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.

Improve this answer

4 Comments

Hi Sajmon thanks a lot for the detailed answer can i use a function like this also to delete the row public void deleteOldRows(SQLiteDatabase database) { long nowTime = new Date().getTime(); long difference = nowTime -2*24*60*60; database.execSQL(String.format("DELETE FROM %s WHERE %s < %d", TABLE_NAME,TIMESTAMP_DB,String.valueOf(difference))); }
@bourne you can try, i never used your appproach so i cant tell you if it will works for 100%.
@Sajmon Please note that the Android API replaces parameters with string values, so the SQL will not work unless the comparions are against columns that happen to have number affinity. In Android, parameters should be used only for strings.
@CL. thanks but i never have problem with thing you re describing. And im working with SQLiteDatabase a lot.
0 
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.