java - 如果使用sqlite存在，如何删除项目？

Question

我在SQLite上花了很多时间，如果存在删除项目，我遇到了问题！

我正在使用SQLite将链接从webview保存到listview的Bookmark App中工作，我的问题是无法检查该项目是否存在 > 不要创建链接。

这是我用于sqlite的BookmarksDatabase：

public class BookmarksDatabase extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "bookmarks.db";
    private static final String TABLE_NAME = "bookmarks_data";
    private static final String COL1 = "ID";
    private static final String COL2 = "ITEM1";
    private static final String COL3 = "ITEM2";

    public BookmarksDatabase(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " + " ITEM1 TEXT, " + " ITEM2 TEXT)";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public boolean addData(String item1, String item2) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL2, item1);
        contentValues.put(COL3, item2);

        long result = db.insert(TABLE_NAME, null, contentValues);

        // if date as inserted incorrectly it will return -1
        return result != -1;
    }
    public Cursor getListContents() {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
    }

    public ArrayList<Bookmarks> getAllData() {
        ArrayList<Bookmarks> arrayList = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor data = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);

        while (data.moveToNext()) {
            int id = data.getInt(0);
            String title = data.getString(1);
            String link = data.getString(2);
            Bookmarks bookmarks = new Bookmarks(id, title, link);

            arrayList.add(bookmarks);
        }
        return arrayList;
    }

    public int deleteSpecificContents(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, COL1 + "=?", new String[]{Integer.toString(id)});
    }
}

这是我在MainActivity中用于获取列表视图项目的代码

/*---------------- Bookmark Tab, sqlite databases integrated ---------------*/
    private void showBookmarksScreen() {

        // initialize a dialog in the main activity
        final Dialog bookmarksScreen = new Dialog(this);

        bookmarksScreen.requestWindowFeature(Window.FEATURE_NO_TITLE);

        bookmarksScreen.setContentView(R.layout.activity_bookmark);

        bookmarksScreen.setCancelable(true);

        final ListView listView = bookmarksScreen.findViewById(R.id.bookmark_list);
        RelativeLayout bookmarkEmpty = bookmarksScreen.findViewById(R.id.bookmark_empty);

        // create an array and call bookmark
        // database to retrive data then
        // fetch it into list view
        arrayList = new ArrayList<>();
        arrayList = bookmarkDB.getAllData();

        // get all data from sqlite database
        Cursor data = bookmarkDB.getListContents();

        // check if no bookmarks
        // then show view that inform
        // user that there is no bookmarks
        if(data.getCount() == 0 ) {
            bookmarkEmpty.setVisibility(View.VISIBLE);
        }

        bookmarkListAdpater = new BookmarkListAdpater(this, arrayList);
        listView.setAdapter(bookmarkListAdpater);
        bookmarkListAdpater.notifyDataSetChanged();

        // load link on item click
        listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                TextView link = view.findViewById(R.id.list_link);
                String convertedLink = link.getText().toString();
                webView.loadUrl(convertedLink);
                bookmarksScreen.dismiss();
            }
        });

        // ask user to delete bookmark on item long click
        listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> parent, View view, final int position, long id) {

                // initialize a dialog in the main activity
                final Dialog deleteDialog = new Dialog(MainActivity.this);

                deleteDialog.requestWindowFeature(Window.FEATURE_NO_TITLE);

                deleteDialog.setContentView(R.layout.activity_confirm);

                // confirm message or dialog can't be
                // canceled so we set it to false
                deleteDialog.setCancelable(false);

                TextView deleteMessage = deleteDialog.findViewById(R.id.confirm_text);
                TextView deleteConfirm = deleteDialog.findViewById(R.id.confirm_allow);
                TextView deleteCancel = deleteDialog.findViewById(R.id.confirm_deny);

                deleteMessage.setText(getString(R.string.delete_bookmark));

                // confirm button
                deleteConfirm.setOnClickListener(new View.OnClickListener() {

                    @Override
                    public void onClick(View v) {

                        Cursor data = bookmarkDB.getListContents();
                        int i = 0;
                        while(data.moveToNext()){
                            if(i == position){
                                break;
                            }
                            i++;
                        }

                        bookmarkDB.deleteSpecificContents(data.getInt(0));

                        deleteDialog.dismiss();
                        bookmarksScreen.dismiss();
                        customToast(getString(R.string.bookmark_deleted), 0);

                    }

                });

                // confirm cancel
                deleteCancel.setOnClickListener(new View.OnClickListener() {

                    @Override
                    public void onClick(View v) {

                        deleteDialog.dismiss();

                    }

                });

                deleteDialog.getWindow().setBackgroundDrawable(new ColorDrawable(Color.TRANSPARENT));

                deleteDialog.show();

                return true;
            }
        });

        bookmarksScreen.getWindow().setBackgroundDrawable(new ColorDrawable(Color.TRANSPARENT));

        bookmarksScreen.getWindow().setLayout(WindowManager.LayoutParams.MATCH_PARENT, WindowManager.LayoutParams.MATCH_PARENT);

        bookmarksScreen.show();

    }

我的模特

public class Bookmarks {

    int id;
    String title, link;

    public Bookmarks(int id, String title, String link) {
        this.id = id;
        this.title = title;
        this.link = link;
    }

    public  Bookmarks() {}

    public int getId() {
        return id;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getLink() {
        return link;
    }

    public void setLink(String link) {
        this.link = link;
    }
}

有任何想法吗？谢谢。

标签： javaandroidsqlite

Answer 1

使用您使用的相同逻辑：

SQLiteDatabase db = new DatabaseManager(context).getWritableDatabase();

String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + ID_KEY + " = ?";
Cursor cursor = db.rawQuery(query, new String[]{Integer.toString(id)});

if(cursor.moveToFirst()){
      db.delete(TABLE_NAME, ID_KEY + " = ?", new String[]{Integer.toString(id)});
}

cursor.close();
db.close();

另一种可能性是将方法返回类型更改为布尔值并验证删除命令的返回计数：

public boolean deleteSpecificContents(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME, COL1 + "=?", new String[]{Integer.toString(id)}) > 0;
}