java - 如何从数据库表中删除特定项目？

Question

我想制作一个待办事项列表应用程序，并且我想通过点击复选框来删除列表中的项目。

我尝试在数据库类中创建一个“deleteTask”(如您在代码中看到的)方法。另外，您还可以看到“populateListView” 方法，它将数据库中的数据提供到 ListView 中，每次从数据库中删除任务后我都用它来刷新。

 public void deleteTask(String task) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NAME, COL2 , new String[]{task});
    }

<小时/>

 public void populateListView() {
        try {
            mDataBaseHelper = new DataBaseHelper(MainActivity.this);
            data = mDataBaseHelper.getData();
            mArrayList = new ArrayList<>();
            if (data.getCount() != 0) {
                while (data.moveToNext()) {
                    mArrayList.add(data.getString(1));
                    ListAdapter listAdapter = new ArrayAdapter(MainActivity.this, R.layout.list_items, R.id.checkBox, mArrayList);
                    list = (ListView) findViewById(R.id.myListId);
                    list.setAdapter(listAdapter);
                }
                mDataBaseHelper.close();
            } else {
                toastMessage("the Database is empty");
            }
        }catch(Exception e){
            Log.e(TAG, "populateListView: error"+e.getStackTrace() );
        }
    }

当应用程序启动时，我点击了要删除的项目，但我看到项目开始按上面的顺序删除!每次我点击任何复选框时都会一一进行。

Answer 1

你想要:-

public void deleteTask(String task) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_NAME, COL2 + "=?" , new String[]{task});
}

如果您没有通过使用 db.delete(TABLE_NAME, COL2 , new String[]{task}); 来使用 try/catch 来捕获错误，您将得到一个异常的:-

java.lang.IllegalArgumentException: Too many bind arguments.  1 arguments were provided but the statement needs 0 arguments.

但是

假设按顺序删除行而不是根据选中的项目删除行的问题可能是由于对选中的项目的处理造成的。但是，由于未提供此代码，因此只能通过猜测来知道代码中哪里出错了。

有一件事是，您不希望每次填充 ListView 时都创建一个新的 listadapter 实例。

作为处理 ListView 的提示，但根据 COL2 值长按时删除项目，也许可以考虑基于您的代码的以下内容(但根据长按项目删除):-

public void populateLisView() {
    mDataBaseHelper = new DataBaseHelper(this);  //<<<<<<<<<< NOTE 1
    list = (ListView) this.findViewById(R.id.myListId); //<<<<<<<<<< NOTE 1
    data = mDataBaseHelper.getData(); //<<<<<<<<<< get the data to be listed

    if (listadapter == null) { //<<<<<<<<<< Only need to instantiate one adapter when it has not bee instantiated
        listadapter = new ArrayAdapter<>(this,android.R.layout.simple_list_item_1,android.R.id.text1,data); // for convenience using a stock layout
        list.setAdapter(listadapter);
        //<<<<<<<<<<< add the onItemLongClick listener
        list.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
                mDataBaseHelper.deleteTaskByCol2(data.get(position)); //<<<<<<<<<< gets the value of the item according to it's position in the list
                populateLisView(); //<<<<<<<<<< as the item has been deleted then refresh the Listview
                return true; // flag the event as having been handled.
            }
        });
    //<<<<<<<<<<< If the Adapter has been instantiated then refresh the ListView's data
    } else {
        listadapter.clear(); // Clear the data from the adapter
        listadapter.addAll(data); // add the new changed data to the adapter
        listadapter.notifyDataSetChanged(); // tell the adapter that the data has changed
    }
}

• 注1

  • 您通常会实例化这些变量一次。

• 查看评论

您可能希望编辑您的问题以包括您如何处理检查事件。

完整的工作示例

DatabaseHelper.java

• 请注意，这可能与您的略有不同

  公共(public)类 DataBaseHelper 扩展 SQLiteOpenHelper {

  public static final String DBNAME = "mydb";
  public static final int DBVERSION = 1;
  
  public static final String TABLE_NAME = "mytable";
  public static final String COL1 = "col1";
  public static final String COL2 = "col2";
  
  SQLiteDatabase db;
  
  private static final String CRT_MYTABLE_SQL = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
          "(" +
          COL1 + " TEXT, " +
          COL2 + " TEXT" +
          ")";
  public DataBaseHelper(Context context) {
      super(context, DBNAME, null, DBVERSION);
      db = this.getWritableDatabase();
  }
  
  @Override
  public void onCreate(SQLiteDatabase db) {
      db.execSQL(CRT_MYTABLE_SQL);
  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  
  }
  
  public long addMytableRow(String col1, String col2) {
      ContentValues cv = new ContentValues();
      cv.put(COL1,col1);
      cv.put(COL2,col2);
      return db.insert(TABLE_NAME,null,cv);
  }
  
  public ArrayList<String> getData() {
      ArrayList<String> rv = new ArrayList<>();
      Cursor csr = db.query(TABLE_NAME,null,null,null,null,null,null);
      while (csr.moveToNext()) {
          rv.add(csr.getString(csr.getColumnIndex(COL2)));
      }
      csr.close();
      return rv;
  }
  
  public void deleteTaskByCol2(String task) {
      db.delete(TABLE_NAME,COL2 + "=?",new String[]{task});
  }
  

  }

MainActivity.java

即基于您的代码的示例 Activity ，但根据上面的内容:-

public class MainActivity extends AppCompatActivity {

    DataBaseHelper mDataBaseHelper;
    ArrayList<String> data;
    ListView list;
    ArrayAdapter<String> listadapter;

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

    private void example001() {
    }

    public void populateLisView() {
        mDataBaseHelper = new DataBaseHelper(this);
        list = (ListView) this.findViewById(R.id.myListId);
        data = mDataBaseHelper.getData();
        if (listadapter == null) {
            listadapter = new ArrayAdapter<>(this,android.R.layout.simple_list_item_1,android.R.id.text1,data);
            list.setAdapter(listadapter);
            list.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
                @Override
                public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
                    //mDataBaseHelper.deleteTaskWrong(data.get(position)); // ooops
                    mDataBaseHelper.deleteTaskByCol2(data.get(position));
                    populateLisView();
                    return true;
                }
            });
        } else {
            listadapter.clear();
            listadapter.addAll(data);
            listadapter.notifyDataSetChanged();
        }
    }

    private void addSomeTestData() {
        if (mDataBaseHelper == null) {
            mDataBaseHelper = new DataBaseHelper(this);
        }
        if (DatabaseUtils.queryNumEntries(mDataBaseHelper.getWritableDatabase(),DataBaseHelper.TABLE_NAME) > 0) return;
        mDataBaseHelper.addMytableRow("Test1","Test1");
        mDataBaseHelper.addMytableRow("Test2","Test2");
        mDataBaseHelper.addMytableRow("Test3","Test3");
        mDataBaseHelper.addMytableRow("Test4","Test4");
    }
}

• 注意 AddSomeTestData 添加一些用于测试/演示的数据。

结果

第一次运行时:-

长按后测试2

即长按的项目已被删除(从列表和数据库中)并且列表已刷新。