Get generated id after insert

Question

I'm using the SQLite with Android, and I want to know the best way to get the generated id of the row I inserted.

A solution I think makes a search after include, but it doesn't look the best way.

Elana

Answer 1

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.

Murderous

I read on specs. "Returns: the row ID of the newly inserted row, or -1 if an error occurred" the rowId is the same as my generated field "id primary key autoincrement" ? – Elana

@GrAnd, but what if I'll delete some "start-middle" rows in my table, so I break the sequence of n-th rows with generated id=n. Will that returned row ID remain the same as generated autoincrement id? – Martensite

I could not find a answer, what if the long id = -1 value, and what does that error code about what? – Caleb

What if you need to do INSERT OR UPDATE and get the id? – Acalia

@Log.d You should be using insertOrThrow in order for you to gather more information about the error occurred. (I know this is too late but this might help other readers as well) – Lansquenet

@Lansquenet that's not that case. It's was well documented in android – Caleb

@Martensite I know this is old post. But may be helpful for someone. The row id and autoincremented id will be same even if it is deleted from middle. – Prostitute

Answer 2

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);
}

Dorina

Answer 3

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

Cwmbran

Answer 4

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.

Tinaret

This requires your column values for each row be unique (or mostly unique) or risk returning multiple ids. – Fisch

Answer 5

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;

}

Pankey

Rupesh, please see this question. #65140680 – Lookthrough