Android Studio Sqllite 自动增量重置 [英] Android Studio Sqllite autoincrement reset

查看：34 发布时间：2021/8/23 19:47:05 java android sqlite

本文介绍了Android Studio Sqllite 自动增量重置的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

db.execSQL("CREATE TABLE "+DBTable0+ "("+ROW2+"INTEGER PRIMARY KEY AUTOINCREMENT, "+ROW3+" VARCHAR NOT NULL, "+ROW0+"; TEXT NOT NULL, "+ROW1+" VARCHAR NOT NULL)");

这是我的数据库的创建表命令.

我想重置 ROW2 的值.

例如，我在这张表上有 5 条记录.

<块引用>

1, save1, 这是一个 Save1, 11.25

2, save2, 这是一个 Save2, 23.48

3，save3，这是一个 Save3，09.45

4, save4, 这是一个 Save4, 11.55

5, save5, 这是一个 Save5, 21.00

我想删除 save2.当我删除它或其他删除时，我想重置 ROW2 索引，所以

<块引用>

1, save1, 这是一个 Save1, 11.25

2, save3, 这是一个 Save3, 09.45

3, save4, 这是一个 Save4, 11.55

4, save5, 这是一个 Save5, 21.00

像这样...

我该怎么做.?

解决方案

要在使用 AUTOINCREMENT 时重置为补偿已删除行而分配的 rowid，您必须进行两项更改:-

您必须更改(更新)每行乱序的 rowid 或 rowid 的别名以跟随新的序列和
然后您必须将系统表sqlite_sequence中的相应行更改(更新)到分配的最高rowid.

这根本不可取，而且会大大加剧使用 AUTOINCREMENT 的低效率(当您很有可能不需要使用 AUTOINCREMENT 时).

SQLite Autoincrement 包括
<块引用>
AUTOINCREMENT 关键字会增加额外的 CPU、内存、磁盘空间和磁盘 I/O 开销，如果不是绝对需要，应该避免使用 .通常不需要.

事实上，不建议依赖 rowid 不是SQlite分配的任何东西

(INTEGER PRIMARY KEY(带或不带 AUTOINCREMENT)生成 rowid 的 <column_name> 和别名).

###你说:-

<块引用>

我想重置 ROW2 的值.

我建议重新评估您的愿望，只在确实有需要时才想要这个，而这在所有可能的情况下都没有.

###你接着说

<块引用>

我将通过 ListViews 选择记录.然后我可以删除和更新我选择的项目的记录.所以当我删除中间的任何记录时.id 正在混合和删除虚假记录

如果您使用 CursorAdapter，例如SimpleCursorAdapter，onItemClick 和 onItemLongClick 的第 4 个参数是 id.

请注意，要使用 CursorAdapter，在作为 ListView 源的 Cursor 中必须存在名为 _id 的列，并且该列应包含 rowid.您可以使用 BaseColumns._ID，这是一个值为 _id 的常量.

通常您将表中的列定义为 _id INTEGER PRIMARY KEY

或者，您可以使用 rowid AS _id, * 在这种情况下，列 _id 将附加到所有其他列.

可以找到其他适配器的这个和其他选项/解释这里

##工作示例

但是，如果您坚持，那么下面的示例可以满足您的要求.

注意此示例对 2 个表执行相同的操作(有效).

第一个表table1使用AUTOINCREMENT
第二个表 table2 没有 AUTOINCREMENT 编码，但结果是相同的(除了 sqlite_sequence 没有被修改，因为没有必要，因为由于 AUTOINCREMENT 或被编码，sqlite_sequence 中没有行).

主要代码在Database Helper(SQLiteOpenHelper的子类)中，即DBHelper.java :-

公共类 DBHelper 扩展 SQLiteOpenHelper {public static final String DBNAME =mydb"；public static final int DBVERSION = 1;public static final String TBL_TABLE1 =table1"；public static final String TBL_TABLE2 =table2"；public static final String COL_TABLE_COL1 = "col1";public static final String COL_TABLE_COL2 = "col2";public static final String COL_TABLE_COL3 = "col3";private static final String crt_table1_sql =如果不存在则创建表"+ TBL_TABLE1 + "(" +COL_TABLE_COL1 + "整数主键自增，"+COL_TABLE_COL2 + "文本非空，"+COL_TABLE_COL3 + "文本非空"+)";private static final String crt_table2_sql =如果不存在则创建表"+ TBL_TABLE2 + "(" +COL_TABLE_COL1 + "整数主键，"+COL_TABLE_COL2 + "文本非空，"+COL_TABLE_COL3 + "文本非空"+)";SQLiteDatabase mDB；公共 DBHelper(上下文上下文){超级(上下文，DBNAME，空，DBVERSION)；mDB = this.getWritableDatabase();}@覆盖公共无效onConfigure(SQLiteDatabase db){super.onConfigure(db);db.disableWriteAheadLogging();}@覆盖公共无效onCreate(SQLiteDatabase db){db.execSQL(crt_table1_sql);db.execSQL(crt_table2_sql);}@覆盖public void onUpgrade(SQLiteDatabase db, int i, int i1) {}公共无效插入(字符串col2，字符串col3){ContentValues cv = 新的 ContentValues();cv.put(COL_TABLE_COL2,col2);cv.put(COL_TABLE_COL3,col3);mDB.beginTransaction();mDB.insert(TBL_TABLE1,null,cv);mDB.insert(TBL_TABLE2,null,cv);mDB.setTransactionSuccessful();mDB.endTransaction();}public void deleteByValues(String col2, String col3) {字符串 whereclause = COL_TABLE_COL2 + "=?和"+ COL_TABLE_COL3 + "=?";String[] args = new String[]{col2,col3};mDB.beginTransaction();mDB.delete(TBL_TABLE1,whereclause,args);mDB.delete(TBL_TABLE2,whereclause,args);合理化Col1Values();mDB.setTransactionSuccessful();mDB.endTransaction();}私人无效的合理化Col1Values(){ContentValues cv = 新的 ContentValues();Cursor csr = mDB.query(TBL_TABLE1,null,null,null,null,null,COL_TABLE_COL1 + "ASC");int rowcount = csr.getCount();长预期 ID = 1;长 current_id;String where_clause = COL_TABLE_COL1 + "=?";字符串[] args = 新字符串[1];而 (csr.moveToNext()) {current_id = csr.getLong(csr.getColumnIndex(COL_TABLE_COL1));如果(current_id ！= expected_id){cv.clear();cv.put(COL_TABLE_COL1,expected_id);args[0] = String.valueOf(current_id);mDB.update(TBL_TABLE1,cv,where_clause,args);mDB.update(TBL_TABLE2,cv,where_clause,args);}预期 ID++；}csr.close();//现在调整sqlite_sequencewhere_clause = "name=?";args[0] = TBL_TABLE1;cv.clear();cv.put(seq",String.valueOf(rowcount));mDB.update(sqlite_sequence",cv,where_clause,args);}public void logTableRows(int stage) {String tablenamne_column = "tablename";光标[] csr = 新光标[] {mDB.query(TBL_TABLE1,new String[]{"'table1' AS " + tablenamne_column + ",*"},null,null,null,null,null),mDB.query(TBL_TABLE2,new String[]{"'table2' AS &" + tablenamne_column + ",*"}, null,null,null,null,null)};MergeCursor csr3 = new MergeCursor(csr);StringBuilder sb = new StringBuilder("两个表中的数据由" + String.valueOf(csr3.getCount()) +"行:-");而 (csr3.moveToNext()) {sb.append("\n\tTableName = "+ csr3.getString(csr3.getColumnIndex(tablenamne_column))).append(" " + COL_TABLE_COL1 + " 值是 " + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL1))).append(" " + COL_TABLE_COL2 + " 值是 " + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL2))).append("" + COL_TABLE_COL3 + "值是" + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL3)));}Log.d("DATA4STAGE" + String.valueOf(stage),sb.toString());}}

rowid(因此别名为 COL1)的核心操作由 rationaliseCol1Values() 方法进行.这本身作为 deleteByValues 方法的一部分被调用.
- 如果有根据 id 又名 COL1 删除的方法，那么这也会调用 rationaliseCol1Values() 方法).
logTableRows 只是存在以便可以将表输出到日志中.

用于测试上述的调用活动是:-

公共类 MainActivity 扩展 AppCompatActivity {DBHelper mDBHlpr;@覆盖protected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);mDBHlpr = 新的 DBHelper(this);操作一些数据()；}私有无效操作一些数据(){mDBHlpr.logTableRows(0);mDBHlpr.insert(TEST001",TESTING001");mDBHlpr.insert(TEST002",TESTING001");mDBHlpr.insert(TEST003",TESTING001");mDBHlpr.insert(TEST004",TESTING001");mDBHlpr.insert(TEST005",TESTING001");mDBHlpr.insert(TEST006",TESTING001");mDBHlpr.insert(TEST007",TESTING001");mDBHlpr.insert(TEST008",TESTING001");mDBHlpr.insert(TEST009",TESTING001");mDBHlpr.logTableRows(1);mDBHlpr.deleteByValues(TEST005",TESTING001");mDBHlpr.logTableRows(2);mDBHlpr.deleteByValues(TEST008",TESTING001");mDBHlpr.logTableRows(3);mDBHlpr.deleteByValues(TEST003",TESTIN001");mDBHlpr.logTableRows(4);}}

这个:-
1. 列出行(第一次运行时没有).
2. 添加 9 行，其中 COL2 是唯一的(同样仅在第一次运行时).
3. 列出所有 9 行(第一次运行).
4. 删除具有值TEST005"的任何行；在 COL2 ANDTESTING001"中在 COL3 中.
5. 列出行(注意 COL1 的序列是如何维护的).
6. 删除具有值TEST008"的任何行；在 COL2 ANDTESTING001"中在 COL3 中.

列出行(注意 COL1 的序列是如何维护的).
删除具有值TEST003"的任何行；在 COL2 ANDTESTIN001"中在 COL3 中.由于打字错误(缺少 G)而没有做
列出行(注意 COL1 的序列是如何维护的).

输出到日志的结果是:-

2018-12-31 12:43:21.618 2269-2269/so53976714.so53976714.so53976714 D/DATA4STAGE0:两个表中的数据均由0行组成:-2018-12-31 12:43:21.657 2269-2269/so53976714.so53976714 D/DATA4STAGE1:两个表中的数据均由 18 行组成:-TableName = table1 col1 值为 1 col2 值为 TEST001 col3 值为 TESTING001TableName = table1 col1 值为 2 col2 值为 TEST002 col3 值为 TESTING001TableName = table1 col1 值为 3 col2 值为 TEST003 col3 值为 TESTING001TableName = table1 col1 值为 4 col2 值为 TEST004 col3 值为 TESTING001TableName = table1 col1 值为 5 col2 值为 TEST005 col3 值为 TESTING001TableName = table1 col1 值为 6 col2 值为 TEST006 col3 值为 TESTING001TableName = table1 col1 值为 7 col2 值为 TEST007 col3 值为 TESTING001TableName = table1 col1 值为 8 col2 值为 TEST008 col3 值为 TESTING001TableName = table1 col1 值为 9 col2 值为 TEST009 col3 值为 TESTING001TableName = table2 col1 值为 1 col2 值为 TEST001 col3 值为 TESTING001TableName = table2 col1 值为 2 col2 值为 TEST002 col3 值为 TESTING001TableName = table2 col1 值为 3 col2 值为 TEST003 col3 值为 TESTING001TableName = table2 col1 值为 4 col2 值为 TEST004 col3 值为 TESTING001TableName = table2 col1 值为 5 col2 值为 TEST005 col3 值为 TESTING001TableName = table2 col1 值为 6 col2 值为 TEST006 col3 值为 TESTING001TableName = table2 col1 值为 7 col2 值为 TEST007 col3 值为 TESTING001TableName = table2 col1 值为 8 col2 值为 TEST008 col3 值为 TESTING001TableName = table2 col1 值为 9 col2 值为 TEST009 col3 值为 TESTING0012018-12-31 12:43:21.666 2269-2269/so53976714.so53976714 D/DATA4STAGE2:两个表中的数据均由 16 行组成:-TableName = table1 col1 值为 1 col2 值为 TEST001 col3 值为 TESTING001TableName = table1 col1 值为 2 col2 值为 TEST002 col3 值为 TESTING001TableName = table1 col1 值为 3 col2 值为 TEST003 col3 值为 TESTING001TableName = table1 col1 值为 4 col2 值为 TEST004 col3 值为 TESTING001TableName = table1 col1 值为 5 col2 值为 TEST006 col3 值为 TESTING001TableName = table1 col1 值为 6 col2 值为 TEST007 col3 值为 TESTING001TableName = table1 col1 值为 7 col2 值为 TEST008 col3 值为 TESTING001TableName = table1 col1 值为 8 col2 值为 TEST009 col3 值为 TESTING001TableName = table2 col1 值为 1 col2 值为 TEST001 col3 值为 TESTING001TableName = table2 col1 值为 2 col2 值为 TEST002 col3 值为 TESTING001TableName = table2 col1 值为 3 col2 值为 TEST003 col3 值为 TESTING001TableName = table2 col1 值为 4 col2 值为 TEST004 col3 值为 TESTING001TableName = table2 col1 值为 5 col2 值为 TEST006 col3 值为 TESTING001TableName = table2 col1 值为 6 col2 值为 TEST007 col3 值为 TESTING001TableName = table2 col1 值为 7 col2 值为 TEST008 col3 值为 TESTING001TableName = table2 col1 值为 8 col2 值为 TEST009 col3 值为 TESTING0012018-12-31 12:43:21.675 2269-2269/so53976714.so53976714 D/DATA4STAGE3:两个表中的数据均由 14 行组成:-TableName = table1 col1 值为 1 col2 值为 TEST001 col3 值为 TESTING001TableName = table1 col1 值为 2 col2 值为 TEST002 col3 值为 TESTING001TableName = table1 col1 值为 3 col2 值为 TEST003 col3 值为 TESTING001TableName = table1 col1 值为 4 col2 值为 TEST004 col3 值为 TESTING001TableName = table1 col1 值为 5 col2 值为 TEST006 col3 值为 TESTING001TableName = table1 col1 值为 6 col2 值为 TEST007 col3 值为 TESTING001TableName = table1 col1 值为 7 col2 值为 TEST009 col3 值为 TESTING001TableName = table2 col1 值为 1 col2 值为 TEST001 col3 值为 TESTING001TableName = table2 col1 值为 2 col2 值为 TEST002 col3 值为 TESTING001TableName = table2 col1 值为 3 col2 值为 TEST003 col3 值为 TESTING001TableName = table2 col1 值为 4 col2 值为 TEST004 col3 值为 TESTING001TableName = table2 col1 值为 5 col2 值为 TEST006 col3 值为 TESTING001TableName = table2 col1 值为 6 col2 值为 TEST007 col3 值为 TESTING001TableName = table2 col1 值为 7 col2 值为 TEST009 col3 值为 TESTING0012018-12-31 12:43:21.681 2269-2269/so53976714.so53976714 D/DATA4STAGE4:两个表中的数据均由 14 行组成:-TableName = table1 col1 值为 1 col2 值为 TEST001 col3 值为 TESTING001TableName = table1 col1 值为 2 col2 值为 TEST002 col3 值为 TESTING001TableName = table1 col1 值为 3 col2 值为 TEST003 col3 值为 TESTING001TableName = table1 col1 值为 4 col2 值为 TEST004 col3 值为 TESTING001TableName = table1 col1 值为 5 col2 值为 TEST006 col3 值为 TESTING001TableName = table1 col1 值为 6 col2 值为 TEST007 col3 值为 TESTING001TableName = table1 col1 值为 7 col2 值为 TEST009 col3 值为 TESTING001TableName = table2 col1 值为 1 col2 值为 TEST001 col3 值为 TESTING001TableName = table2 col1 值为 2 col2 值为 TEST002 col3 值为 TESTING001TableName = table2 col1 值为 3 col2 值为 TEST003 col3 值为 TESTING001TableName = table2 col1 值为 4 col2 值为 TEST004 col3 值为 TESTING001TableName = table2 col1 值为 5 col2 值为 TEST006 col3 值为 TESTING001TableName = table2 col1 值为 6 col2 值为 TEST007 col3 值为 TESTING001TableName = table2 col1 值为 7 col2 值为 TEST009 col3 值为 TESTING001

#NOTE 不推荐使用上面的

db.execSQL("CREATE TABLE " +DBTable0+ "("+ROW2+" INTEGER PRIMARY KEY AUTOINCREMENT, "+ROW3+" VARCHAR NOT NULL, "+ROW0+" TEXT NOT NULL, "+ROW1+" VARCHAR NOT NULL)");

Here is my datebase's create table command.

I want to reset ROW2's values.

For Example i have 5 record on this table.

1, save1, This is a Save1, 11.25

2, save2, This is a Save2, 23.48

3, save3, This is a Save3, 09.45

4, save4, This is a Save4, 11.55

5, save5, This is a Save5, 21.00

I want to delete save2. When i delete it or other delete i want to reset ROW2 indexes, so

1, save1, This is a Save1, 11.25

2, save3, This is a Save3, 09.45

3, save4, This is a Save4, 11.55

4, save5, This is a Save5, 21.00

Like this ...

How i can do it .?

解决方案

To reset the rowid assigned to compensate for deleted row's when AUTOINCREMENT is used you have to make two changes:-

You would have to alter (UPDATE) the rowid or an alias of the rowid of each row that is out of sequence to following the new sequence and
then you would have to alter (UPDATE) the respective row in the system table sqlite_sequence to the highest rowid allocated.

This is not at all advisable and will greatly compound the inefficiencies of using AUTOINCREMENT (when there is a very good chance that you do not need to use AUTOINCREMENT).

SQLite Autoincrement includes
The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

In fact it is inadvisable to ever rely upon the rowid being anything other than what SQlite assigns

(<column_name> INTEGER PRIMARY KEY (with or without AUTOINCREMENT) makes <column_name> and alias of the rowid).

###You say :-

I want to reset ROW2's values.

I would suggest re-evaluating your want to only wanting this if there is in fact a need, which in all likliehood, there isn't.

###You then say

I'll select the records via ListViews. Then i can delete and update the records on i selected item. So when i delete any records in the middless. the id's are mixing and deleting false records

If you use a CursorAdapter, e.g. SimpleCursorAdapter, the 4th parameter of onItemClick and onItemLongClick is the id.

Noting that to use a CursorAdapter a column named _id must be present in the Cursor that is the source of the ListView and that column should contain the rowid. You can use BaseColumns._ID, which is a constant that has the value _id.

Generally you'd define the column in the table to be _id INTEGER PRIMARY KEY

Alternately you could use rowid AS _id, * in which case the column _id would be additional to all the other columns.

this and other options/explantions for other adapters can be found here

##Working Example

However, if you insist then the following example does what I believe you are asking.

Note this example does the same (effectively) for 2 tables.

The first table table1 uses AUTOINCREMENT
The second table table2 doesn't have AUTOINCREMENT coded but the results are identical (other than sqlite_sequence is not amended as there is no need as there is no row in sqlite_sequence due to AUTOINCREMENT nor being coded).

The main code is in the Database Helper (subclass of SQLiteOpenHelper) namely DBHelper.java :-

public class DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;

    public static final String TBL_TABLE1 = "table1";
    public static final String TBL_TABLE2 = "table2";
    public static final String COL_TABLE_COL1 = "col1";
    public static final String COL_TABLE_COL2 = "col2";
    public static final String COL_TABLE_COL3 = "col3";

    private static final String crt_table1_sql = "CREATE TABLE IF NOT EXISTS " + TBL_TABLE1 + "(" +
            COL_TABLE_COL1 + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            COL_TABLE_COL2 + " TEXT NOT NULL," +
            COL_TABLE_COL3 + " TEXT NOT NULL" +
            ")";
    private static final String crt_table2_sql = "CREATE TABLE IF NOT EXISTS " + TBL_TABLE2 + "(" +
            COL_TABLE_COL1 + " INTEGER PRIMARY KEY," +
            COL_TABLE_COL2 + " TEXT NOT NULL," +
            COL_TABLE_COL3 + " TEXT NOT NULL" +
            ")";

    SQLiteDatabase mDB;

    public DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.disableWriteAheadLogging();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(crt_table1_sql);
        db.execSQL(crt_table2_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public void insert(String col2, String col3) {
        ContentValues cv = new ContentValues();
        cv.put(COL_TABLE_COL2,col2);
        cv.put(COL_TABLE_COL3,col3);
        mDB.beginTransaction();
        mDB.insert(TBL_TABLE1,null,cv);
        mDB.insert(TBL_TABLE2,null,cv);
        mDB.setTransactionSuccessful();
        mDB.endTransaction();
    }

    public void deleteByValues(String col2, String col3) {
        String whereclause = COL_TABLE_COL2 + "=? AND " + COL_TABLE_COL3 + "=?";
        String[] args = new String[]{col2,col3};
        mDB.beginTransaction();
        mDB.delete(TBL_TABLE1,whereclause,args);
        mDB.delete(TBL_TABLE2,whereclause,args);
        rationaliseCol1Values();
        mDB.setTransactionSuccessful();
        mDB.endTransaction();
    }

    private void rationaliseCol1Values() {
        ContentValues cv = new ContentValues();
        Cursor csr = mDB.query(TBL_TABLE1,null,null,null,null,null,COL_TABLE_COL1 + " ASC");

        int rowcount = csr.getCount();
        long expected_id = 1;
        long current_id;
        String where_clause = COL_TABLE_COL1 + "=?";
        String[] args = new String[1];

        while (csr.moveToNext()) {
            current_id = csr.getLong(csr.getColumnIndex(COL_TABLE_COL1));
            if (current_id != expected_id) {
                cv.clear();
                cv.put(COL_TABLE_COL1,expected_id);
                args[0] = String.valueOf(current_id);
                mDB.update(TBL_TABLE1,cv,where_clause,args);
                mDB.update(TBL_TABLE2,cv,where_clause,args);
            }
            expected_id++;
        }
        csr.close();
        // Now adjust sqlite_sequence
        where_clause = "name=?";
        args[0] = TBL_TABLE1;
        cv.clear();
        cv.put("seq",String.valueOf(rowcount));
        mDB.update("sqlite_sequence",cv,where_clause,args);
    }

    public void logTableRows(int stage) {

        String tablenamne_column = "tablename";    
        Cursor[] csr = new Cursor[] {
                mDB.query(TBL_TABLE1,new String[]{"'table1' AS " + tablenamne_column + ",*"},null,null,null,null,null),
                mDB.query(TBL_TABLE2,new String[]{"'table2' AS " + tablenamne_column + ",*"}, null,null,null,null,null)
        };
        MergeCursor csr3 = new MergeCursor(csr);
        StringBuilder sb = new StringBuilder("Data in both tables consists of " + String.valueOf(csr3.getCount()) + " rows :-");

        while (csr3.moveToNext()) {
            sb.append(
                    "\n\tTableName = " + csr3.getString(csr3.getColumnIndex(tablenamne_column)
                    )
            ).append(" " + COL_TABLE_COL1 + " value is " + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL1))
            ).append(" " + COL_TABLE_COL2 + " value is " + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL2))
            ).append(" " + COL_TABLE_COL3 + " value is" + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL3))
            );
        }
        Log.d("DATA4STAGE" + String.valueOf(stage),sb.toString());
    }
}

The core manipulation of the rowid (alias therefore i.e. COL1) is undertaken by the rationaliseCol1Values() method. This itself is invoked as part of the deleteByValues method.
- if there were a method to delete according to id aka COL1 then this too would invoke the rationaliseCol1Values() method).
The logTableRows just exists so that the tables can be output to the log.

The invoking activity used for testing the above was :-

public class MainActivity extends AppCompatActivity {

    DBHelper mDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBHlpr = new DBHelper(this);
        manipulateSomeData();
    }

    private void manipulateSomeData() {
        mDBHlpr.logTableRows(0);
        mDBHlpr.insert("TEST001","TESTING001");
        mDBHlpr.insert("TEST002","TESTING001");
        mDBHlpr.insert("TEST003","TESTING001");
        mDBHlpr.insert("TEST004","TESTING001");
        mDBHlpr.insert("TEST005","TESTING001");
        mDBHlpr.insert("TEST006","TESTING001");
        mDBHlpr.insert("TEST007","TESTING001");
        mDBHlpr.insert("TEST008","TESTING001");
        mDBHlpr.insert("TEST009","TESTING001");
        mDBHlpr.logTableRows(1);

        mDBHlpr.deleteByValues("TEST005","TESTING001");
        mDBHlpr.logTableRows(2);
        mDBHlpr.deleteByValues("TEST008","TESTING001");
        mDBHlpr.logTableRows(3);
        mDBHlpr.deleteByValues("TEST003","TESTIN001");
        mDBHlpr.logTableRows(4);
    }
}

This :-
1. lists the rows (none when first run).
2. Adds 9 rows with COL2 being unique (again only when first run).
3. list all the 9 rows (1st run).
4. deletes any rows that have values "TEST005" in COL2 AND "TESTING001" in COL3.
5. lists the rows (note how COL1's sequence is maintained).
6. deletes any rows that have values "TEST008" in COL2 AND "TESTING001" in COL3.

lists the rows (note how COL1's sequence is maintained).
deletes any rows that have values "TEST003" in COL2 AND "TESTIN001" in COL3. None do due to the typo (missing G)
lists the rows (note how COL1's sequence is maintained).

The Results output to the log being :-

2018-12-31 12:43:21.618 2269-2269/so53976714.so53976714 D/DATA4STAGE0: Data in both tables consists of 0 rows :-
2018-12-31 12:43:21.657 2269-2269/so53976714.so53976714 D/DATA4STAGE1: Data in both tables consists of 18 rows :-
        TableName = table1 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table1 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table1 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table1 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table1 col1 value is 5 col2 value is TEST005 col3 value isTESTING001
        TableName = table1 col1 value is 6 col2 value is TEST006 col3 value isTESTING001
        TableName = table1 col1 value is 7 col2 value is TEST007 col3 value isTESTING001
        TableName = table1 col1 value is 8 col2 value is TEST008 col3 value isTESTING001
        TableName = table1 col1 value is 9 col2 value is TEST009 col3 value isTESTING001
        TableName = table2 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table2 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table2 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table2 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table2 col1 value is 5 col2 value is TEST005 col3 value isTESTING001
        TableName = table2 col1 value is 6 col2 value is TEST006 col3 value isTESTING001
        TableName = table2 col1 value is 7 col2 value is TEST007 col3 value isTESTING001
        TableName = table2 col1 value is 8 col2 value is TEST008 col3 value isTESTING001
        TableName = table2 col1 value is 9 col2 value is TEST009 col3 value isTESTING001
2018-12-31 12:43:21.666 2269-2269/so53976714.so53976714 D/DATA4STAGE2: Data in both tables consists of 16 rows :-
        TableName = table1 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table1 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table1 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table1 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table1 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table1 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table1 col1 value is 7 col2 value is TEST008 col3 value isTESTING001
        TableName = table1 col1 value is 8 col2 value is TEST009 col3 value isTESTING001
        TableName = table2 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table2 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table2 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table2 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table2 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table2 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table2 col1 value is 7 col2 value is TEST008 col3 value isTESTING001
        TableName = table2 col1 value is 8 col2 value is TEST009 col3 value isTESTING001
2018-12-31 12:43:21.675 2269-2269/so53976714.so53976714 D/DATA4STAGE3: Data in both tables consists of 14 rows :-
        TableName = table1 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table1 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table1 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table1 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table1 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table1 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table1 col1 value is 7 col2 value is TEST009 col3 value isTESTING001
        TableName = table2 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table2 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table2 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table2 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table2 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table2 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table2 col1 value is 7 col2 value is TEST009 col3 value isTESTING001
2018-12-31 12:43:21.681 2269-2269/so53976714.so53976714 D/DATA4STAGE4: Data in both tables consists of 14 rows :-
        TableName = table1 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table1 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table1 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table1 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table1 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table1 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table1 col1 value is 7 col2 value is TEST009 col3 value isTESTING001
        TableName = table2 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table2 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table2 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table2 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table2 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table2 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table2 col1 value is 7 col2 value is TEST009 col3 value isTESTING001

#NOTE using the above is NOT recommended

这篇关于Android Studio Sqllite 自动增量重置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

Android Studio Sqllite 自动增量重置 [英] Android Studio Sqllite autoincrement reset

问题描述

相关文章

Java开发最新文章

热门教程

热门工具

登录关闭

Android Studio Sqllite 自动增量重置 [英] Android Studio Sqllite autoincrement reset

问题描述

相关文章

Java开发最新文章

热门教程

热门工具

登录 关闭

登录关闭