問題描述
How to delete a row in Sqlite database in Android application by user at listview (How to delete a row in Sqlite database in Android application by user at listview)
I made an Android application with a SQLite database. I want the user to be able to select a row of the database in a listview and be able to delete that specific row. I already have the database and the listview. But I can't find out how to delete a row. Can someone please help me? Here's my DatabaseHelper:
package com.persoonlijk.rooster.test2;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import java.util.ArrayList;
import java.util.List;
public class DataManipulator
{
public static final String KEY_ROWID = "id";
public static final String KEY_DAG = "dag";
public static final String KEY_UUR = "uur";
public static final String KEY_VAK = "vak";
public static final String KEY_LOKAAL = "lokaal";
private static final String DATABASE_NAME = "mydatabase.db";
static final String TABLE_NAME = "newtable";
private static final int DATABASE_VERSION = 11;
private static Context context;
static SQLiteDatabase db;
private SQLiteStatement insertStmt;
private static final String INSERT = "insert into " + TABLE_NAME + " (dag,uur,vak,lokaal) values (?,?,?,?)";
public DataManipulator(Context context) {
DataManipulator.context = context;
OpenHelper openHelper = new OpenHelper(DataManipulator.context);
DataManipulator.db = openHelper.getWritableDatabase();
this.insertStmt = DataManipulator.db.compileStatement(INSERT);
}
public long insert(String dag,String uur,String vak,String lokaal) {
this.insertStmt.bindString(1, dag);
this.insertStmt.bindString(2, uur);
this.insertStmt.bindString(3, vak);
this.insertStmt.bindString(4, lokaal);
return this.insertStmt.executeInsert();
}
public void deleteAll() {
db.delete(TABLE_NAME, null, null);
}
public List<String[]> selectAll()
{
List<String[]> list = new ArrayList<String[]>();
Cursor cursor = db.query(TABLE_NAME, new String[] { "id","dag","uur","vak","lokaal" }, null, null, null, null, "dag asc");
int x=0;
if (cursor.moveToFirst()) {
do {
String[] b1=new String[] {cursor.getString(0),cursor.getString(1),cursor.getString(2),
cursor.getString(3),cursor.getString(4)};
list.add(b1);
x=x+1;
} while (cursor.moveToNext());
}
if (cursor != null && !cursor.isClosed()) {
cursor.close();
}
cursor.close();
return list;
}
public void delete(int rowId) {
db.delete(TABLE_NAME, null, null);
}
private static class OpenHelper extends SQLiteOpenHelper {
OpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + TABLE_NAME + " (id INTEGER PRIMARY KEY, dag TEXT, uur TEXT, vak TEXT, lokaal TEXT)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
}
}
Here is my listview of the data in the database: package com.persoonlijk.rooster.test2;
import java.util.ArrayList;
import java.util.List;
import com.persoonlijk.rooster.test2.SaveData;
import android.app.ListActivity;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListView;
import android.widget.TextView;
import android.view.View.OnClickListener;
import android.app.Activity;
public class MainActivity extends ListActivity{
TextView selection;
public int idToModify;
DataManipulator dm;
List<String[]> list = new ArrayList<String[]>();
List<String[]> names2 =null ;
String[] stg1;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.check);
dm = new DataManipulator(this);
names2 = dm.selectAll();
stg1=new String[names2.size()];
int x=0;
String stg;
for (String[] dag : names2) {
stg = dag[1]+" ‑ "+dag[2]+ " ‑ "+dag[3]+" ‑ "+dag[4];
stg1[x]=stg;
x++;
}
ArrayAdapter<String> adapter = new ArrayAdapter<String> (this,android.R.layout.simple_list_item_1,stg1);
this.setListAdapter(adapter);
selection=(TextView)findViewById(R.id.selection);
}
public void onListItemClick(ListView parent, View v, int position, long id) {
selection.setText(stg1[position]);
}
//menuknoppen
@Override
public boolean onCreateOptionsMenu(Menu menu) {
menu.add(Menu.NONE, 0, 0, "Voeg gegevens toe");
menu.add(Menu.NONE, 1, 1, "Verwijder gegevens");
return super.onCreateOptionsMenu(menu);
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
switch (item.getItemId()) {
case 0:
startActivity(new Intent(this, SaveData.class));
return true;
case 1:
startActivity(new Intent(this, VerwijderData.class));
return true;
}
return false;
}
}
This is what the code looks like now: package com.persoonlijk.rooster.test2;
import java.util.ArrayList;
import java.util.List;
import android.app.ListActivity;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.view.ContextMenu;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
public class MainActivity extends ListActivity{
TextView selection;
public int idToModify;
DataManipulator dm;
List<String[]> list = new ArrayList<String[]>();
List<String[]> names2 =null ;
String[] stg1;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.check);
dm = new DataManipulator(this);
names2 = dm.selectAll();
stg1=new String[names2.size()];
int x=0;
String stg;
for (String[] dag : names2) {
stg = dag[1]+" ‑ "+dag[2]+ " ‑ "+dag[3]+" ‑ "+dag[4];
stg1[x]=stg;
x++;
}
ArrayAdapter<String> adapter = new ArrayAdapter<String> (this,android.R.layout.simple_list_item_1,stg1);
this.setListAdapter(adapter);
selection=(TextView)findViewById(R.id.selection);
}
//menuknoppen
@Override
public boolean onCreateOptionsMenu(Menu menu) {
menu.add(Menu.NONE, 0, 0, "Voeg gegevens toe");
menu.add(Menu.NONE, 1, 1, "Verwijder gegevens");
return super.onCreateOptionsMenu(menu);
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
switch (item.getItemId()) {
case 0:
startActivity(new Intent(this, SaveData.class));
return true;
case 1:
startActivity(new Intent(this, VerwijderData.class));
return true;
}
return false;
}
}
Right now, in my MainActivity in the menu you can click on 'verwijder' and then you go to the VerwijderData activity. This VerwijderData Activity used to be the MainActivity. In the VerwijderData activity the user deletes the data, and this is the activity where my application won't refresh itself. This is the code from the VerwijderData activity: package com.persoonlijk.rooster.test2;
import java.util.ArrayList;
import java.util.List;
import android.app.ListActivity;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.view.ContextMenu;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
public class VerwijderData extends ListActivity{
TextView selection;
public int idToModify;
DataManipulator dm;
ArrayAdapter<String> adapter;
List<String[]> list = new ArrayList<String[]>();
List<String[]> names2 =null ;
String[] stg1;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.verwijder);
dm = new DataManipulator(this);
names2 = dm.selectAll();
stg1=new String[names2.size()];
int x=0;
String stg;
for (String[] dag : names2) {
stg = dag[1]+" ‑ "+dag[2]+ " ‑ "+dag[3]+" ‑ "+dag[4];
stg1[x]=stg;
x++;
}
adapter = new ArrayAdapter<String> (this,android.R.layout.simple_list_item_1,stg1);
this.setListAdapter(adapter);
selection=(TextView)findViewById(R.id.selection);
}
public void onListItemClick(ListView parent, View v, int position, long id) {
String[] delete = names2.get(position);
String idString = delete[0];
long idLong = Long.valueOf(idString);
Log.d("Deleting...", idLong + "");
dm.delete(idLong);
names2.remove(position);
stg1=new String[names2.size()];
int x=0;
String stg;
for (String[] dag : names2) {
stg = dag[1]+" ‑ "+dag[2]+ " ‑ "+dag[3]+" ‑ "+dag[4];
stg1[x]=stg;
x++;
}
adapter.notifyDataSetChanged();
}
}
My application has changed a little since I first asked my question, I hope you still understand what I mean.
‑‑‑‑‑
參考解法
方法 1:
Firstly we need to re‑work your database delete method. it won't do anything the way it is.
public boolean delete(long rowId) {
/* this is what your database delete method should look like
this method deletes by id, the first column in your database*/
return db.delete(TABLE_NAME, KEY_ROWID + "=" + rowId, null) > 0;
}
Now the rest is in the Activity's onListItemClick method
public void onListItemClick(ListView parent, View v, int position, long id) {
/* it looks like you're using an ArrayAdapter so you're gonna need to delete
both from the listview and from the database.
first we refer specifically to the corresponding item in the list by position
which is provided by the listItemClick parameter*/
String[] delete = names2.get(position);
/* we do this because we want the id to delete by in the database by id
based on your dm.selectAll() method, it should be at the first index of the array*/
String idString = delete[0];
// you've made it into a string, but we need it back to being a long value
long idLong = Long.valueOf(idString);
Log.d("Deleting...", idLong + "");
/* i put in the log statement for debugging purposes, it will put the id value on Logcat
so if something goes wrong, we can at least rule out the step since we see the value being
removed, but!
finally we can delete it from our database because we have the parameter we need*/
dm.delete(idLong);
/* There it's done, but surely you want it off the listview too so no one is confused.
unfortunately you've made stg1 an array so we can't just remove the index from it*/
names2.remove(position);
stg1=new String[names2.size()];
int x=0;
String stg;
for (String[] dag : names2) {
stg = dag[1]+" ‑ "+dag[2]+ " ‑ "+dag[3]+" ‑ "+dag[4];
stg1[x]=stg;
x++;
}
/* can you see what happened? the `List` collection is able have its components dynamically
added and removed, so i removed from that, then i remade the stg1 collection using your method.*/
adapter.notifyDataSetChanged();
// ^ that command refreshes the listview to see changes.
}
All things considered, you've made this task much more tedious than it needs to be with your current setup. Firstly you've made plenty of intermediary collections before inputting to your adapter, then you've put an array as the datasource, which can't be dynamically reszied.
More satisfactory would be to have a setup utilizing a CursorAdapter. Then you could make use of the id parameter provided on the ListItemClick method as it would be the exact id that we wanted. Moreover, you'd only need to delete from the one database source, and simply refresh the adapter afterwards.
Make your adapter a field like some of those other variables in order to refrence it throughout the entire class:
public class MainActivity extends ListActivity{
TextView selection;
public int idToModify;
DataManipulator dm;
......
ArrayAdapter<String> adapter; <‑‑‑‑‑‑‑‑‑ add that
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.check);
.........
adapter = new ArrayAdapter<String> (this,android.R.layout.simple_list_item_1,stg1); <‑‑‑‑‑‑ edit that
this.setListAdapter(adapter);
}
}
方法 2:
Creating context menu and adding menu item "delete" and delete the item from adapter or Database.
Here I have just deleted item from only adapter. Please call your delete method via SQliteOpenHelper
public void onCreateContextMenu(android.view.ContextMenu menu, View v, ContextMenuInfo menuInfo)
{
super.onCreateContextMenu(menu, v, menuInfo);
MenuInflater inflater = getMenuInflater();
inflater.inflate(R.menu.contextmenu, menu);
}
@Override
public boolean onContextItemSelected(MenuItem item)
{
AdapterContextMenuInfo info = (AdapterContextMenuInfo) item.getMenuInfo();
switch (item.getItemId()) {
case R.id.deleteItem:
//here's my question mark :)
adapter.remove(adapter.getItem(info.position));
adapter.notifyDataSetChanged();
return true;
default:
return super.onContextItemSelected(item);
}
}
(by user1941141、mango、Vikalp Patel)