How to get position of item and delete it using Cursor?

Question

I'm making webview app that contains a bookmarks i successfully added items into listView using Cursor and SQLite database but my problem is i can't get position of item and delete it i tried to do it in my own way but what i got is i had deleted all content of list view. this is my MainActivity, i made a longClickItemListener to delete listview item on long click and this is my BookmarkDatabase

Main Activity

final Dialog bookmarksScreen = new Dialog(this);

        bookmarksScreen.requestWindowFeature(Window.FEATURE_NO_TITLE);

        bookmarksScreen.setContentView(R.layout.activity_bookmark);

        bookmarksScreen.setCancelable(true);

        final ListView listView = bookmarksScreen.findViewById(R.id.bookmark_list);
        ImageView bookmarkBack   = bookmarksScreen.findViewById(R.id.close_bookmarks);
        ImageView bookmarkDelete = bookmarksScreen.findViewById(R.id.delete_table);

        // populate an ArrayList<String> from the database and then view it
        final ArrayList<String> theList = new ArrayList<>();
        Cursor data = bookmarkDB.getListContents();
        // check if there is no data on database
        if(data.getCount() == 0){
            Log.d("Database Bookmark", "There is no items on item list");
        } else {
            while(data.moveToNext()) {
                // add data into table "COL1" and "COL2"
                theList.add(data.getString(1));
                ListAdapter listAdapter = new ArrayAdapter<>(this, R.layout.activity_listitem, theList);
                listView.setAdapter(listAdapter);
            }
        }

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

                String item = parent.getItemAtPosition(position).toString();

                webView.loadUrl(item);

                bookmarksScreen.dismiss();

            }
        });

        listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
                final int which_item = position;

                // here i want to delete listview item

                return true;
            }
        });

        bookmarkBack.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                bookmarksScreen.dismiss();
            }
        });

        bookmarkDelete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                showConfirmDialog();

                bookmarksScreen.dismiss();

            }
        });

        bookmarksScreen.getWindow().setBackgroundDrawable(new ColorDrawable(Color.TRANSPARENT));

        bookmarksScreen.getWindow().setLayout(WindowManager.LayoutParams.MATCH_PARENT, WindowManager.LayoutParams.MATCH_PARENT);

        bookmarksScreen.show();

Bookmarks Database

package com.rixhion.mint.databases;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class BookmarksDatabase extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "bookmarks.db";
    public static final String TABLE_NAME = "bookmarks_data";
    public static final String COL1 = "ID";
    public static final String COL2 = "ITEM1";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " + " ITEM1 TEXT)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public boolean addData(String item1) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL2, item1);

        long result = db.insert(TABLE_NAME, null, contentValues);

        //if date as inserted incorrectly it will return -1
        if (result == -1) {
            return false;
        } else {
            return true;
        }
    }
    public Cursor getListContents(){
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor data = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        return data;
    }

    public Cursor deleteSpecificContents() {
        SQLiteDatabase db = this.getWritableDatabase();
        // Here i want to get the position of item and delete it
    }


}

Answer 1

Ideally, you would be using the primary key somewhere retrievable in your views (Or, vice versa, use as a primary key, a value that is already used somewhere retrievable). Since your Primary Key here is an auto_increment field, there is no real reliable correlation between it and the view you are trying to delete (At least from what I can see on your code). One approach would be to use a custom Adapter for your list, rather than an ArrayAdapter, and using the View.setTag() and View.getTag() methods to store the primary key on each of your List's items. You can see how to do this here, as well as several online custom Adapter tutorials.

Assuming your activity_listitem layout has a TextView with id text_view, where you display the text fetched from your database, and to which you have added the Tag storing the primary key, you can do the following in your Main Activity:

    listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
        @Override
        public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
            TextView textView = (TextView) view.findViewById(R.id.text_view);
            final Integer which_item = (Integer) textView.getTag();    //Assuming you stored the ID as an Integer
            int rowsDeleted = bookmarksDB.deleteSpecificContent(which_item);
            if(rowsDeleted == 0){  //should be 1, since we're deleting by Primary Key
                //if you would like like, handle if nothing was deleted
            }
            return true;
        }
    });

And in your BookmarksDatabase class:

    public int deleteSpecificContents(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, COL1 + "=?", new String[]{Integer.toString(id)});
    }

---

Another approach, if you do not want to use a custom Adapter, would be determining the item you want to delete by using its position on the list. You can do this by querying the Database, and iterating on the Cursor until it matches the position of your item on the List.

    listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
        @Override
        public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
            Cursor data = bookmarkDB.getListContents();
            int i = 0;
            while(data.moveToNext()){
                if(i == position){
                    break;
                }
                i++;
            }
            int rowsDeleted = bookmarkDB.deleteSpecificContent(data.getInt(0));
            //again, handle if rowsDeleted != 1 if you want
            return true;
        }
    });

Your BookmarkDatabase.deleteSpecificContent() method is exactly the same as in the first approach for this one - the only difference is in how you determine the id of the item to be deleted. If you would not like to handle the case where deleteSpecificContent() does not return 1 (unless I'm missing something major here, it should always return 1 in your use case), feel free to change its signature to void, rather than int.

Note that this second approach is more expensive than the first one, since it requires an additional Database query, and could iterate on the Cursor a lot. While it requires a lot less new/changed code, it may not be advisable to use (I honestly cannot say for certain. It is probably fine for your use case, depending on the size of your Database, but - full disclosure - I am not experienced enough with Android to make this call. Use at your own discretion).