Remove ListItem from Listview attached to SQLite

Question

[GIF OF ACTIVITY][1] <--------------- WATCH ACTIVITY

I have been having issues trying to delete the item on the row from SQLite in a ListView So far I have been able to successfully delete always first item on list. whenever I click my Delete Button on my ListView.

If you have any input or feedback don't hesitate to indulge me please!

Here is the relevant code

DBHelper

public class DBHelper extends SQLiteOpenHelper {

private static final String DB_NAME = "ListappDB";
private static final int DB_VERSION = 1;
public static final String DB_TABLE = "List";
public static final String DB_COLUMN = "Item";

public DBHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String query = String.format("CREATE TABLE %s(ID INTEGER PRIMARY KEY AUTOINCREMENT, %s TEXT NOT NULL)", DB_TABLE, DB_COLUMN);
    db.execSQL(query);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String query = String.format("DELETE TABLE IF EXISTS %s", DB_TABLE);
    db.execSQL(query);
    onCreate(db);
}

public void insertNewItem(String item){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(DB_COLUMN, item);
    db.insertWithOnConflict(DB_TABLE, null, values, SQLiteDatabase.CONFLICT_REPLACE);
    db.close();
}

public void deleteItem(String item){
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(DB_TABLE, DB_COLUMN + "=?", new String[]{ item });
    db.close();
}

public ArrayList<String> getItemList(){
    ArrayList<String> itemList = new ArrayList<String>();
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(DB_TABLE, new String[]{DB_COLUMN},null,null,null,null,null);
    while (cursor.moveToNext()){
        int index = cursor.getColumnIndex(DB_COLUMN);
        itemList.add(cursor.getString(index));
    }
    cursor.close();
    db.close();
    return itemList;
}

}

Main Activity

public class MainActivity extends AppCompatActivity {

private FirebaseAuth mAuth;
private FirebaseAuth.AuthStateListener mAuthListener;
private DBHelper mDBHelper;
private ArrayAdapter<String> mAdapter;
private ListView mListView;
private Button mButton;
private Button mDeleteButton;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    this.requestWindowFeature(Window.FEATURE_ACTION_BAR);
    setContentView(R.layout.activity_main);

    mDBHelper = new DBHelper(this);
    mAuth = FirebaseAuth.getInstance();

    mListView = (ListView) findViewById(R.id.list);
    mButton = (Button) findViewById(R.id.buttonQR);


    mButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            ArrayList<String> itemList = mDBHelper.getItemList();
            MultiFormatWriter multiFW = new MultiFormatWriter();
            try {
                BitMatrix bitMatrix = multiFW.encode(String.valueOf(itemList), BarcodeFormat.QR_CODE, 200, 200);
                BarcodeEncoder enconder = new BarcodeEncoder();
                Bitmap bitmap =  enconder.createBitmap(bitMatrix);
                Intent intent = new Intent(getApplicationContext(), QR.class);
                intent.putExtra("qrcode", bitmap);
                startActivity(intent);

            } catch (WriterException e) {
                e.printStackTrace();
            }
        }
    });

    mAuthListener = new FirebaseAuth.AuthStateListener() {
        @Override
        public void onAuthStateChanged(@NonNull FirebaseAuth firebaseAuth) {

            if (firebaseAuth.getCurrentUser() == null){
                goAuthenticate();
            }
        }
    };


    loadItemList();
}

private void loadItemList() {
    ArrayList<String> itemList = mDBHelper.getItemList();
    if (mAdapter==null){
        mAdapter = new ArrayAdapter<String>(this, R.layout.row, R.id.item_name, itemList);
        mListView.setAdapter(mAdapter);
    } else {
        mAdapter.clear();
        mAdapter.addAll(itemList);
        mAdapter.notifyDataSetChanged();
    }
}

public void goAuthenticate(){
    Intent mLogin = new Intent(MainActivity.this, Authentication.class);
    mLogin.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP|Intent.FLAG_ACTIVITY_NEW_TASK);
    startActivity(mLogin);
    finish();
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
    MenuInflater inflater=getMenuInflater();
    inflater.inflate(R.menu.menu, menu);
    return super.onCreateOptionsMenu(menu);
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {

    switch (item.getItemId()){
        case R.id.logout:
            mAuth.signOut();
            LoginManager.getInstance().logOut();
            break;
        case R.id.addNewItem:
            final EditText itemEditText = new EditText(this);
            AlertDialog alertDialog = new AlertDialog.Builder(this)
                    .setTitle("Add New Item")
                    .setView(itemEditText)
                    .setPositiveButton("Add", new DialogInterface.OnClickListener(){

                        @Override
                        public void onClick(DialogInterface dialog, int which) {
                            String item = String.valueOf(itemEditText.getText());
                            if(item.length() <= 0|| item.equals("")){
                                Toast.makeText(MainActivity.this, "Item Cant Be Blank",
                                        Toast.LENGTH_LONG).show();
                            } else {
                                mDBHelper.insertNewItem(item);
                                loadItemList();
                            }
                        }
                    })
                    .setNegativeButton("Cancel", null)
                    .create();
            alertDialog.show();
            break;
    }


    return super.onOptionsItemSelected(item);
}

public void deleteItemH(View view){

    TextView itemTextView = (TextView) view.findViewById(R.id.item_name);
    String item = String.valueOf(itemTextView.getText());
    mDBHelper.deleteItem(item);
    loadItemList();

}

@Override
public void onStart() {
    super.onStart();
    mAuth.addAuthStateListener(mAuthListener);
}

@Override
public void onStop() {
    super.onStop();
    if (mAuthListener != null){
        mAuth.removeAuthStateListener(mAuthListener);
    }
}

}

Row.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
            android:layout_width="match_parent"
            android:layout_height="match_parent">

<TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_centerVertical="true"
    android:id="@+id/item_name"
    android:text="Example"
    android:textSize="20dp"
    android:layout_alignParentStart="true"
    />

<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/btnDelete"
    android:layout_alignParentEnd="true"
    android:layout_alignParentRight="true"
    android:text="DELETE"
    android:onClick="deleteItemH"
    android:layout_centerVertical="true"/>

Activity Main.xml

<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:id="@+id/activity_main"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
android:orientation="vertical"
tools:context="tech.destinum.listapp.MainActivity"
android:layout_centerVertical="true">


<ListView
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:id="@+id/list"
    android:clipChildren="false"
    android:layout_weight="1"/>


<RelativeLayout
    android:id="@+id/footer"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:layout_alignParentBottom="true"
    android:gravity="center">

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:id="@+id/buttonQR"
            android:text="Generate"/>
</RelativeLayout>

deleteItemH(View view) view getting received is the row that you want to delete? — Dharmendra Pratap Singh, Jan 03 '17 at 21:31
That's what I am trying to solve, so far exclusively deleting the first item on the list over and over when **delete** button is clicked — Ispam, Jan 03 '17 at 23:51
Please post the whole activity code so that I can easily look from where you are calling your deleteItemH method? — Dharmendra Pratap Singh, Jan 04 '17 at 04:08
change public static final String DB_COLUMN = "Item"; to DB_COLUMN = "_Id". I did that on mine and everything works right out of the box. — danny117, Jan 10 '17 at 20:05

Dharmendra Pratap Singh · Accepted Answer · 2017-01-05T05:43:30.953

Your approach to solve the problem that you have mentioned in the question is not correct. Mentioning the problem with the solution below

Problem 1

You should use Custom Adapter instead of using basic ArrayAdapter.

ItemAdapter.class

 public class ItemAdapter extends BaseAdapter {

    private final LayoutInflater layoutInflater;
    private ArrayList<ItemClass> itemList;
    private final DBHelper dbHelper;

    public ItemAdapter(Context context, DBHelper dbHelper, ArrayList<ItemClass> itemList) {
        this.itemList = itemList;
        this.layoutInflater = LayoutInflater.from(context);
        this.dbHelper = dbHelper;
    }

    @Override
    public int getCount() {
        return itemList != null ? itemList.size() : 0;
    }

    @Override
    public Object getItem(int i) {
        return itemList.get(i);
    }

    @Override
    public long getItemId(int i) {
        return itemList.get(i)._id;
    }

    //use this function to assign list of items
    public void setItemList(ArrayList<ItemClass> itemList) {
        this.itemList.clear();
        this.itemList = itemList;
        notifyDataSetChanged();
    }

    @Override
    public View getView(int position, View convertView, ViewGroup viewGroup) {

        ViewHolder viewHolder;
        if (convertView == null) {
            convertView = layoutInflater.inflate(R.layout.row, null);
            viewHolder = new ViewHolder(convertView);
            convertView.setTag(viewHolder);
        } else {
            viewHolder = (ViewHolder) convertView.getTag();
        }

        final ItemClass itemClass = itemList.get(position);

        viewHolder.btn_delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                deleteItemH(itemClass);
            }
        });
        viewHolder.tv_item.setText(itemClass.detail);
        return convertView;
    }

    private void deleteItemH(ItemClass item) {
        dbHelper.deleteItemById(item._id);
        setItemList(dbHelper.getItemList());
    }

    private static class ViewHolder {
        final Button btn_delete;
        final TextView tv_item;

        ViewHolder(View view) {
            btn_delete = (Button) view.findViewById(R.id.btnDelete);
            tv_item = (TextView) view.findViewById(R.id.item_name);
        }
    }
}

Problem 2:-

You were trying to delete the row data using the item column which will delete all the row with same name, as you haven't put the unique constraint in the item column.

So depending on you use case you should either put a unique constraint on item column or delete the item by an _ID column.

Modified your DBHelper code to delete row it by _ID column.

public class DBHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "ListappDB";
    private static final int DB_VERSION = 1;
    public static final String DB_TABLE = "List";
    public static final String DB_COLUMN = "Item";
    //added this variable to use it while deleting the record
    public static final String DB_COLUMN_ID = "_id";

    public DBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query = String.format("CREATE TABLE %s( %s INTEGER PRIMARY KEY AUTOINCREMENT, %s TEXT NOT NULL)", DB_TABLE, DB_COLUMN_ID, DB_COLUMN);
        db.execSQL(query);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String query = String.format("DELETE TABLE IF EXISTS %s", DB_TABLE);
        db.execSQL(query);
        onCreate(db);
    }

    public void insertNewItem(String item) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DB_COLUMN, item);
        db.insertWithOnConflict(DB_TABLE, null, values, SQLiteDatabase.CONFLICT_REPLACE);
        db.close();
    }


    public void deleteItem(String item) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(DB_TABLE, DB_COLUMN + "=?", new String[]{item});
        db.close();
    }

    //Function to delete item using ID
    public void deleteItemById(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(DB_TABLE, DB_COLUMN_ID + "=?", new String[]{id + ""});
        db.close();
    }

    public ArrayList<ItemClass> getItemList() {
        ArrayList<ItemClass> itemList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(DB_TABLE, new String[]{DB_COLUMN_ID, DB_COLUMN}, null, null, null, null, null);
        while (cursor.moveToNext()) {
            final String name = cursor.getString(cursor.getColumnIndex(DB_COLUMN));
            final long id = cursor.getLong(cursor.getColumnIndex(DB_COLUMN_ID));
            itemList.add(new ItemClass(id, name));
        }
        cursor.close();
        db.close();
        return itemList;
    }
}

Modified you MainActivity.class to support the ItemAdapter.class used above

public class MainActivity extends AppCompatActivity {

private FirebaseAuth mAuth;
private FirebaseAuth.AuthStateListener mAuthListener;
private DBHelper mDBHelper;
private ItemAdapter mAdapter;
private ListView mListView;
private Button mButton;
private Button mDeleteButton;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    this.requestWindowFeature(Window.FEATURE_ACTION_BAR);
    setContentView(R.layout.activity_main);

    mDBHelper = new DBHelper(this);
    mAuth = FirebaseAuth.getInstance();

    mListView = (ListView) findViewById(R.id.list);
    mButton = (Button) findViewById(R.id.buttonQR);


    mButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            ArrayList<String> itemList = mDBHelper.getItemList();
            MultiFormatWriter multiFW = new MultiFormatWriter();
            try {
                BitMatrix bitMatrix = multiFW.encode(String.valueOf(itemList), BarcodeFormat.QR_CODE, 200, 200);
                BarcodeEncoder enconder = new BarcodeEncoder();
                Bitmap bitmap =  enconder.createBitmap(bitMatrix);
                Intent intent = new Intent(getApplicationContext(), QR.class);
                intent.putExtra("qrcode", bitmap);
                startActivity(intent);

            } catch (WriterException e) {
                e.printStackTrace();
            }
        }
    });

    mAuthListener = new FirebaseAuth.AuthStateListener() {
        @Override
        public void onAuthStateChanged(@NonNull FirebaseAuth firebaseAuth) {

            if (firebaseAuth.getCurrentUser() == null){
                goAuthenticate();
            }
        }
    };


    loadItemList();
}

//only needed once rest of the time it is getting managed inside the ItemAdapter
private void loadItemList() {
    ArrayList<ItemClass> itemList = mDBHelper.getItemList();
    mAdapter = new ItemAdapter(MainActivity.this, mDBHelper, itemList);
    mListView.setAdapter(mAdapter);
}

public void goAuthenticate(){
    Intent mLogin = new Intent(MainActivity.this, Authentication.class);
    mLogin.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP|Intent.FLAG_ACTIVITY_NEW_TASK);
    startActivity(mLogin);
    finish();
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
    MenuInflater inflater=getMenuInflater();
    inflater.inflate(R.menu.menu, menu);
    return super.onCreateOptionsMenu(menu);
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {

    switch (item.getItemId()){
        case R.id.logout:
            mAuth.signOut();
            LoginManager.getInstance().logOut();
            break;
        case R.id.addNewItem:
            final EditText itemEditText = new EditText(this);
            AlertDialog alertDialog = new AlertDialog.Builder(this)
                    .setTitle("Add New Item")
                    .setView(itemEditText)
                    .setPositiveButton("Add", new DialogInterface.OnClickListener(){

                        @Override
                        public void onClick(DialogInterface dialog, int which) {
                            String item = String.valueOf(itemEditText.getText());
                            if(item.length() <= 0|| item.equals("")){
                                Toast.makeText(MainActivity.this, "Item Cant Be Blank",
                                        Toast.LENGTH_LONG).show();
                            } else {
                                mDBHelper.insertNewItem(item);
                                loadItemList();
                            }
                        }
                    })
                    .setNegativeButton("Cancel", null)
                    .create();
            alertDialog.show();
            break;
    }


    return super.onOptionsItemSelected(item);
}


@Override
public void onStart() {
    super.onStart();
    mAuth.addAuthStateListener(mAuthListener);
}

@Override
public void onStop() {
    super.onStop();
    if (mAuthListener != null){
        mAuth.removeAuthStateListener(mAuthListener);
    }
}
}

Added extra ItemClass.java for supporting the adapter to pass both id and item.

public class ItemClass {
    public final long _id;
    public final String detail;

    public ItemClass(long id, String detail) {
        _id = id;
        this.detail = detail;
    }
}

Removed the android:onClick="deleteItemH" from row.xml as you cannot use on click in item of an adapter.

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
            android:layout_width="match_parent"
            android:layout_height="match_parent">

<TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_centerVertical="true"
    android:id="@+id/item_name"
    android:text="Example"
    android:textSize="20dp"
    android:layout_alignParentStart="true"
    />

<Button
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/btnDelete"
    android:layout_alignParentEnd="true"
    android:layout_alignParentRight="true"
    android:text="DELETE"
    android:layout_centerVertical="true"/>

Using the above mentioned changes your deletion will work properly

With your **fix** this is what i've got : NullPointerException: Attempt to invoke virtual method 'java.lang.CharSequence android.widget.TextView.getText()' on a null object reference.Thanks for trying to help! — Ispam, Jan 03 '17 at 23:49
Please share you full activity code, so that I can use to debug it at my side. — Dharmendra Pratap Singh, Jan 04 '17 at 04:25
Hope this solution will help you. Please revert back if there any problem thx. — Dharmendra Pratap Singh, Jan 05 '17 at 05:44
Hi! Sorry for the delay on the response. I've implemented ur solution, but been getting this error : **java.lang.RuntimeException: Unable to start activity ComponentInfo{tech.destinum.listapp/tech.destinum.listapp.MainActivity}: android.database.sqlite.SQLiteException: no such column: _id (code 1): , while compiling: SELECT Item, _id FROM List** — Ispam, Jan 07 '17 at 17:30
You just have to clear the cache of your app and install it fresh. As in your implementation you have named it as ID instead of _id. thanks. — Dharmendra Pratap Singh, Jan 08 '17 at 05:58

score 0 · Answer 2 · edited May 23 '17 at 12:08

First of all, please provide complete code for the custom array adapter in which layouts are getting inflated.

Secondly, the following attribute in Row.xml is not suitable here.

android:onClick="deleteItemH"

That will only work when you're inflating the view directly from your Activity (Click here for more details). You should use setOnClickListener for btnDelete in your custom array adapter.

Again, more help can be provided after having a look at custom array adapter.

Remove ListItem from Listview attached to SQLite

2 Answers2