I want to delete specific data from SQLite database but it is not working

Question

I have a problem in SQLite database in Android. I want to delete specific data from SQLite database through cardview but it is not working.

When I uninstall the app and again install it in mobile first time the data has been removed but after that no data is removed from SQLite database the data has been deleted from cardview but not deleted in SQLite database.

Here is my code:

My Adapterclass

package com.deitel.sqlitedatabase_recyclerview.adapterclass;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ImageButton;
import android.widget.TextView;
import androidx.annotation.NonNull;
import androidx.recyclerview.widget.RecyclerView;

import com.deitel.sqlitedatabase_recyclerview.MainActivity;
import com.deitel.sqlitedatabase_recyclerview.R;
import com.deitel.sqlitedatabase_recyclerview.database.DatabaseHelper;
import com.deitel.sqlitedatabase_recyclerview.modelclass.NotepadModelClass;
import java.util.List;
public class NotepadAdapter extends RecyclerView.Adapter<NotepadAdapter.Viewholder> {
DatabaseHelper databaseHelper;
    public static List<NotepadModelClass> itemlist;
    public NotepadAdapter(@NonNull Context context, List<NotepadModelClass> itemlist) {
        this.itemlist = itemlist;
        databaseHelper=new DatabaseHelper((MainActivity) context);
    }
    @NonNull
    @Override
    public NotepadAdapter.Viewholder onCreateViewHolder(@NonNull ViewGroup viewGroup, int viewType) {
        View view = LayoutInflater.from(viewGroup.getContext()).inflate(R.layout.custom_cardview_layout, viewGroup, false);
        Viewholder holder = new Viewholder(view);
        return holder;
    }
    @Override
    public void onBindViewHolder(@NonNull NotepadAdapter.Viewholder holder, final int position) {
        holder.textView_name.setText("Name :" + itemlist.get(position).getName());
        holder.textView_details.setText("Details : " + itemlist.get(position).getDetails());
        holder.textView_languages.setText("Language : " + itemlist.get(position).getLanguages());
        holder.btn_delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                databaseHelper.delete(position);
                itemlist.remove(position);
                notifyItemRemoved(position);
                notifyItemRangeChanged(position,itemlist.size());
                notifyDataSetChanged();
            }
        });
    }
    @Override
    public int getItemCount() {
        return itemlist.size();
    }
    public class Viewholder extends RecyclerView.ViewHolder {
        TextView textView_name;
        TextView textView_details;
        TextView textView_languages;
        ImageButton btn_delete;
        public Viewholder(View view) {
            super(view);
            textView_name = view.findViewById(R.id.textview_name);
            textView_details = view.findViewById(R.id.textview_details);
            textView_languages = view.findViewById(R.id.textview_spinner);
            btn_delete=view.findViewById(R.id.btn_delete);
        }
    }
}

My DatabaseHelper Class

package com.deitel.sqlitedatabase_recyclerview.database;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.SyncStateContract;

import androidx.annotation.NonNull;
import androidx.annotation.Nullable;
import com.deitel.sqlitedatabase_recyclerview.MainActivity;
public class DatabaseHelper extends SQLiteOpenHelper {
    public static final  String DATABASE_NAME="Notepad.db";
    public static final String TABLE_NAME="notepad_table";
    public static final String COL_1="ID";
    public static final String COL_2="NAME";
    public static final String COL_3="DETAILS";
    public static final String COL_4="LANGUAGES";
    @NonNull
    @Override
    public String toString() {
        return super.toString();
    }
    public DatabaseHelper(@Nullable MainActivity context) {
        super(context, DATABASE_NAME, null, 2);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + TABLE_NAME + "("
                + COL_1 + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COL_2 + " TEXT,"
                + COL_3 + " TEXT,"
                + COL_4 + " TEXT)");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS "+ TABLE_NAME);
        onCreate(db);
    }
    public boolean insertdata(String name,String details,String languages)
    {
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues contentValues=new ContentValues();
        contentValues.put(COL_2,name);
        contentValues.put(COL_3,details);
        contentValues.put(COL_4,languages);
        long result=db.insert(TABLE_NAME,null,contentValues);
        if (result== -1)
        {
            return false;
        }
        else {
            return true;
        }
    }
    public void delete(int id){
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete(TABLE_NAME,COL_1 + " = ?", new String[]{String.valueOf(id)});
        db.close();

    }

}

Answer 1

You are using position in the list, which is not always the id of the row that is clicked.

• If you click the first, the position is 0, so no row is deleted as lowest id is 1 (typically).
• If you click the second the position is 1 so id 1 is deleted, if the row exists.

You need to have an id in NotepadModelClass storing the id when making the itemlist. You also ned to have a getId method in NotepadModelClass. With this you can then use databaseHelper.delete(itemlist.get(position).getId());. This will then delete the respective row.

---

Example

NotepadModelClass.java

public class NotepadModelClass {

    int id; //<<<<<<<<< ADDED (should really be long but doesn't matter)
    String name;
    String details;
    String languages;

    NotepadModelClass(){}
    NotepadModelClass(String name, String details, String languages) {
    }
    NotepadModelClass(int id, String name, String details, String languages) {
        this.id = id;
        this.name = name;
        this.details = details;
        this.languages = languages;
    }

    /* <<<<<<<<<< ADDED >>>>>>>>>> */
    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDetails() {
        return details;
    }

    public void setDetails(String details) {
        this.details = details;
    }

    public String getLanguages() {
        return languages;
    }

    public void setLanguages(String languages) {
        this.languages = languages;
    }
}

• Note, this may well differ, the important bits are commented

DataabseHelper.java

The only changes were that the following method was added :-

public List<NotepadModelClass> getAllNotepadModels() {
    ArrayList<NotepadModelClass> list = new ArrayList<>();
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor csr = db.query(TABLE_NAME,null,null,null,null,null,null);
    while (csr.moveToNext()) {
        list.add(new NotepadModelClass(
                csr.getInt(csr.getColumnIndex(COL_1)), //<<<<<<<<<< sets the ID
                csr.getString(csr.getColumnIndex(COL_2)),
                csr.getString(csr.getColumnIndex(COL_3)),
                csr.getString(csr.getColumnIndex(COL_4))
                )
        );
    }
    return list;
}

• The important aspect is thet the id is set in the NotepadModelClass

NotepadAdapter.java

The only change was as per :-

            //databaseHelper.delete(position);
            Log.d("BUTTONCLICKINFO","In the display item " + position + " was clicked. The respective rowid is " + itemlist.get(position).getId());
            databaseHelper.delete(itemlist.get(position).getId());

i.e. the id is being retrieved from the item at the position, the logging has been added to demonstrate the difference.

Results

When the App is first run 10 rows are added and the display is :-

The DELETE ME! button for Rows Test2, Test5 and Test7 are clicked in that order.

The Log displays :-

2019-12-22 09:20:38.508 D/BUTTONCLICKINFO: In the display item 1 was clicked. The respective rowid is 2
2019-12-22 09:20:40.685 D/BUTTONCLICKINFO: In the display item 3 was clicked. The respective rowid is 5
2019-12-22 09:20:42.361 D/BUTTONCLICKINFO: In the display item 4 was clicked. The respective rowid is 7

• i.e. clicking TEST2 the 2nd row (position 1) results in id 2 being deleted.
• Items/positions are then re-ordered
  • Test1 is position 0,
  • Test3 is position 1
  • So Test5 who's ID is 5, which was originally position 4, is now at position 3

The Display is :-

App Restarted :-