Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to delete a single row in android sqlite

Tags:

android

sqlite

I have sqlite data represented on a ListView by a CustomListAdapter.On clicking a row an alert dialogue pops up that prompts the user to delete the single row from sqlite in my activity:

private void deleteDialog() {
        AlertDialog.Builder alertDialog = new AlertDialog.Builder(MyCart.this);
        alertDialog.setCancelable(false);
        alertDialog.setMessage("Delete item?");
        alertDialog.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                myDb.deleteSingleContact(toString());
            }
        });
        alertDialog.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                dialog.cancel();

            }
        });
        alertDialog.show();
    }

On my DBHelper.java:

 public void deleteSingleContact(String title){

        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(CONTACTS_TABLE_NAME, CONTACTS_COLUMN_TITLE + "=?", new String[]{title});
//KEY_NAME is a column name
    }

However the above code doesn't delete anything.I guess its the function i havent done correctly.Any suggestions?

Full DBHelper.java

public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "MyDBName.db";
    public static final String CONTACTS_TABLE_NAME = "contacts";
    public static final String CONTACTS_COLUMN_ID = "id";
    public static final String CONTACTS_COLUMN_TITLE = "title";
    public static final String CONTACTS_COLUMN_AMOUNT = "amount";
    public static final String CONTACTS_COLUMN_DESC = "description";

    private HashMap hp;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(
                "create table contacts " +
                        "(id integer primary key, title text,amount float,description text)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }

    public boolean insertContact(String title,  float amount, String description) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();

        contentValues.put("title", title);
        contentValues.put("amount", amount);
        contentValues.put("description", description);


        db.insert("contacts", null, contentValues);
        return true;
    }

    public Cursor getData(int id) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from contacts where id=" + id + "", null);
        return res;
    }

    public int numberOfRows() {
        String countQuery = "SELECT  * FROM " + CONTACTS_TABLE_NAME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int cnt = cursor.getCount();
        cursor.close();
        return cnt;
    }

    public boolean updateContact(Integer id, String title, float amount, String description) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("title", title);
        contentValues.put("amount", amount);
        contentValues.put("description", description);

        db.update("contacts", contentValues, "id = ? ", new String[]{Integer.toString(id)});
        return true;
    }

    public void deleteContact() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("contacts", null, null);
        db.close();
    }

    public void deleteSingleContact(String title){

        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(CONTACTS_TABLE_NAME, CONTACTS_COLUMN_TITLE + "=?", new String[]{title});
//KEY_NAME is a column name
    }

    public boolean checkForTables() {
        boolean hasRows = false;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + CONTACTS_TABLE_NAME, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        if(count > 0)
            hasRows = true;
        db.close();
        return hasRows;
    }
    public ArrayList<ContactListItems> getAllContacts() {
        ArrayList<ContactListItems> contactList = new ArrayList<>();
        hp = new HashMap();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from contacts", null);
        res.moveToFirst();
        while (!res.isAfterLast()) {
                ContactListItems contactListItems = new ContactListItems();

                contactListItems.setTitle(res.getString(res
                        .getColumnIndex("title")));
                contactListItems.setAmount(res.getFloat(res
                        .getColumnIndex("amount")));
                contactListItems.setDescription(res.getString(res
                        .getColumnIndex("description")));
                contactList.add( contactListItems);
                res.moveToNext();
            }
        res.close();
        return contactList;
    }
    public int getTotalOfAmount(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c=db.rawQuery("SELECT SUM(amount) FROM " +CONTACTS_TABLE_NAME,null);
        c.moveToFirst();
        int i=c.getInt(0);
        c.close();
        return i;
    }
}

EDIT

obj.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
                deleteDialog();
                return true;
            }
        });
    }

Logcat Error:

android.database.sqlite.SQLiteException: near "=": syntax error (code 1): , while compiling: DELETE FROM contacts WHERE title=
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
            at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
            at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
            at com.softtech.stevekamau.buyathome.DBHelper.removeSingleContact(DBHelper.java:157)
            at com.softtech.stevekamau.buyathome.MyCart$6.onClick(MyCart.java:140)
            at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:166)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4745)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
            at dalvik.system.NativeStart.main(Native Method)
like image 899
Steve Kamau Avatar asked Jul 01 '15 12:07

Steve Kamau


People also ask

How do I remove something from a table in SQLite?

The TRUNCATE TABLE statement is used to remove all records from a table. SQLite does not have an explicit TRUNCATE TABLE command like other databases. Instead, it has added a TRUNCATE optimizer to the DELETE statement. To truncate a table in SQLite, you just need to execute a DELETE statement without a WHERE clause.

How do you delete a record in SQLite explain with example?

SQLite DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete the selected rows, otherwise all the records would be deleted.


1 Answers

You can use SQL DELETE statement to choose which entry you want to delete. When using a string value as the selector, make sure you enclose the value in ''.

    /**
     * Remove a contact from database by title
     *
     * @param title to remove
     */
    public void removeSingleContact(String title) {
        //Open the database
        SQLiteDatabase database = this.getWritableDatabase();

        //Execute sql query to remove from database
        //NOTE: When removing by String in SQL, value must be enclosed with ''
        database.execSQL("DELETE FROM " + TABLE_NAME + " WHERE " + CONTACTS_COLUMN_TITLE + "= '" + title + "'");

        //Close the database
        database.close();
    }

Then whenever you want to remove an entry from the database use the following:

DBHelper dbHelper = new DBHelper(context);
dbHelper.removeSingleContact("CONTACT_TO_REMOVE_HERE");

UPDATE Assuming that your ListView dataset is an ArrayList containing ContactListItems, you can set an OnItemClickListener for your ListView and use it to get your selected title, and then remove it.

listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

    @Override
    public void onItemClick(AdapterView<?> parent, View view, final int position, long id) {

        AlertDialog.Builder alertDialog = new AlertDialog.Builder(MyCart.this);
        alertDialog.setCancelable(false);
        alertDialog.setMessage("Delete item?");
        alertDialog.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                String title;

                title = arrayList.get(position).getTitle();

                dbHelper.removeSingleContact(title);

                //Update your ArrayList
                arrayList = dbHelper.getAllContacts();

                //Notify your ListView adapter
                adapter.notifyDataSetChanged();

            }
        });
        alertDialog.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int which) {
                dialog.cancel();
            }
        });
        alertDialog.show();

    }

});
like image 138
Max Tayler Avatar answered Nov 03 '22 09:11

Max Tayler