How do I delete by ID in SQLite?

Question

I am quite new Android Development and figured I should start by trying to create a simple ToDo List App using SQLite. I have all of the basic functionality in place: adding, updating, and deleting tasks. However, I am adding, updating, and deleting by the title of the task, rather than by the ID. This creates problems with duplicate tasks (e.g. tasks of the same name are deleted simultaneously). After much internet search, I still cannot find a way to do this. I would appreciate any help offered!

Here's my code:

public class TaskDbHelper extends SQLiteOpenHelper {

    public TaskDbHelper(Context context) {
        super(context, TaskContract.DB_NAME, null, TaskContract.DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String createTable = "CREATE TABLE " + TaskContract.TaskEntry.TABLE + " ( " +
                TaskContract.TaskEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                TaskContract.TaskEntry.COL_TASK_TITLE + " TEXT NOT NULL, " +
                TaskContract.TaskEntry.COL_TASK_DATE + " DATE);";
        sqLiteDatabase.execSQL(createTable);
    }
}

Activity where tasks are shown

public class ShowTaskActivity extends AppCompatActivity {

    private TaskDbHelper mHelper;
    private ListView mTaskListView;
    private ArrayAdapter<String> mAdapter;

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

        mHelper = new TaskDbHelper(this);
        mTaskListView = (ListView) findViewById(R.id.list_todo);

        updateUI();
    }

    private void updateUI() {
        ArrayList<String> taskList = new ArrayList<>();
        SQLiteDatabase sqLiteDatabase = mHelper.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.query(
                TaskContract.TaskEntry.TABLE,  // Name of the table to be queried
                new String[]{  // Which columns are returned
                        TaskContract.TaskEntry._ID,
                        TaskContract.TaskEntry.COL_TASK_TITLE,
                        TaskContract.TaskEntry.COL_TASK_DATE},
                null, null, null, null, null);
        while (cursor.moveToNext()) {
            int index =     cursor.getColumnIndex(TaskContract.TaskEntry.COL_TASK_TITLE);
            taskList.add(cursor.getString(index));
        }

        if (mAdapter == null) {
            mAdapter = new ArrayAdapter<>(this,
                    task, // What view to use for the items
                    R.id.task_title, // Where to put the string of data
                    taskList); // Where to get the data
            mTaskListView.setAdapter(mAdapter);
        } else {
            mAdapter.clear();
            mAdapter.addAll(taskList);
            mAdapter.notifyDataSetChanged();
        }

        cursor.close();
        sqLiteDatabase.close();
    }

    // TODO: Change to delete by ID, not name
    public void deleteTask(View view) {
        View parent = (View) view.getParent();
        TextView taskTextView = (TextView) parent.findViewById(R.id.task_title);
        String task = taskTextView.getText().toString();
        SQLiteDatabase sqLiteDatabase = mHelper.getWritableDatabase();
        sqLiteDatabase.delete(
            TaskContract.TaskEntry.TABLE,  // Where to delete
            TaskContract.TaskEntry.COL_TASK_TITLE + " = ?",  // Boolean check
            new String[]{task});  // What to delete
        sqLiteDatabase.close();
        updateUI();
    }
}

Task adding Code

public void addTask(String task, String date) {
    SQLiteDatabase sqLiteDatabase = mHelper.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(TaskContract.TaskEntry.COL_TASK_TITLE, task);
    contentValues.put(TaskContract.TaskEntry.COL_TASK_DATE, date);
    sqLiteDatabase.insertWithOnConflict(
            TaskContract.TaskEntry.TABLE,
            null,
            contentValues,
            SQLiteDatabase.CONFLICT_REPLACE);
    sqLiteDatabase.close();
}

Create `Model` of Task with fields `id` and `name` and then create custom `Adapter` then get the `id` when deleting item and delete it from the database — ak sacha, Mar 30 '17 at 06:58
@aksacha How do I get the ID when deleting the item using a custom adapter? — sumowrestler, Mar 30 '17 at 17:24

score 3 · Accepted Answer · answered Mar 30 '17 at 06:50

String rowId; //Set your row id here
SQLiteDatabase sqLiteDatabase = mHelper.getWritableDatabase();
sqLiteDatabase.delete(
                TaskContract.TaskEntry.TABLE,  // Where to delete
                KEY_ID+" = ?",  
                new String[]{rowId});  // What to delete
sqLiteDatabase.close();

score 2 · Answer 2 · answered Mar 30 '17 at 06:50

2

SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_NAME, KEY_ID + " = ?",new String[]{Long.toString(id)} );
db.close();

answered Mar 30 '17 at 06:50

Aravindraj

590
5
23

score 0 · Answer 3 · answered Mar 30 '17 at 06:50

0

You can try this method to delete By id

  public void deleteData(String tableName, Integer id) {

    try {
        if (mWritableDB != null) {
            mWritableDB.execSQL("delete from " + tableName + " Where id = " + id);
        }
    } catch (Exception _exception) {
        _exception.printStackTrace();
    }
}

answered Mar 30 '17 at 06:50

Nikhil Sharma

593
7
23

The problem is when I create a task I don't store the ID – sumowrestler Mar 30 '17 at 06:52
1

Its better your create model(POJO) your task and in that you maintain your ID when you fetch list and better to use recycler view for this purpose – Nikhil Sharma Mar 30 '17 at 06:56
I'm not sure what model(POJO) means. Can you explain that? And what is better about a recycler view? – sumowrestler Mar 30 '17 at 06:58
1

POJO is java class with getter and setter with all params are required by you and recycler view is better because to remove a list from view is easy and can get position of item – Nikhil Sharma Mar 30 '17 at 07:03
@CL can you explain me please i wanna know – Nikhil Sharma Mar 30 '17 at 08:14
Do you know why we use catch block – Nikhil Sharma Mar 30 '17 at 08:28
if there is any error even if in my sql query also it give m error. if you dont have idea about it i can explain by code – Nikhil Sharma Mar 30 '17 at 08:29
@NikhilSharma Thank you for your help! After your advice, and some online research, I will implement a recycler view. I never knew how much more powerful it was over ListView. I will also make a POJO class for Tasks. Thanks for your help! – sumowrestler Mar 30 '17 at 17:23
Your most welcome my frnd if my suggestion really helps you so hit up button .Thanks! – Nikhil Sharma Mar 30 '17 at 17:59

How do I delete by ID in SQLite?

3 Answers3