How to delete all Table records in Sqlite?

Question

I get JSON value from a server and save the data into a database in Sqlite in Android, I create a database and a table with SQLiteOpenHelper class:

public class ActivityTableDBOpenHelper extends SQLiteOpenHelper {

private static final String LOGTAG = "ATDBOpenHelper";

private static final String DATABASE_NAME = "MyDatabase.db";
private static final int DATABASE_VERSION = 1;

public static final String ACTIVITY_TABLE = "'activity'";
public static final String COLUMN_ACTIVITY_ID = "'activityId'";
public static final String COLUMN_USER_ID = "'userId'";
public static final String COLUMN_PROGRAM_ID = "'programId'";
public static final String COLUMN_ORDER = "'order'";
public static final String COLUMN_G_DATE = "'gDate'";
public static final String COLUMN_J_DATE = "'jDate'";

private static final String TABLE_CREATE =
        "CREATE TABLE " + ACTIVITY_TABLE + " (" +
                COLUMN_ACTIVITY_ID + " INTEGER(20), " +
                COLUMN_USER_ID + " INTEGER(20), " +
                COLUMN_PROGRAM_ID + " INTEGER(20), " +
                COLUMN_ORDER + " VARCHAR(200), " +
                COLUMN_G_DATE + " VARCHAR(200), " +
                COLUMN_J_DATE + " VARCHAR(200) " +
                ")";

public ActivityTableDBOpenHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(TABLE_CREATE);
    Log.i(LOGTAG, "Activity Table has been created!");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS" + ACTIVITY_TABLE);
    onCreate(db);
}

}

I can successfully insert data into activity table, now I want to delete all records when user click a button, I use this line code and I get error:

SQLiteDatabase database;
database.delete(ActivityTableDBOpenHelper.ACTIVITY_TABLE,null,null);

this is my activity class:

@Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_activity_table);


        btnDeleteATData = (Button) findViewById(R.id.btnDeleteATData);


        dataSource = new ActivityTableDataSource(this);
        dataSource.open();


        btnDeleteATData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                database=new SQLiteDatabase();
                  database.delete(ActivityTableDBOpenHelper.ACTIVITY_TABLE,null,null);
            }
        });       
    }

this is error:

java.lang.NullPointerException: Attempt to invoke virtual method 'int android.database.sqlite.SQLiteDatabase.delete(java.lang.String, java.lang.String, java.lang.String[])' on a null object reference
            at com.rastari.salar.salytest.ActivityTableActivity$2.onClick(ActivityTableActivity.java:69)
            at android.view.View.performClick(View.java:4780)
            at android.view.View$PerformClick.run(View.java:19866)
            at android.os.Handler.handleCallback(Handler.java:739)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:135)
            at android.app.ActivityThread.main(ActivityThread.java:5254)
            at java.lang.reflect.Method.invoke(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:372)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

how can I delete all records from activity table?

Thank you.

`DELETE FROM activity` But your issue is `null object reference`. Another issue is your table name and field names enclosed in single quotes (**'**) — Phantômaxx, Jun 26 '15 at 11:30
**java.lang.NullPointerException**: Attempt to invoke virtual method 'int **android.database.sqlite.SQLiteDatabase**.delete(java.lang.String, java.lang.String, java.lang.String[])' on a **null object reference** — pskink, Jun 26 '15 at 11:32
possible duplicate of [What is a Null Pointer Exception, and how do I fix it?](http://stackoverflow.com/questions/218384/what-is-a-null-pointer-exception-and-how-do-i-fix-it) — Selvin, Jun 26 '15 at 11:47

score 1 · Accepted Answer · answered Jun 26 '15 at 11:32

1

You should first initialize you SQLiteDatabase, so convert this:

SQLiteDatabase database;
database.delete(ActivityTableDBOpenHelper.ACTIVITY_TABLE,null,null);

to this:

SQLiteDatabase database = new SQLiteDatabase(this); // or dbHelper.getWritableDatabase(); if you have a dbHelper
database.delete(ActivityTableDBOpenHelper.ACTIVITY_TABLE,null,null);

Else the database object is null and you get the error. Here you could find a whole example for all operations http://www.vogella.com/tutorials/AndroidSQLite/article.html

answered Jun 26 '15 at 11:32

Gabriella Angelova

2,985
1
17
30

I define `SQLiteDatabase database;` in top of my activity class, I can't write `SQLiteDatabase database = new SQLiteDatabase(this);` – Salar Rastari Jun 26 '15 at 11:39
the definition should be written in your `onCreate` method or inside another method and it will be then: `SQLiteDatabase database; `at the top and `database = dbHelper.getWritableDatabase();` in the method – Gabriella Angelova Jun 26 '15 at 11:41

score 0 · Answer 2 · answered Jun 26 '15 at 11:35

0

db.execSQL("delete from "+ TABLE_NAME);

answered Jun 26 '15 at 11:35

Ajay Maurya

69
6

1

the database object is null, the problem is not in the sql query – Gabriella Angelova Jun 26 '15 at 11:36

score 0 · Answer 3 · answered Jun 26 '15 at 11:35

The problem with your call

SQLiteDatabase database;
database.delete(ActivityTableDBOpenHelper.ACTIVITY_TABLE,null,null);

is that your database-object is null. Call SQLiteDatabase db = getWritableDatabase() and proceed.

To delete information from your database or specific tables you can also use basic SQL-Queries: http://www.w3schools.com/sql/sql_delete.asp

db.rawQuery("DELETE FROM " + table + " WHERE " + args);

score 0 · Answer 4 · edited Jun 26 '15 at 14:09

0

//ourDb ->SqliteDatabase object
//TABLE_NAME -> 
public void deleteDatafromAllTables() {
    ourDb.execSQL("delete from " + TABLE_NAME);
}

edited Jun 26 '15 at 14:09

beresfordt

5,088
10
35
43

answered Jun 26 '15 at 12:06

bala

89
1
5

score 0 · Answer 5 · answered Jun 26 '15 at 12:10

I found where I was wrong :D

thanks to @Gabriella Angelova

I have to initializing SQLiteDatabase and SQLiteOpenHelper

public class ActivityTableActivity extends ActionBarActivity {

    SQLiteDatabase database;
    ActivityTableDBOpenHelper dbOpenHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_activity_table);

        dbOpenHelper = new ActivityTableDBOpenHelper(this);
        database = dbOpenHelper.getWritableDatabase();

        btnDeleteATData = (Button) findViewById(R.id.btnDeleteATData);

        dataSource = new ActivityTableDataSource(this);
        dataSource.open();

        btnDeleteATData.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {        
                database.delete(ActivityTableDBOpenHelper.ACTIVITY_TABLE, null, null);
            }
        });

    }

How to delete all Table records in Sqlite?

5 Answers5

Linked