How to delete all records from table in sqlite with Android?
AndroidSqliteAndroid Problem Overview
My app has two buttons, the first button is for deleting record on user input and the second button is for deleting all records. But when I want to delete data it shows the message
"Your application has been forcefully stopped".
Please check my code and give me some suggestion.
public void deleteAll()
{
//SQLiteDatabase db = this.getWritableDatabase();
// db.delete(TABLE_NAME,null,null);
//db.execSQL("delete * from"+ TABLE_NAME);
db.execSQL("TRUNCATE table" + TABLE_NAME);
db.close();
}
and
public void delete(String id)
{
String[] args={id};
getWritableDatabase().delete("texts", "_ID=?", args);
}
But it shows the following Log cat error.
03-07 15:57:07.143: ERROR/AndroidRuntime(287): Uncaught handler: thread main exiting due to uncaught exception
03-07 15:57:07.153: ERROR/AndroidRuntime(287): java.lang.NullPointerException
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at com.example.MySQLiteHelper.delete(MySQLiteHelper.java:163)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at com.example.Settings$4.onClick(Settings.java:94)
-07 15:57:07.153: ERROR/AndroidRuntime(287): at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:158)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at android.os.Handler.dispatchMessage(Handler.java:99)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at android.os.Looper.loop(Looper.java:123)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at android.app.ActivityThread.main(ActivityThread.java:4203)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at java.lang.reflect.Method.invokeNative(Native Method)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at java.lang.reflect.Method.invoke(Method.java:521)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:791)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:549)
03-07 15:57:07.153: ERROR/AndroidRuntime(287): at dalvik.system.NativeStart.main(Native Method)
Android Solutions
Solution 1 - Android
You missed a space: db.execSQL("delete * from " + TABLE_NAME);
Also there is no need to even include *
, the correct query is:
db.execSQL("delete from "+ TABLE_NAME);
Solution 2 - Android
db.delete(TABLE_NAME, null, null);
or, if you want the function to return the count of deleted rows,
db.delete(TABLE_NAME, "1", null);
From the documentation of SQLiteDatabase delete method:
> To remove all rows and get a count pass "1" as the whereClause.
Solution 3 - Android
To delete all the rows within the table you can use:
db.delete(TABLE_NAME, null, null);
Solution 4 - Android
SQLite doesn't support the TRUNCATE
command. You should use what you've tried in the previous line:
DELETE FROM `TABLE_NAME`;
P.S. You can optimize your program by using the same instance of the database connection for all of your queries to the given database instead of creating a new one for every query.
Solution 5 - Android
There's no need to use "execute" function.The following code worked for me:::
db.delete(TABLE_NAME,null,null);
db.close();
Solution 6 - Android
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("DELETE FROM tablename"); //delete all rows in a table
db.close();
this work for me :)
Solution 7 - Android
try this code to delete all data from a table..
String selectQuery = "DELETE FROM table_name ";
Cursor cursor = data1.getReadableDatabase().rawQuery(selectQuery, null);
Solution 8 - Android
use Sqlit delete function with last two null parameters.
db.delete(TABLE_NAME,null,null)
Solution 9 - Android
Just Write
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("delete from "+TableName);
or
db.delete(tablename,null,null);
Solution 10 - Android
I use this class to handle database.I hope it will help some one in future.
Happy coding.
public class Database {
private static class DBHelper extends SQLiteOpenHelper {
/**
* Database name
*/
private static final String DB_NAME = "db_name";
/**
* Table Names
*/
public static final String TABLE_CART = "DB_CART";
/**
* Cart Table Columns
*/
public static final String CART_ID_PK = "_id";// Primary key
public static final String CART_DISH_NAME = "dish_name";
public static final String CART_DISH_ID = "menu_item_id";
public static final String CART_DISH_QTY = "dish_qty";
public static final String CART_DISH_PRICE = "dish_price";
/**
* String to create reservation tabs table
*/
private final String CREATE_TABLE_CART = "CREATE TABLE IF NOT EXISTS "
+ TABLE_CART + " ( "
+ CART_ID_PK + " INTEGER PRIMARY KEY, "
+ CART_DISH_NAME + " TEXT , "
+ CART_DISH_ID + " TEXT , "
+ CART_DISH_QTY + " TEXT , "
+ CART_DISH_PRICE + " TEXT);";
public DBHelper(Context context) {
super(context, DB_NAME, null, 2);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE_CART);
}
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
db.execSQL("DROP TABLE IF EXISTS " + CREATE_TABLE_CART);
onCreate(db);
}
}
/**
* CART handler
*/
public static class Cart {
/**
* Check if Cart is available or not
*
* @param context
* @return
*/
public static boolean isCartAvailable(Context context) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
boolean exists = false;
try {
String query = "SELECT * FROM " + DBHelper.TABLE_CART;
Cursor cursor = db.rawQuery(query, null);
exists = (cursor.getCount() > 0);
cursor.close();
db.close();
} catch (SQLiteException e) {
db.close();
}
return exists;
}
/**
* Insert values in cart table
*
* @param context
* @param dishName
* @param dishPrice
* @param dishQty
* @return
*/
public static boolean insertItem(Context context, String itemId, String dishName, String dishPrice, String dishQty) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DBHelper.CART_DISH_ID, "" + itemId);
values.put(DBHelper.CART_DISH_NAME, "" + dishName);
values.put(DBHelper.CART_DISH_PRICE, "" + dishPrice);
values.put(DBHelper.CART_DISH_QTY, "" + dishQty);
try {
db.insert(DBHelper.TABLE_CART, null, values);
db.close();
return true;
} catch (SQLiteException e) {
db.close();
return false;
}
}
/**
* Check for specific record by name
*
* @param context
* @param dishName
* @return
*/
public static boolean isItemAvailable(Context context, String dishName) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
boolean exists = false;
String query = "SELECT * FROM " + DBHelper.TABLE_CART + " WHERE "
+ DBHelper.CART_DISH_NAME + " = '" + String.valueOf(dishName) + "'";
try {
Cursor cursor = db.rawQuery(query, null);
exists = (cursor.getCount() > 0);
cursor.close();
} catch (SQLiteException e) {
e.printStackTrace();
db.close();
}
return exists;
}
/**
* Update cart item by item name
*
* @param context
* @param dishName
* @param dishPrice
* @param dishQty
* @return
*/
public static boolean updateItem(Context context, String itemId, String dishName, String dishPrice, String dishQty) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(DBHelper.CART_DISH_ID, itemId);
values.put(DBHelper.CART_DISH_NAME, dishName);
values.put(DBHelper.CART_DISH_PRICE, dishPrice);
values.put(DBHelper.CART_DISH_QTY, dishQty);
try {
String[] args = new String[]{dishName};
db.update(DBHelper.TABLE_CART, values, DBHelper.CART_DISH_NAME + "=?", args);
db.close();
return true;
} catch (SQLiteException e) {
db.close();
return false;
}
}
/**
* Get cart list
*
* @param context
* @return
*/
public static ArrayList<CartModel> getCartList(Context context) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
ArrayList<CartModel> cartList = new ArrayList<>();
try {
String query = "SELECT * FROM " + DBHelper.TABLE_CART + ";";
Cursor cursor = db.rawQuery(query, null);
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
cartList.add(new CartModel(
cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_ID)),
cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_NAME)),
cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_QTY)),
Integer.parseInt(cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_PRICE)))
));
}
db.close();
} catch (SQLiteException e) {
db.close();
}
return cartList;
}
/**
* Get total amount of cart items
*
* @param context
* @return
*/
public static String getTotalAmount(Context context) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
double totalAmount = 0.0;
try {
String query = "SELECT * FROM " + DBHelper.TABLE_CART + ";";
Cursor cursor = db.rawQuery(query, null);
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
totalAmount = totalAmount + Double.parseDouble(cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_PRICE))) *
Double.parseDouble(cursor.getString(cursor.getColumnIndex(DBHelper.CART_DISH_QTY)));
}
db.close();
} catch (SQLiteException e) {
db.close();
}
if (totalAmount == 0.0)
return "";
else
return "" + totalAmount;
}
/**
* Get item quantity
*
* @param context
* @param dishName
* @return
*/
public static String getItemQty(Context context, String dishName) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = null;
String query = "SELECT * FROM " + DBHelper.TABLE_CART + " WHERE " + DBHelper.CART_DISH_NAME + " = '" + dishName + "';";
String quantity = "0";
try {
cursor = db.rawQuery(query, null);
if (cursor.getCount() > 0) {
cursor.moveToFirst();
quantity = cursor.getString(cursor
.getColumnIndex(DBHelper.CART_DISH_QTY));
return quantity;
}
} catch (SQLiteException e) {
e.printStackTrace();
}
return quantity;
}
/**
* Delete cart item by name
*
* @param context
* @param dishName
*/
public static void deleteCartItem(Context context, String dishName) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
try {
String[] args = new String[]{dishName};
db.delete(DBHelper.TABLE_CART, DBHelper.CART_DISH_NAME + "=?", args);
db.close();
} catch (SQLiteException e) {
db.close();
e.printStackTrace();
}
}
}//End of cart class
/**
* Delete database table
*
* @param context
*/
public static void deleteCart(Context context) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
try {
db.execSQL("DELETE FROM " + DBHelper.TABLE_CART);
} catch (SQLiteException e) {
e.printStackTrace();
}
}
}
Usage:
if(Database.Cart.isCartAvailable(context)){
Database.deleteCart(context);
}
Solution 11 - Android
//Delete all records of table
db.execSQL("DELETE FROM " + TABLE_NAME);
//Reset the auto_increment primary key if you needed
db.execSQL("UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME=" + TABLE_NAME);
//For go back free space by shrinking sqlite file
db.execSQL("VACUUM");
Solution 12 - Android
This is working for me. The difference is here with execSQL and rawQuery. The rawQuery use most in searching case and execSQL mostly used in apply operations.
// truncate the table
ArrayList<HashMap<String, String>> getDatabaseName1(String sr) {
SQLiteDatabase sqLiteDatabase=this.getWritableDatabase();
sqLiteDatabase.execSQL("delete from Hotel");
sqLiteDatabase.close();
return null;
}
Solution 13 - Android
you can use two different methods to delete or any query in sqlite android
first method is
public void deleteItem(Student item) {
SQLiteDatabase db = getWritableDatabase();
String whereClause = "id=?";
String whereArgs[] = {item.id.toString()};
db.delete("Items", whereClause, whereArgs);
}
second method
public void deleteAll()
{
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("delete from "+ TABLE_NAME);
db.close();
}
use any method for your use case
Solution 14 - Android
getContentResolver().delete(DB.TableName.CONTENT_URI, null, null);
Solution 15 - Android
Can be usefull.
public boolean deleteAllFood() {
SQLiteDatabase db = dbHelper.getReadableDatabase();
int affectedRows = db.delete(DBHelper.TABLE_NAME_FOOD, null, null);
return affectedRows > 0;
}
public class DBProgram {
private static DBProgram INSTANCE;
private Context context;
private DBHelper dbHelper;
private DBProgram(Context context) {
// burda bu methodu kullanan activity ile eileştiriyoruz
this.dbHelper = new DBHelper(context);
}
public static synchronized DBProgram getInstance(Context context) {
if (INSTANCE == null) {
INSTANCE = new DBProgram(context);
}
return INSTANCE;
}
//**********************************************
public boolean updateById(ProgramModel program) {
SQLiteDatabase database = dbHelper.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(DBHelper.COLUM_NAME_P, program.getProgName());
contentValues.put(DBHelper.COLUM_DAY_P, program.getDay());
contentValues.put(DBHelper.COLUMN_WEIGHT_P, program.getWeight());
contentValues.put(DBHelper.COLUMN_SET_P, program.getSet());
contentValues.put(DBHelper.COLUMN_REPETITION_P, program.getRepetition());
int affectedRows = database.update(DBHelper.TABLE_NAME_PROGRAM, contentValues, "PROG_ID_P = ?", new String[]{String.valueOf(program.getId())});
return affectedRows > 0;
}
//**********************************************
//**********************************************
// TODO
public boolean deleteProgramById(int id) {
SQLiteDatabase database = dbHelper.getReadableDatabase();
int affectedRows = database.delete(DBHelper.TABLE_NAME_PROGRAM, DBHelper.COLUMN_ID_P + "= ?", new String[]{String.valueOf(id)});
// return bize etkilenen sıra sayısınıını temsil eder
return affectedRows > 0;
}
//**********************************************
//***************************************************
public boolean deleteProgramByName(String progName) {
SQLiteDatabase database = dbHelper.getReadableDatabase();
final String whereClause = DBHelper.COLUM_NAME_P + "=?";
final String whereArgs[] = {progName};
int affectedRows = database.delete(DBHelper.TABLE_NAME_PROGRAM, whereClause, whereArgs);
return affectedRows > 0;
}
//***************************************************
//************************************** get Meal
// TODO WEB Get All Meals
public List<ProgramModel> getAllProgram(String name) {
List<ProgramModel> foodList = new ArrayList<>();
ProgramModel food;
SQLiteDatabase database = dbHelper.getReadableDatabase();
final String kolonlar[] = {DBHelper.COLUMN_ID_P,
DBHelper.COLUM_NAME_P,
DBHelper.COLUM_DAY_P,
DBHelper.COLUMN_WEIGHT_P,
DBHelper.COLUMN_SET_P,
DBHelper.COLUMN_REPETITION_P};
final String whereClause = DBHelper.COLUM_DAY_P + "=?";
final String whereArgs[] = {name};
Cursor cursor = database.query(DBHelper.TABLE_NAME_PROGRAM, kolonlar, whereClause, whereArgs, null, null, null);
while (cursor.moveToNext()) {
food = new ProgramModel();
food.setId(cursor.getInt(cursor.getColumnIndex(DBHelper.COLUMN_ID_P)));
food.setProgName(cursor.getString(cursor.getColumnIndex(DBHelper.COLUM_NAME_P)));
food.setDay(cursor.getString(cursor.getColumnIndex(DBHelper.COLUM_DAY_P)));
food.setWeight(cursor.getInt(cursor.getColumnIndex(DBHelper.COLUMN_WEIGHT_P)));
food.setSet(cursor.getInt(cursor.getColumnIndex(DBHelper.COLUMN_SET_P)));
food.setRepetition(cursor.getInt(cursor.getColumnIndex(DBHelper.COLUMN_REPETITION_P)));
foodList.add(food);
}
database.close();
cursor.close();
return foodList;
}
//**************************************
//**************************************insert FOOD
//TODO LOCAL insert Foods
public boolean insertProgram(ProgramModel favorite) {
boolean result = false;
ContentValues contentValues = new ContentValues();
contentValues.put(DBHelper.COLUM_NAME_P, favorite.getProgName());
contentValues.put(DBHelper.COLUM_DAY_P, favorite.getDay());
contentValues.put(DBHelper.COLUMN_WEIGHT_P, favorite.getWeight());
contentValues.put(DBHelper.COLUMN_SET_P, favorite.getSet());
contentValues.put(DBHelper.COLUMN_REPETITION_P, favorite.getRepetition());
SQLiteDatabase database = dbHelper.getWritableDatabase();
long id = database.insert(DBHelper.TABLE_NAME_PROGRAM, null, contentValues);
if (id != 1) {
result = true;
}
database.close();
return result;
}
//***************************************************
// ******************************* SQLITE HELPER CLASS ******************
private class DBHelper extends SQLiteOpenHelper {
private final Context context;
private static final String DATABASE_NAME = "PROGRAM_INFO";
private static final String TABLE_NAME_PROGRAM = "PROGRAM";
private static final int DATABASE_VERSION = 2;
// FOOD
private static final String COLUMN_ID_P = "PROG_ID_P";
private static final String COLUM_NAME_P = "PROG_NAME_P";
private static final String COLUM_DAY_P = "PROG_DAY_P";
private static final String COLUMN_WEIGHT_P = "PROG_WEIGHT_P";
private static final String COLUMN_SET_P = "PROG_SET_P";
private static final String COLUMN_REPETITION_P = "PROG_REPETITION_P";
private final String CREATE_TABLE_PROGRAM = "CREATE TABLE " + TABLE_NAME_PROGRAM +
" (" + COLUMN_ID_P + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ COLUM_NAME_P + " TEXT, "
+ COLUM_DAY_P + " TEXT, "
+ COLUMN_WEIGHT_P + " INTEGER, "
+ COLUMN_SET_P + " INTEGER, "
+ COLUMN_REPETITION_P + " INTEGER)";
private static final String DROP_TABLE_PROGRAM = "DROP TABLE IF EXIST " + TABLE_NAME_PROGRAM;
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE_PROGRAM);
Util.showMessage(context, "Database Created");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(DROP_TABLE_PROGRAM);
Util.showMessage(context, "Database Upgrated");
onCreate(db);
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
super.onDowngrade(db, oldVersion, newVersion);
}
}
}
Solution 16 - Android
this metod delate all data from database
public void deleteAll()
{
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("delete from "+ TABLE_NAME);
db.close();
}
Solution 17 - Android
I have a function that works for me in Unity (in conjunction with the database SQLite).
code C#:
public void DeleteAllObj() {
connectionString = "URI=file:"+Application.dataPath+"/TerrainTools/db/object.sqlite";
using (IDbConnection dbConnection = new SqliteConnection(connectionString)) {
dbConnection.Open();
using(IDbCommand dbCmd =dbConnection.CreateCommand()) {
dbCmd.CommandText = "DELETE FROM 'ObjectMap'";
using (IDataReader reader = dbCmd.ExecuteReader()) {
dbConnection.Close();
reader.Close();
return;
}
}
}
}
Solution 18 - Android
Here is simple way to delete:
public void deleteItem(Item item) {
SQLiteDatabase db = getWritableDatabase();
String whereClause = "id=?";
String whereArgs[] = {item.id.toString()};
db.delete("Items", whereClause, whereArgs);
}
Here whereClause
is optional, passing null will delete all rows in table. delete function will return number of affected row if whereClause
passed otherwise will return 0.
> Important Note: If you want to remove all rows and require count of deleted ones also then pass 1 as whereClause
.
Solution 19 - Android
Using SQLite_NET_PCL v1.6.292 in VStudio - a lot of the docs and comments out there seem out dated(I think).
To delete all records
public Task<int> DeleteAllEntry()
{
return DataBasePath.DeleteAllAsync<tableName>();
}