CODEWITHSUNDEEP
androidsqlitegridviewandroid-sqlite
Devsolutionsbeyond
Devsolutionsbeyond

Reputation: 29

How to delete SQLite data from a GridView item click?

I cannot for the life of me figure out how to delete only data in SQLite that is populated into a single gridView cell by clicking on that cell. Any help would be greatly appreciated. I have marked where I was going to insert code to delete from SQLite. Here is my List code:

public class FishList extends AppCompatActivity {

GridView gridView;
ArrayList<Fish> list;
FishListAdapter adapter;


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

    gridView =  findViewById(R.id.gridView);
    list = new ArrayList<>();
    adapter = new FishListAdapter(this, R.layout.fish_items, list);
    gridView.setAdapter(adapter);

    Cursor cursor = sqLiteHelper.getData("SELECT * FROM FISH");
    list.clear();
    while (cursor.moveToNext()) {

        String species = cursor.getString(cursor.getColumnIndex("species"));
        String date = cursor.getString(cursor.getColumnIndex("date"));
        String weight = cursor.getString(cursor.getColumnIndex("weight"));
        String length = cursor.getString(cursor.getColumnIndex("length"));
        String sex = cursor.getString(cursor.getColumnIndex("sex"));
        String bait = cursor.getString(cursor.getColumnIndex("bait"));
        String method = cursor.getString(cursor.getColumnIndex("method"));
        byte[] image = cursor.getBlob(cursor.getColumnIndex("image"));

        list.add(new Fish(species, date, weight, length, sex, bait, method, image));
    }
    adapter.notifyDataSetChanged();
}
public void returndash(View view){
    Intent myintent = new Intent(FishList.this, Dash.class);
    startActivity(myintent);
}
public void returnNew(View view){
    Intent myintent = new Intent(FishList.this, NewCatch.class);
    startActivity(myintent);
}
public void deleteEntry(View view){

    AlertDialog.Builder builder1 = new AlertDialog.Builder(FishList.this);
    builder1.setMessage("Are you sure you want to delete this Catch?");
    builder1.setCancelable(true);
    builder1.setPositiveButton(
            "Yes",
            new DialogInterface.OnClickListener() {
                public void onClick(DialogInterface dialog, int id) {
                    ////////////
                    //Delete database strings and byte[] image here
                    ////////////
                }
            });
    builder1.setNegativeButton(
            "No",
            new DialogInterface.OnClickListener() {
                public void onClick(DialogInterface dialog, int id) {
                    dialog.cancel();
                }
            });
    AlertDialog alert11 = builder1.create();
    alert11.show();
}
}

And here is my SQLiteHelper:

public class SQLiteHelper extends SQLiteOpenHelper {

public SQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
    super(context, name, factory, version);


}
public void queryData(String sql) {
    SQLiteDatabase database = getWritableDatabase();
    database.execSQL(sql);
}
public void insertData(String species, String date, String weight, String length, String sex,String bait,String method, byte[] image) {
    SQLiteDatabase database = getWritableDatabase();
    String sql = "INSERT INTO FISH VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

    SQLiteStatement statement = database.compileStatement(sql);
    statement.clearBindings();

    statement.bindString(1, species);
    statement.bindString(2, date);
    statement.bindString(3, weight);
    statement.bindString(4, length);
    statement.bindString(5, sex);
    statement.bindString(6, bait);
    statement.bindString(7, method);
    statement.bindBlob(8, image);

    statement.executeInsert();
}
public void deleteDatabase(){
    SQLiteDatabase database = sqLiteHelper.getWritableDatabase();
    database.delete("FISH", "1", new String[0] );
    database.close();
}
public Cursor getData(String sql) {
    SQLiteDatabase database = getReadableDatabase();
    return database.rawQuery(sql, null);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}

Maybe someone else can make some sense of this.
I tried so many different ways.
All I could get it to do is erase the entire database.

Upvotes: 0

Views: 300

Answers (2)

MikeT
MikeT

Reputation: 57103

You could use :-

public void deleteCatch(String species, String date, String weight, String length, String sex, String bait){
    SQLiteDatabase database = sqLiteHelper.getWritableDatabase();
    database.delete("FISH",
        "species=? AND " +
        "date=? AND " +
        "weight=? AND " +
        "length=? AND " +
        "sex=? AND " +
        "bait=?",
        new String[]{species,date,weight,length,sex,bait});
    );
    database.close();
}

This does assume that the columns would be sufficient to uniquely identify the row (fewer/more could be used as required). This would be invoked by passing the respective values extracted from the respective Fish in the list.

Alternative more efficient approach

However SQLite automatically provides a unqiue identifier (rowid) for each row (unless the table is defined with WITHOUT ROWID). This rowid is normally hidden. Frequently a column will be defined as ?? INTEGER PRIMARY KEY where ?? is the column name. This is treated specially as the column name becomes an alias for the rowid and is then not hidden. Using such an alias is very common and also very efficient to use (the above method isn't nearly as efficient).

Note ?? INTEGER PRIMARY KEY AUTOINCREMENT also creates the alias. However, it's use, which is seldom needed, incurs overheads. Generally AUTOINCREMENT is best not used.

So if you have included such a column, lets say you named it _id for illustrative purposes.

If you amended your Fish class to have a member called id then adding the following to when you create the list array :-

    String id = Long.toString(cursor.getlong(cursor.getColumnIndex("_id")));

and amended adding the fish to the list as per :-

    ......
    list.add(new Fish(id, species, date, weight, length, sex, bait, method, image)); // Note id added

You would then be able to access the _id value from a Fish instance.

No rowid alias!

If you don't have such a column defined an alternative could be to generate a suitable column from the "hidden" rowid when querying the database to get the cursor. e.g. change :-

Cursor cursor = sqLiteHelper.getData("SELECT * FROM FISH");

to

Cursor cursor = sqLiteHelper.getData("SELECT rowid AS _id, * FROM FISH");

Then the changes above would work with the previous changes.

The simpler deleteCatch Method

You could then have a simpler deleteCatch method :-

public void deleteCatch(long id){
    SQLiteDatabase database = sqLiteHelper.getWritableDatabase();
    database.delete("FISH",
        "_id=?",
        new String[]{Long.toString(id)});
    );
    database.close();
}

You'd just need to extract the one value from the respective Fish in the list.

Note! You may encounter issues storing images in the database (e.g. A CursorWindow is limited to 2MB). Many would advise storing the images outside of the database and storing the path to the file in the database.

Upvotes: 1

ReverseEffect
ReverseEffect

Reputation: 133

In your Fish POJO, keep track of the row ID. When I (used to) create an Android database this way, I would call the ID of the row INTEGER PRIMARY AUTOINCREMENT.

With that in mind, I'd store that as an int variable in my model class (in your case, Fish) and then when I'd want to perform an operation on it (e.g. edit/delete), I'd reference that row with the row ID in a SQL statement using where id=?.

Upvotes: 1

Related Questions