Get generated id after insert

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.

Answers


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.

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;

}

Need Your Help

How do I listen for changes to a RadioGroup's selected value using JQuery?

jquery radio-button onchange radio-group

I need to register a handler for a group of radio buttons. I'm using JQuery and hoped that its .change method would accomplish this. However I have not experienced the desired behavoir.