M.Bill M.Bill - 3 months ago 8
Android Question

How to remove an item in an SQL database?

I know there are lots of threads with more or less same topic but none of them covers my situation:

I have delete button that delete one item in a listView but when you close the app and reopen it the items reappears. I don't know how to fix this .

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "todo.db";
public static final int DATABASE_VERSION = 1;
public static final String ITEMS_TABLE = "items";

private static DatabaseHelper instance = null;

public static DatabaseHelper getInstance(Context context) {

if(instance == null) {
instance = new DatabaseHelper(context);
}
return instance;
}

private DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {

String createQuery = "CREATE TABLE " + ITEMS_TABLE + " (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT," +
"description TEXT NOT NULL, " +
"completed INTEGER NOT NULL DEFAULT 0)";
db.execSQL(createQuery);
}

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

}
}


The button works fine but it does not delete permently I don't know if it is the code or if it is where I am placing it in my
MainActivity
if someone would please tell that would much appreciated.

MainActivity.java

public class MainActivity extends AppCompatActivity {

private static final String LOG_TAG = "ToDoApp";

private ToDoListManager listManager;
private ToDoItemAdapter adapter;

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

ListView todoList = (ListView) findViewById(R.id.todo_list);

listManager = new ToDoListManager(getApplicationContext());

adapter = new ToDoItemAdapter(
this,
listManager.getList()
);

todoList.setAdapter(adapter);

ImageButton addButton = (ImageButton) findViewById(R.id.add_item);
addButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
onAddButtonClick();
}
});
}

@Override
protected void onPause() {
super.onPause();
}

private void onAddButtonClick() {
AlertDialog.Builder builder = new AlertDialog.Builder(this);
builder.setTitle(R.string.add_item);

final EditText input = new EditText(this);
input.setInputType(InputType.TYPE_CLASS_TEXT);
builder.setView(input);

builder.setPositiveButton(
R.string.ok,
new DialogInterface.OnClickListener() {

@Override
public void onClick(DialogInterface dialog, int which) {
ToDoItem item = new ToDoItem(
input.getText().toString(),
false
);
listManager.addItem(item);
adapter.swapItems(listManager.getList());
}
});

builder.setNegativeButton(
R.string.cancel,
new DialogInterface.OnClickListener() {

@Override
public void onClick(DialogInterface dialog, int which) {
dialog.cancel();
}
});

builder.show();
}

private class ToDoItemAdapter extends ArrayAdapter<ToDoItem> {

private Context context;
private List<ToDoItem> items;
private LayoutInflater inflater;

public ToDoItemAdapter(
Context context,
List<ToDoItem> items
) {
super(context, -1, items);

this.context = context;
this.items = items;
this.inflater = LayoutInflater.from(context);
}

public void swapItems(List<ToDoItem> items) {
this.items = items;
notifyDataSetChanged();
}

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

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

final ItemViewHolder holder;

if(convertView == null) {

convertView = inflater.inflate(R.layout.to_do_item_layout, parent, false);


holder = new ItemViewHolder();
holder.itemDescription = (TextView) convertView.findViewById(R.id.item);
holder.itemState = (CheckBox) convertView.findViewById(R.id.checkBox);
convertView.setTag(holder);
}else {
holder = (ItemViewHolder) convertView.getTag();
}


holder.itemDescription.setText(items.get(position).getDescription());
holder.itemState.setChecked(items.get(position).isComplete());

holder.itemState.setTag(items.get(position));

convertView.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
ToDoItem item = (ToDoItem) holder.itemState.getTag();
item.toggleComplete();
listManager.updateItem(item);
notifyDataSetChanged();
}
});
ImageButton deleteButton = (ImageButton) convertView.findViewById(R.id.delete_Button);
deleteButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
items.remove(items.get(position));
notifyDataSetChanged();
}
});

holder.itemState.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
ToDoItem item = (ToDoItem) holder.itemState.getTag();
item.toggleComplete();
listManager.updateItem(item);
notifyDataSetChanged();

}
});

return convertView;
}
}

public static class ItemViewHolder{
public TextView itemDescription;
public CheckBox itemState;
}
}


ToDoListManager.java

public class ToDoListManager {

private DatabaseHelper dbHelper;

public ToDoListManager(Context context) {

dbHelper = DatabaseHelper.getInstance(context);
}

public List<ToDoItem> getList() {

SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.rawQuery(
"SELECT * FROM " + DatabaseHelper.ITEMS_TABLE,
null
);

List<ToDoItem> items = new ArrayList<>();

if(cursor.moveToFirst()) {
while(!cursor.isAfterLast()) {

ToDoItem item = new ToDoItem(
cursor.getString(cursor.getColumnIndex("description")),
cursor.getInt(cursor.getColumnIndex("completed")) != 0,
cursor.getLong(cursor.getColumnIndex("_id"))
);
items.add(item);
cursor.moveToNext();
}
}
cursor.close();
return items;
}

public void addItem(ToDoItem item) {

ContentValues newItem = new ContentValues();
newItem.put("description", item.getDescription());
newItem.put("completed", item.isComplete());

SQLiteDatabase db = dbHelper.getWritableDatabase();
db.insert(DatabaseHelper.ITEMS_TABLE, null, newItem);


}

public void updateItem(ToDoItem item) {

ContentValues editItem = new ContentValues();
editItem.put("description", item.getDescription());
editItem.put("completed", item.isComplete());

SQLiteDatabase db = dbHelper.getWritableDatabase();

String[] args = new String[] { String.valueOf(item.getId()) };

db.update(DatabaseHelper.ITEMS_TABLE, editItem, "_id=?", args);

}
}


ToDoItem.java

public class ToDoItem {

private String description;
private boolean isComplete;
private long id;

public ToDoItem(String description, boolean isComplete) {
this(description, isComplete, -1);
}
public ToDoItem(String description,boolean isComplete,long id) {
this.description = description;
this.isComplete = isComplete;
this.id = id;
}

public String getDescription() {
return description;
}

public boolean isComplete() {
return isComplete;
}

public void toggleComplete() {
isComplete = !isComplete;
}
public long getId() {return id;}

@Override
public String toString() {

return getDescription();
}
}

Answer

you just remove items in your adapter class that causes the item delete from the current listView. but you have not any function in your ToDoListManager.java that remove items from your database. deleting from a listView does not affect to your database items.

you can add this function to your ToDoListManager.java class

public void deleteItem(long itemId) {

    SQLiteDatabase db = dbHelper.getWritableDatabase();
    db.delete(DatabaseHelper.ITEMS_TABLE, "_id = ?",
            new String[] { String.valueOf(itemId) }); 
}

and call it from your delete button's onClick, like this

ImageButton deleteButton = (ImageButton) convertView.findViewById(R.id.delete_Button);
        deleteButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                long itemId = items.get(position).getId();
                items.remove(items.get(position));
                listManager.deleteItem(itemId);
                notifyDataSetChanged();
            }
        });
Comments