代码之家 › 专栏 › 技术社区 › MikeT

android-对于没有rowid表,insert-convenience方法返回了什么值?

android-sqlite sqlite android java

MikeT · 技术社区 · 6 年前

注释这个问题的目的是 share your knowledge, Q&A-style .

默认情况下,sqlite创建一个隐藏列,即 罗维德 它将被分配一个唯一的64位有符号整数值。

但是,自从sqlite 3.8.2版以来,sqlite支持 没有罗维德 导致 罗维德 被省略。

假设插入成功,结果会是什么?

即从sqliteDatabase返回的long 插入系列(插入,插入到第行,插入到OnCloct中),依据:

新插入行的行ID,如果发生错误,则为-1

SQLiteDatabase - insert

1 回复 | 直到 6 年前

MikeT 6 年前

因为sqlite版本3.8.2或更高版本仅在API 21 Lollilop之后可用,那么 没有罗维德 只能从API 21上使用。

( 告诫 一些设备制造商在其设备上包含不同版本的sqlite。 android.database.sqlite )

在没有rowid的api 21编码之前,将导致语法错误,因此不可能出现这样的结果,因为这样的表不存在。

从api 21开始,如果插入操作成功,则返回0,否则返回-1。

但是,这只是假设单个表或多个表没有rowid表(即没有rowid表)的简单情况。

将rowid表添加到混合中,这是更可能的情况,然后结果与上次相同,因为数据库已打开,所以设置了一个结果(根据sqlite而不是android sdk)或-1。

简而言之,如果不是-1,则值表示插入了一行(很可能),-1通常表示未插入该行。

告诫如果强制rowid为-1(直接或通过别名为rowid列提供-1),则即使插入了行,也可以返回-1的结果。

例子

下面演示了使用一个不带rowid表的数据库的创建,其中插入两行,并将结果输出到日志。使用sqliteOpenHelper的子类,因为这似乎是访问sqlite数据库的更常见方法。

dbhelpernorowid.java(数据库助手)

public class DBHelperNoRowid extends SQLiteOpenHelper {

    public final static String DB = "test_norowid";
    public static final int VERSION = 1;
    public static final String TBL_NOROWID = "norowid";
    public static final String COL_ID = BaseColumns._ID;
    public static final String COL_MYDATA = "mydata";

    SQLiteDatabase mDB;

    public DBHelperNoRowid(Context context) {
        super(context, DB, null, VERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crt_norowid_table = "CREATE TABLE IF NOT EXISTS " + TBL_NOROWID + "(" +
                COL_ID + " INTEGER PRIMARY KEY, " +
                COL_MYDATA + " TEXT" +
                ") WITHOUT ROWID"; //<<<<<<<<<<
        db.execSQL(crt_norowid_table);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertUsingConvenienceInsert(long not_an_id, String text) {
        ContentValues cv = new ContentValues();
        cv.put(COL_ID,not_an_id);
        cv.put(COL_MYDATA,text);
        return mDB.insert(TBL_NOROWID,null,cv);
    }
}

mainactivity.java语言

public class MainActivity extends AppCompatActivity {

    DBHelperNoRowid mDBhlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBhlpr = new DBHelperNoRowid(this);
        logInsertResult(2100L,"row 1");
        logInsertResult(-898765432,"row 2");
    }

    private void logInsertResult(long not_an_id, String text) {

        Log.d("INSERTRESULT","Insertion of a row returned " + String.valueOf(
                mDBhlpr.insertUsingConvenienceInsert(not_an_id,text)
        ));
    }
}

结果

2019-01-23 17:36:04.029 24175-24175/so.uru D/INSERTRESULT: Insertion of a row returned 0
2019-01-23 17:36:04.029 24175-24175/so.uru D/INSERTRESULT: Insertion of a row returned 0

二次跑

运行上述操作将导致唯一的约束冲突,这将被插入方法,然后返回-1,例如:-

2019-01-23 19:49:51.915 24876-24876/so.uru E/SQLiteDatabase: Error inserting _id=2100 mydata=row 1
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: norowid._id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:796)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1564)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1433)
        at so.uru.DBHelperNoRowid.insertUsingConvenienceInsert(DBHelperNoRowid.java:42)
        at so.uru.MainActivity.logInsertResult(MainActivity.java:23)
        at so.uru.MainActivity.onCreate(MainActivity.java:16)
        at android.app.Activity.performCreate(Activity.java:7136)
        at android.app.Activity.performCreate(Activity.java:7127)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:193)
        at android.app.ActivityThread.main(ActivityThread.java:6669)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
2019-01-23 19:49:51.916 24876-24876/so.uru D/INSERTRESULT: Insertion of a row returned -1
2019-01-23 19:49:51.918 24876-24876/so.uru E/SQLiteDatabase: Error inserting _id=-898765432 mydata=row 2
    android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: norowid._id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:796)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1564)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1433)
        at so.uru.DBHelperNoRowid.insertUsingConvenienceInsert(DBHelperNoRowid.java:42)
        at so.uru.MainActivity.logInsertResult(MainActivity.java:23)
        at so.uru.MainActivity.onCreate(MainActivity.java:17)
        at android.app.Activity.performCreate(Activity.java:7136)
        at android.app.Activity.performCreate(Activity.java:7127)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
        at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
        at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
        at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:193)
        at android.app.ActivityThread.main(ActivityThread.java:6669)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
2019-01-23 19:49:51.918 24876-24876/so.uru D/INSERTRESULT: Insertion of a row returned -1

用于测试rowid和不带表的混合的代码

注:以下只是用于测试的多种排列之一:

dbhelperborowid.java软件

public class DBHelperNoRowid extends SQLiteOpenHelper {

    public final static String DB = "test_norowid";
    public static final int VERSION = 1;
    public static final String TBL_NOROWID = "norowid";
    public static final String COL_ID = BaseColumns._ID;
    public static final String COL_MYDATA = "mydata";
    public static final String TBL_WITHROWID = "withrowid";

    SQLiteDatabase mDB;

    public DBHelperNoRowid(Context context) {
        super(context, DB, null, VERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crt_norowid_table = "CREATE TABLE IF NOT EXISTS " + TBL_NOROWID + "(" +
                COL_ID + " INTEGER PRIMARY KEY, " +
                COL_MYDATA + " TEXT" +
                ") WITHOUT ROWID";
        db.execSQL(crt_norowid_table);
        String crt_withrowid_table = "CREATE TABLE IF NOT EXISTS " + TBL_WITHROWID + "(" +
                COL_ID + " INTEGER PRIMARY KEY, " +
                COL_MYDATA + " TEXT" +
                ")";
        db.execSQL(crt_withrowid_table);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertUsingConvenienceInsert(long not_an_id, String text, boolean without_rowid, boolean generate_rowid) {
        ContentValues cv = new ContentValues();
        String table = TBL_WITHROWID;
        if (without_rowid) {
            table = TBL_NOROWID;
            cv.put(COL_ID,not_an_id);
        } else {
            if (generate_rowid) {
                cv.put(COL_ID,not_an_id);
            }
        }
        cv.put(COL_MYDATA,text);
        return mDB.insert(table,null,cv);
    }

    public void logLastInsertId() {
        Cursor csr = mDB.rawQuery("SELECT last_insert_rowid()",null);
        if (csr.moveToFirst()) {
            Log.d("LASTINSERTEDROWID","The Last RowID inserted was " + String.valueOf(csr.getLong(0)));
        } else {
            Log.d("LASTINSERTEDROWID","Ooops there does not appear to have been a row inserted");
        }
    }
}

mainactivity.java(根据上次使用的排列)

public class MainActivity extends AppCompatActivity {

    DBHelperNoRowid mDBhlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBhlpr = new DBHelperNoRowid(this);
        // Insert some rows into the table that is a rowid table
        logInsertResult(1,"row 1",false,false);
        logInsertResult(1,"row 1",false,false);
        logInsertResult(1,"row 1",false,false);
        logInsertResult(1,"row 1",false,false);
        logInsertResult(-1,"row 1",false,true);

        logInsertResult(2100L,"row 1",true,true); //<<<<<<<<<< WITHOUT

        logInsertResult(1,"row 1",false,false);
        logInsertResult(1,"row 1",false,false);
        logInsertResult(1,"row 1",false,false);

        logInsertResult(-898765432,"row 2",true,true); //<<<<<<<<<< WITHOUT

        logInsertResult(1,"row 1",false,false);
        logInsertResult(1,"row 1",false,false);
        logInsertResult(1,"row 1",false,false);
        logInsertResult(1,"row 1",false,false);

        logInsertResult(-1L,"3rd row",true,true); //<<<<<<<<<< WITHOUT
    }

    private void logInsertResult(long no_an_id, String text, boolean without_rowid, boolean generate_rowid) {

        if (without_rowid) {
            Log.d("INSERTRESULT",">>>>>>>>>> WITHOUT ROWID ATTEMPT");
        }

        Log.d("INSERTRESULT","Insertion of a row returned " + String.valueOf(
                mDBhlpr.insertUsingConvenienceInsert(no_an_id,text,without_rowid,generate_rowid)
        ));
        mDBhlpr.logLastInsertId(); //
    }
}