Get generated id after insert

Question

The insert method returns the id of row just inserted or -1 if there was an error during insertion.

long id = db.insert(...);

where db is SQLiteDatabase.

If use ContentValues :

 DBHelper db =new DBHelper();// your dbHelper
 ContentValues values = new ContentValues();
  values.put("firstName","Ahmad");
 values.put("lastName","Aghazadeh");
 long insertedId= db.getSQLiteDatabase().insert("user", "", values) ;

If query exec use select last_insert_rowid()

String sql = "INSERT INTO [user](firstName,lastName) VALUES (\"Ahmad\",\"Aghazadeh\"); select last_insert_rowid()";
 DBHelper itemType =new DBHelper();// your dbHelper
 c = db.rawQuery(sql, null);
 if (c.moveToFirst())
    result = c.getLong(0);

If use Room

@Entity
class User {
    @PrimaryKey(autoGenerate = true)
    public int id;
    //...
}


@Dao
public interface UserDao{
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    long insert(User user);

    // Insert multiple users
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    long[] insert(User... user);
}

I checked the sources. insert method use sqlite3_last_insert_rowid function to return an id. According to the documentation: https://www.sqlite.org/c3ref/last_insert_rowid.html The row id is the hidden column or a column of type INTEGER PRIMARY KEY if it's declared.

Each entry in most SQLite tables (except for WITHOUT ROWID tables) has a unique 64-bit signed integer key called the "rowid". The rowid is always available as an undeclared column named ROWID, OID, or _ROWID_ as long as those names are not also used by explicitly declared columns. If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.

So that is the default _ID column usually

I had quite a bit of problems with this on mySQL, the LAST_INSERT_ID is not reliable way to get the ID, if you have users hammering the database, the ID returned may not be the ID that was inserted by the query you have run, several other users might impact on the return of this id. We had server with average of 7000 users a minute hammering away and it always stumbled.

The solution we had was to use data from the query you inserted, and then search for that result using that data. You are doing a request looking for last id anyway. So you might as well do a SELECT id FROM table where field=var and field=var to get the id. Its a slight performance hit on the query, but a much more reliable result returned.

One can simply get last inserted row _id using last_insert_rowid(). Sample code is as below.

/**
 * Return Last inserted row id(auto incremented row) (_id)
 * @return
 */
public int getLastAddedRowId() {
    String queryLastRowInserted = "select last_insert_rowid()";

    final Cursor cursor = database.rawQuery(queryLastRowInserted, null);
    int _idLastInsertedRow = 0;
    if (cursor != null) {
        try {
            if (cursor.moveToFirst()) {
                _idLastInsertedRow = cursor.getInt(0);
            }
        } finally {
            cursor.close();
        }
    }

    return _idLastInsertedRow;

}