android - 如何删除 SQL 数据库上的行

Question

我有一个带有 SQL 数据库 的 ListView。可以使用两个 EditText 动态添加它的值。

我想知道如何删除我的 SQL 数据库 中的一行。现在我可以通过 rowId 删除一行，因此将删除包含所有值的整行。但我希望只删除该行，这样就会有连续的数字。

例如 ListView。

1 Text1
2 Text2
3 Text3
4 Text4

如果我要删除第 3 行，我的代码会这样:

1 Text1
2 Text2
4 Text4

但我希望删除第 3 行时看起来像这样:

1 Text1
2 Text2
3 Text4

我的代码:

主 Activity

private void listViewItemLongClick() {
        ListView myList = (ListView) findViewById(R.id.list);
        myList.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> parent, View viewClicked, final int position, final long id) {

                AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
                        //Set header
                        builder.setTitle("delete");
                        builder.setMessage("Are you sure ? ");
                                //set the OK button with an onClickListener
                        builder.setPositiveButton("yes", new DialogInterface.OnClickListener() {
                            //edit the userinput
                            public void onClick(DialogInterface dialog, int whichButton) {
                                myDb.deleteRow(id);
                                populateListView();
                            }
                        }).setNegativeButton("no", new DialogInterface.OnClickListener() {
                    public void onClick(DialogInterface dialog, int whichButton) {
                        // Do nothing.
                    }
                }).show();

                return false;
            }
        });
    }

DBAdapter

public class DBAdapter {

    private static final String TAG = "DBAdapter"; //used for logging database version changes

    // Field Names:
    public static final String KEY_ROWID = "_id";
    public static final String KEY_WDH = "task";
    public static final String KEY_KG = "date";

    public static final String[] ALL_KEYS = new String[]{KEY_ROWID, KEY_WDH, KEY_KG};

    public static final String DATABASE_NAME = "dbToDo";
    public static final String DATABASE_TABLE = "mainToDo";
    public static final int DATABASE_VERSION = 2; // The version number must be incremented each time a change to DB structure occurs.

    //SQL statement to create database
    private static final String DATABASE_CREATE_SQL =
            "CREATE TABLE " + DATABASE_TABLE
                    + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + KEY_WDH + " TEXT NOT NULL, "
                    + KEY_KG + " TEXT"
                    + ");";

    private final Context context;
    private DatabaseHelper myDBHelper;
    private SQLiteDatabase db;


    public DBAdapter(Context ctx) {
        this.context = ctx;
        myDBHelper = new DatabaseHelper(context);
    }

    // Open the database connection.
    public DBAdapter open() {
        db = myDBHelper.getWritableDatabase();
        return this;
    }

    // Close the database connection.
    public void close() {
        myDBHelper.close();
    }

    // Add a new set of values to be inserted into the database.
    public long insertRow(String task, String date) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_WDH, task);
        initialValues.put(KEY_KG, date);

        // Insert the data into the database.
        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    // Delete a row from the database, by rowId (primary key)
    public boolean deleteRow(long rowId) {
        String where = KEY_ROWID + "=" + rowId;
        return db.delete(DATABASE_TABLE, where, null) != 0;
    }

    // Return all data in the database.
    public Cursor getAllRows() {
        String where = null;
        Cursor c = db.query(true, DATABASE_TABLE, ALL_KEYS, where, null, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    // Get a specific row (by rowId)
    public Cursor getRow(long rowId) {
        String where = KEY_ROWID + "=" + rowId;
        Cursor c = db.query(true, DATABASE_TABLE, ALL_KEYS,
                where, null, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    // Change an existing row to be equal to new data.
    public boolean updateRow(long rowId, String task, String date) {
        String where = KEY_ROWID + "=" + rowId;
        ContentValues newValues = new ContentValues();
        newValues.put(KEY_WDH, task);
        newValues.put(KEY_KG, date);
        // Insert it into the database.
        return db.update(DATABASE_TABLE, newValues, where, null) != 0;
    }


    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase _db) {
            _db.execSQL(DATABASE_CREATE_SQL);
        }

        @Override
        public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading application's database from version " + oldVersion
                    + " to " + newVersion + ", which will destroy all old data!");

            // Destroy old database:
            _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);

            // Recreate new database:
            onCreate(_db);
        }
    }


}

Answer 1

它很乱，因为它看起来像是您的身份列，而您正试图消除空白。这不是好的做法，但有一种机制可以做到这一点。从概念上讲，这将是一个 4 步过程。

1. 创建一个具有相同结构(包括自增标识字段)的新临时表。
2. 将主表中剩余的行推送到临时表中。我正要写这个，但是这样做的伪代码已经存在了。例如，here's an answer to a similar question有一个好的(包括我赞同这是一个坏主意的免责声明)。
3. 放下原来的 table 。
4. 将临时表重命名为原始表的名称。

请注意，每次执行删除操作时都会重新写入整个表，因此此解决方案的扩展性不佳。

执行此操作的更好方法是将非标识整数字段添加到您存储始终顺序整数值的表中。维护这个领域将是你的责任。例如……

在初始填充数据时，将此字段设置为等于标识字段。

在你的“删除一行”例程中

// Delete a row from the database, by rowId (primary key)
public boolean deleteRow(long rowId) {
    String where = KEY_ROWID + "=" + rowId;
    boolean myReturnValue;
    myReturnValue = db.delete(DATABASE_TABLE, where, null) != 0;
    db.execSQL("UPDATE " + DATABASE_TABLE + " SET MyNewNonIdentitySequentialIntegerField = MyNewNonIdentitySequentialIntegerField -1) WHERE KEY_ROWID > " + String.valueOf(rowId) + ";") ;
    return myReturnValue;
}

插入时或插入新记录后立即需要将其设置为 MAX(MyNewNonIdentitySequentialIntegerField) + 1 来填充它。