Brian Brian - 4 months ago 41
Android Question

Getting the next AUTO_INCREMENT value of a SQLite database

Using the typical SQLiteDatabase object in Android's API, what can I do to get the next

value of a particular column (ie.
) without affecting the value itself. Is there a method for that? Or what query should I execute to get that result. Keep in mind that SQLiteDatabase.query() returns a
object, so I'm not too sure how to deal with that directly if I just want to get a value out of it.

aha aha

You're right. The first answer (still below) only works without an AUTOINCREMENT for id. With AUTOINCREMENT, the values are stored in a separate table and used for the increment. Here's an example of finding the value:

public void printAutoIncrements(){
    String query = "SELECT * FROM SQLITE_SEQUENCE";
    Cursor cursor = mDb.rawQuery(query, null);
    if (cursor.moveToFirst()){
            System.out.println("tableName: " +cursor.getString(cursor.getColumnIndex("name")));
            System.out.println("autoInc: " + cursor.getString(cursor.getColumnIndex("seq")));

        }while (cursor.moveToNext());




First Answer:

You can query for the max of the _id column, such as:

String query = "SELECT MAX(id) AS max_id FROM mytable";
Cursor cursor = db.rawQuery(query, null);

int id = 0;     
if (cursor.moveToFirst())
        id = cursor.getInt(0);                  
    } while(cursor.moveToNext());           
return id;

This works for row ids that haven't been specified as "INTEGER PRIMARY KEY AUTOINCREMENT" (all tables have a row id column).