Get last inserted value from sqlite database Android

Question

I am trying to get the last inserted rowid from a sqlite database in Android. I have read a lot of posts about it, but can't get one to work. This is my method:

 public Cursor getLastId() {
        return mDb.query(DATABASE_TABLE, new String[] {KEY_WID}, KEY_WID + "=" + MAX(_id), null, null, null, null, null);}

I have tried with MAX, but I must be using it wrong. Is there another way?

Galvin

The insert method returns the rowId or -1 #5410251 – Dicta

see https://mcmap.net/q/121735/-get-generated-id-after-insert – Vera

Answer 1

Well actually the SQLiteDatabase class has its own insert method which returns the id of the newly created row. I think this is the best way to get the new ID. You can check its documentation here.

I hope this helps.

Endocranium

+1 thanks...just straight it is...killed two bird with one stone.. :) – Pilgrimage

So row ID for Android is the generated key, not the ROWID has SQL use to identify a row .... go to know. – Locomotive

Answer 2

Use

 SELECT last_insert_rowid();

to get the last inserted rowid. If you are using AUTOINCREMENT keyword then

SELECT * from SQLITE_SEQUENCE;

will tell you the values for every table.

Elva

I have a class DataHelper where I have all methods that deal with the database. I have seem these methods above like this in many forums and the documentation, but I don't understand how they are used. Do you use them inside the DataHelper class, or inside the actual Sqlite from the terminal? Sorry if i am vague, I am just trying to figure this out! – Galvin

you can do a rawQuery with the Android SQLiteDatabase and feed it the string "Select * from SQLITE_SEQUENCE" – Bartholomeo

Answer 3

To get the last row from the table..

Cursor cursor = theDatabase.query(DATABASE_TABLE, columns,null, null, null, null, null);
cursor.moveToLast();

Iota

This seems to me like the easiest and of faster performance answer. – Hawsehole

Answer 4

Use moveToLast() in Cursor interface.

From android.googlesource.com

/**
 * Move the cursor to the last row.
 *
 * <p>This method will return false if the cursor is empty.
 *
 * @return whether the move succeeded.
 */
boolean moveToLast();

Simple example:

final static String TABLE_NAME = "table_name";
String name;
int id;
//....

Cursor cursor = db.rawQuery("SELECT  * FROM " + TABLE_NAME, null);

if(cursor.moveToLast()){
    //name = cursor.getString(column_index);//to get other values
    id = cursor.getInt(0);//to get id, 0 is the column index
}

Or you can get the last row when insertion(Which is @GorgiRankovski have mentioned):

long row = 0;//to get last row
//.....
SQLiteDatabase db= this.getWritableDatabase();

ContentValues contentValues = new ContentValues();
contentValues.put(COLUMN_NAME, name);

row = db.insert(TABLE_NAME, null, contentValues);
//insert() returns the row ID of the newly inserted row, or -1 if an error occurred 

Also their is a multiple ways you can do this using query:

1. One is expressed by @DiegoTorresMilano
2. SELECT MAX(id) FROM table_name. or to get all columns values SELECT * FROM table_name WHERE id = (SELECT MAX(id) FROM table_name).
3. If your PRiMARY KEY have sat to AUTOINCREMENT, you can SELECT vaules occording to max to min and limit the rows to 1 using SELECT id FROM table ORDER BY column DESC LIMIT 1 (If you want each and every value, use * instead of id)

Dimetric

Answer 5

If you want the last_insert_id just afert a insert you can use that :

public long insert(String table, String[] fields, String[] vals ) 
{
    String nullColumnHack = null;
    ContentValues values = new ContentValues();
    for (int i = 0; i < fields.length; i++) 
    {
        values.put(fields[i], vals[i]); 
    }

    return myDataBase.insert(table, nullColumnHack, values);
}

Strife

Is that correct ? I'm not up to the specifics so I ask. The documentation states that the row_id is returned. As far as I udnerstand it, the sqlite row_id is not exactly the same as the value in the autoincrement column or am I wrong about that ? – Aixenprovence

Answer 6

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

long id = db.insert("your insertion statement");

db is an instance of your SQLiteDatabase.

Hospodar

Answer 7

Try this:

public Cursor getLastId() {
        return mDb.query(DATABASE_TABLE, new String[] { **MAX(id)** }, null, null, null, null, null, null);}

Maladapted

Answer 8

/**
 * @return
 */
public long getLastInsertId() {
    long index = 0;
    SQLiteDatabase sdb = getReadableDatabase();
    Cursor cursor = sdb.query(
            "sqlite_sequence",
            new String[]{"seq"},
            "name = ?",
            new String[]{TABLENAME},
            null,
            null,
            null,
            null
    );
    if (cursor.moveToFirst()) {
        index = cursor.getLong(cursor.getColumnIndex("seq"));
    }
    cursor.close();
    return index;
}

Headstone

I thought that new additions to the SQLite database are added below existing rows in a column(s). Therefore, the last insert would be the bottom row in a column and therefore, would you want to use "...cursor.moveToLast()..." rather than "...cursor.moveToFirst()..."? – Electrocardiogram

Answer 9

I use this

public int lastId(){
    SQLiteDatabase db =  
  this.getReadableDatabase();
    Cursor res =  db.rawQuery( "select * from resep", null );
    res.moveToLast();
    return res.getInt(0);
}

Burschenschaft

Answer 10

In your DbHelper class,

    public long getLastIdFromMyTable()
    {
        SQLiteDatabase db = this.getReadableDatabase();
        SQLiteStatement st = db.compileStatement("SELECT last_insert_rowid() from " + MY_TABLE);
        return st.simpleQueryForLong();
    }

Charcuterie