Android: How can I delete an item from a listview that is saved in a SQLite database?

Question

I've to delete an item from a listview that is saved in a SQLite database. I can do it using a search "EditText", but I need to remove rows only clicking on them. Here's my code:

DBHelper:

public class EventDbHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "USERINFO.DB";
private static final int DATABASE_VERSION = 1;
private static final String CREATE_QUERY =
        "CREATE TABLE "+ Event.NewEventInfo.TABLE_NAME+"("+
                Event.NewEventInfo.NAME+" TEXT,"+
                Event.NewEventInfo.YEAR+" TEXT,"+
                Event.NewEventInfo.MONTH+" TEXT,"+
                Event.NewEventInfo.DAY+" TEXT,"+
                Event.NewEventInfo.HOUR+" TEXT,"+
                Event.NewEventInfo.MINUTE+" TEXT);";

public EventDbHelper(Context context){

    super(context,DATABASE_NAME,null,DATABASE_VERSION);
    Log.e("DATABASE OPERATIONS","Database created/opened...");

}

@Override
public void onCreate(SQLiteDatabase db) {

    db.execSQL(CREATE_QUERY);
    Log.e("DATABASE OPERATIONS","Table created...");

}

public void addInformation (String name,String year,String month,String day,String hour,String minute,SQLiteDatabase db)
{
    ContentValues contentValues=new ContentValues();
    contentValues.put(Event.NewEventInfo.NAME,name);
    contentValues.put(Event.NewEventInfo.YEAR,year);
    contentValues.put(Event.NewEventInfo.MONTH,month);
    contentValues.put(Event.NewEventInfo.DAY,day);
    contentValues.put(Event.NewEventInfo.HOUR,hour);
    contentValues.put(Event.NewEventInfo.MINUTE,minute);
    db.insert(Event.NewEventInfo.TABLE_NAME,null,contentValues);
    Log.e("DATABASE OPERATIONS","One row inserted...");
}

public Cursor getInformation (SQLiteDatabase db)
{
    Cursor cursor;
    String[] projections = {Event.NewEventInfo.NAME, Event.NewEventInfo.YEAR, Event.NewEventInfo.MONTH,
            Event.NewEventInfo.DAY, Event.NewEventInfo.HOUR, Event.NewEventInfo.MINUTE};
    cursor = db.query(Event.NewEventInfo.TABLE_NAME,projections,null,null,null,null,null);
    return cursor;

}



public void deleteInformation(String name, SQLiteDatabase db)
{
    String selection = Event.NewEventInfo.NAME+" LIKE ?";
    String[] selection_args = {name};
    db.delete(Event.NewEventInfo.TABLE_NAME,selection,selection_args);
}




@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}
}

MainActivity:

 public void deleteEvent (View view)
{
    search_name=et.getText().toString();
    eventDbHelper= new EventDbHelper(getApplicationContext());
    sqLiteDatabase= eventDbHelper.getReadableDatabase();
    eventDbHelper.deleteInformation(search_name, sqLiteDatabase);
    Toast.makeText(getApplicationContext(),"Event  deleted",Toast.LENGTH_SHORT).show();
    finish();
    startActivity(getIntent());

}

ListDataAdapterActivity:

public class ListDataAdapter extends ArrayAdapter {
List list = new ArrayList();
public ListDataAdapter(Context context, int resource) {
    super(context, resource);
}

static class LayoutHandler
{
    TextView NAME,DAYS,HOURS,MINUTES,SECONDS;

}

@Override
public void add(Object object) {
    super.add(object);
    list.add(object);
}

@Override
public int getCount() {
    return list.size();
}

@Override
public Object getItem(int position) {
    return list.get(position);
}

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

    View row = convertView;
    LayoutHandler layoutHandler;
    if(row == null)
    {
        LayoutInflater layoutInflater =(LayoutInflater) this.getContext().getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        row = layoutInflater.inflate(R.layout.list_item,parent,false);
        layoutHandler = new LayoutHandler();
        layoutHandler.NAME = (TextView) row.findViewById(R.id.text_name);
        layoutHandler.DAYS = (TextView) row.findViewById(R.id.days_list_item);
        layoutHandler.HOURS = (TextView) row.findViewById(R.id.hours_list_item);
        layoutHandler.MINUTES = (TextView) row.findViewById(R.id.minutes_list_item);
        //layoutHandler.SECONDS = (TextView) row.findViewById(R.id.seconds_list_item);
        row.setTag(layoutHandler);
    }

    else{

        layoutHandler =(LayoutHandler) row.getTag();

    }

    DataProvider dataProvider =(DataProvider) this.getItem(position);
    layoutHandler.NAME.setText(dataProvider.getName());
    layoutHandler.DAYS.setText(dataProvider.getsDays());
    layoutHandler.HOURS.setText(dataProvider.getsHours());
    layoutHandler.MINUTES.setText(dataProvider.getsMinutes());
    //layoutHandler.SECONDS.setText(dataProvider.getsSeconds());

    return row;
}

}

What am I supposed to edit?

Answer 1

Here is a simple example of a listview populated by a SQLite database.

I will only explain how to read informations and how to delete a row afterwards.

First of all, you have to create a modal for the object you are listing (optional). The listview could also be populated by a List of Strings or primitive variable types.

Lets create a Category modal which contains a name and a short description. It contains fields, constructors, getters and setters :

public class Category {

    //Fields
    private int id;
    private String name;
    private String description;

    //Constructors
    public Category()
    {

    }

    public Category(String name, String description)
    {
        this.name = name;
        this.description = description;
    }

    public Category(int id, String name, String description)
    {
        this.id = id;
        this.name = name;
        this.description = description;
    }

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

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

    public void setDescription(String description)
    {
        this.description = description;
    }

    //Getters
    public int getId()
    {
        return this.id;
    }

    public String getName()
    {
        return this.name;
    }

    public String getDescription()
    {
        return this.description;
    }

}

We will populate our listView with a list of categories. This list get informations from the database.

Finally, you need to create a custom adapter (or to use a predefined one) to link every category items to a xml layout file.

public class CategoryAdapter extends BaseAdapter {

    private Activity activity;
    private LayoutInflater inflater;
    private List<Category> items;

    public CategoryAdapter(Activity activity, List<Category> items) {
        this.activity = activity;
        this.items = items;
    }

    @Override
    public int getCount() {
        return items.size();
    }

    @Override
    public Object getItem(int location) {
        return items.get(location);
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

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

        if (inflater == null)
            inflater = (LayoutInflater) activity.getSystemService(Context.LAYOUT_INFLATER_SERVICE);

        if (convertView == null)
            convertView = inflater.inflate(R.layout.item_category, null);

        TextView tvName = (TextView) convertView.findViewById(R.id.tvName);
        TextView tvDescription = (TextView) convertView.findViewById(R.id.tvDescription);

        // getting product data for the row
        Category c = items.get(position);

        // name
        tvName.setText(c.getName());

        // description
        tvDescription.setText(String.valueOf(c.getDescription()));

        return convertView;
    }

}

XML

<?xml version="1.0" encoding="utf-8"?>

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

    <TextView
        android:id="@+id/tvName"
        android:layout_height="wrap_content"
        android:layout_width="wrap_content"
        android:textStyle="bold"/>

    <TextView
        android:id="@+id/tvDescription"
        android:layout_height="wrap_content"
        android:layout_width="wrap_content"
        android:textColor="@color/gray"/>

</LinearLayout>

It's time to link the adapter and the list of categories to the listview.

db = new DatabaseHelper(getApplicationContext());
categories = new ArrayList<>();
categories = db.getAllCategories();
db.closeDB();

//The category adapter links the list of categories to the listview
lvCategories = (ListView) findViewById(R.id.lvCategories);
adapter = new CategoryAdapter(this, categories);
lvCategories.setAdapter(adapter);

The last part of this short tutorial shows how to delete a row when you long click the item. A popup window ask the user to confirm the operation.

        //onLongClick : delete item
        lvCategories.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {

                try {
                    final Category category = (Category) parent.getItemAtPosition(position);

                    //Open dialog box
                    AlertDialog.Builder alertDialogBuilder = new AlertDialog.Builder(c);

                    alertDialogBuilder
                            .setPositiveButton(R.string.confirm, new DialogInterface.OnClickListener() {
                                public void onClick(DialogInterface dialog, int id) {

                                    //Delete client by id
                                    db.deleteCategory(category.getId());

                                    //Confirmation message
                                    Toast.makeText(c, R.string.delete_category_successfull, Toast.LENGTH_SHORT).show();

                                    //Rafraîchir la liste
                                    onResume();

                                }
                            })
                            .setNegativeButton(R.string.cancel, new DialogInterface.OnClickListener() {
                                public void onClick(DialogInterface dialog, int id) {
                                    //Cancel operation

                                }
                                // Create the AlertDialog object and return it
                                // return builder.create();
                            })
                            .setTitle(getString(R.string.delete))
                            .setMessage(getString(R.string.delete_category, category.getName()))
                            .create();

                    alertDialogBuilder.show();

                } catch (Exception e) {
                    Toast.makeText(c, String.valueOf(e), Toast.LENGTH_LONG).show();
                }


                return true;
            }

        });

ON RESUME

categories = db.getAllCategories();
db.closeDB();

adapter = new CategoryAdapter(this, categories);
lvCategories.setAdapter(adapter);

Here are getAllCategories and deleteCategory queries from DatabaseHelper :

/*
 * getting all categories
 * */
public List<Category> getAllCategories() {
    List<Category> categories = new ArrayList<Category>();
    String selectQuery = "SELECT  * FROM " + TABLE_CATEGORIES;

    Log.e(LOG, selectQuery);

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (c.moveToFirst()) {
        do {
            Category category = new Category();
            category.setId(Integer.parseInt(c.getString(c.getColumnIndex(ID))));
            category.setName(c.getString((c.getColumnIndex(CATEGORY_NAME))));
            category.setDescription((c.getString(c.getColumnIndex(CATEGORY_DESCRIPTION))));

            // adding to clients list
            categories.add(category);
        } while (c.moveToNext());
    }

    return categories;
}


/*
 * Deleting a category
 */
public void deleteCategory(int category_id) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_CATEGORIES, ID + " = ?",
            new String[] { String.valueOf(category_id) });
}

Don't be shy to ask if you need further assistance to insert items in database, update them, or anything else !

Answer 2

You should use an adapter to link a xml layout file and a list of object element to respectively display the items and populate the listview.

Then, delete the item in the database and refresh list.