How to delete ListViews items by their ID in sqlite database

Question

I followed the tutorial here : Tutorial todo APP and I would like to customize the code nad build training app. My first change is to delete things by their ID in database ( in the example they are being deleted by names ). Here is my current code responsible for deleting items:

 // FUNCTION for DELETING EXERCISE
 public void deleteExercise(View view) {
    final View parent = (View) view.getParent();
    AlertDialog dialog = new AlertDialog.Builder(this)
            .setMessage("Are you sure, you want to delete exercise?")
            .setPositiveButton("Delete", new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface dialog, int which) {
                    TextView exerciseTextView = (TextView) parent.findViewById(R.id.exercise_name);
                    String exercise = String.valueOf(exerciseTextView.getText());
                    SQLiteDatabase db = mHelper.getWritableDatabase();
                    db.delete(ExerciseContract.ExerciseEntry.TABLE,
                            ExerciseContract.ExerciseEntry.COL_EXERCISE_NAME + " = ?",
                            new String[]{exercise});
                    db.close();
                    updateUI();
                }
            })
            .setNegativeButton("Cancel", null)
            .create();
    dialog.show();
}

// UPDATING USER INTERFACE AFTER CHANGES IN DB
private void updateUI() {
    ArrayList<String> exerciseList = new ArrayList<>();
    SQLiteDatabase db = mHelper.getReadableDatabase();
    String[] projection = {
            ExerciseContract.ExerciseEntry._ID,
            ExerciseContract.ExerciseEntry.COL_EXERCISE_NAME,
            //ExerciseContract.ExerciseEntry.COL_EXERCISE_DESCRIPTION
    };
    Cursor cursor = db.query(
            ExerciseContract.ExerciseEntry.TABLE, //tablica do zapytań
            projection, //zwracane kolumny
            null,  //columny dla WHERE
            null, //wartosci dla WHERE
            null,//nie grupuj wierszy
            null,//nie filtruj grup
            null); //porządek sortowania
    while (cursor.moveToNext()) {
        int idx = cursor.getColumnIndex(ExerciseContract.ExerciseEntry.COL_EXERCISE_NAME);
        exerciseList.add(cursor.getString(idx));
    }

    if (mAdapter == null) {
        mAdapter = new ArrayAdapter<>(this,
                R.layout.item_workout,
                R.id.exercise_name,
                exerciseList);
        mWorkoutListView.setAdapter(mAdapter);
    } else {
        mAdapter.clear();
        mAdapter.addAll(exerciseList);
        mAdapter.notifyDataSetChanged();
    }

    cursor.close();
    db.close();
}

What would be the simplest way to do that?

Answer 1

You're not returning the id anywhere, I'd personally recommend a custom adapter. But for a quick way i think the easiest thing to do is delete it when the users click an item in the list view.

Set the lists views onItemClickListener.

        mTaskListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

            Tasks task =(Tasks) mTaskListView.getItemAtPosition(position);

            deleteTask(task.getId());
        }
    });

For the delete tasks pass in the _id of the clicked item.

    public void deleteTask(long id) {

    //TextView taskTextView = (TextView) parent.findViewById(R.id.task_title);
    //String task = String.valueOf(taskTextView.getText());
    SQLiteDatabase db = mHelper.getWritableDatabase();
    db.delete(TaskContract.TaskEntry.TABLE, TaskContract.TaskEntry._ID + " = ?", new String[]{String.valueOf(id)});
    db.close();
    updateUI();
}

In the UpdateUI section change this while loop to also retrieve _id.

      while (cursor.moveToNext()) {
        int title = cursor.getColumnIndex(TaskContract.TaskEntry.COL_TASK_TITLE);
        int _id = cursor.getColumnIndex(TaskContract.TaskEntry._ID);

        Tasks tasks = new Tasks();
        tasks.setId(cursor.getInt(_id));
        tasks.setTitle(cursor.getString(title));
        taskList.add(tasks);

    }

Lastly create a model for your tasks.

package com.aziflaj.todolist.db;

public class Tasks {

    String title;
    int id;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Override
    public String toString()
    {
        return getTitle();
    }
}

Also remember to remove the button from the LayoutFile. You could show a dialog before deleting, but this was a quick solution. Ideally i'd recommended creating your own custom adapter. You'll probably have to do that if you wish to keep the button in and delete it that way.

Custom Adapter for List View

Answer 2

Create on common method inside your database helper class.

public boolean deleteRowData(String tableName, String selection, String[] selectionArgs) {
    open();
    sqLiteDb.delete(tableName, selection, selectionArgs);
    close();
    return true;
}

// ---opens the database---
public NotesData open() throws SQLException {
    DatabaseHelper dbHelper = new DatabaseHelper(context);
    sqLiteDb = dbHelper.getWritableDatabase();
    sqLiteDb = dbHelper.getReadableDatabase();

    if (!sqLiteDb.isReadOnly()) {
        // Enable foreign key constraints
        sqLiteDb.execSQL("PRAGMA foreign_keys = ON;");
    }
    return this;
}

// ---closes the database---
public void close() {
    if (sqLiteDb != null && sqLiteDb.isOpen()) {
        sqLiteDb.close();
    }
}

Now onClick of listView Item do this:

listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
    @Override
    public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
         showDeleteAlertDialog(position); // Common method for delete alert dialog
    }
});

private void showDeleteAlertDialog(int arrayPosition) {
    //delete data from
    String title = getResources().getString(R.string.warning);
    String message = getResources().getString(R.string.warning_for_delete);

    final android.app.Dialog dialog = new Dialog(YourActivity.this, R.style.DialogTheme); //this is a reference to the style above
    dialog.setContentView(R.layout.custom_dialog); //I saved the xml file above as custom_dialog.xml
    dialog.setCancelable(true);

    //to set the message
    TextView sub_message = (TextView) dialog.findViewById(R.id.tv_Message);
    TextView dialogTitle = (TextView) dialog.findViewById(R.id.tv_Title);
    Button btn_Negative = (Button) dialog.findViewById(R.id.btn_Negative);
    Button btn_Positive = (Button) dialog.findViewById(R.id.btn_Positive);

    btn_Negative.setText("Cancel");
    btn_Positive.setText("Delete");

    sub_message.setText("Are you sure you want to delete this>");
    dialogTitle.setText(title);

    //add some action to the buttons
    btn_Positive.setOnClickListener(new View.OnClickListener() {

        public void onClick(View v) {

            final String selection = YourColumnName + " LIKE ?";
            final String[] selectionArgs = {myArrayList.get(arrayPosition).getID()}; 

            mHelper.deleteRowData(YourTableNAME, selection, selectionArgs);

          // Now just remove that array position from your arraylist (from   activity & adapter arralist too).

            myArrayList.remove(arrayPosition); 
            yourAdapter.yourArrayList.remove(arrayPosition);

            notifyDataSetChanged();

        }
    });

    btn_Negative.setOnClickListener(new View.OnClickListener() {
        public void onClick(View v) {
            dialog.dismiss();
        }
    });

    dialog.show();
}