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 AUTO_INCREMENT value of a particular column (ie. id) without affecting the value itself. Is there a method for that? Or what query should I ex开发者_JS百科ecute to get that result. Keep in mind that SQLiteDatabase.query() returns a Cursor object, so I'm not too sure how to deal with that directly if I just want to get a value out of it.

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()){
        do{
            System.out.println("tableName: " +cursor.getString(cursor.getColumnIndex("name")));
            System.out.println("autoInc: " + cursor.getString(cursor.getColumnIndex("seq")));

        }while (cursor.moveToNext());
    }

    cursor.close(); 

}

See: http://www.sqlite.org/autoinc.html

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())
{
    do
    {           
        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).

This is the best way to get the last ID on auto increment PRIMARY KEY with SQLITE

String query = "select seq from sqlite_sequence WHERE name = 'Table_Name'"

An important remark about the SQLITE_SEQUENCE table.

The documentation says

The SQLITE_SEQUENCE table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created.

So the SQLITE_SEQUENCE table is created, but NOT the row associated with the table that contains the AUTOINCREMENT column. That row is created with the first insert query (with "seq" value of 1).

That means that you must doing at least one insert operation before looking for the next autoincrement value of a specific table. It could be done for example just after the creation of the table, performing an insert and a delete of a dummy row.

Here is what I use to get the next AUTOINCREMENT value for a specific table:

/**
 * Query sqlite_sequence table and search for the AUTOINCREMENT value for <code>tableName</code>
 * @param tableName The table name with which the AUTOINCREMENT value is associated.
 *
 * @return The next AUTOINCREMENT value for <code>tableName</code>
 * If an INSERT call was not previously executed on <code>tableName</code>, the value 1 will
 * be returned. Otherwise, the returned value will be the next AUTOINCREMENT.
 */
private long getNextAutoIncrement(String tableName) {
    /*
     * From the docs:
     * SQLite keeps track of the largest ROWID using an internal table named "sqlite_sequence".
     * The sqlite_sequence table is created and initialized automatically
     * whenever a normal table that contains an AUTOINCREMENT column is created.
     */
    String sqliteSequenceTableName = "sqlite_sequence";
    /*
     * Relevant columns to retrieve from <code>sqliteSequenceTableName</code>
     */
    String[] columns = {"seq"};
    String selection = "name=?";
    String[] selectionArgs = { tableName };

    Cursor cursor = mWritableDB.query(sqliteSequenceTableName, 
            columns, selection, selectionArgs, null, null, null);

    long autoIncrement = 0;

    if (cursor.moveToFirst()) {
        int indexSeq = cursor.getColumnIndex(columns[0]);
        autoIncrement = cursor.getLong(indexSeq);
    }

    cursor.close();

    return autoIncrement + 1;
}

Inside the SQLiteOpenHelper you use, start a transaction. Insert some data and then rollback.

Such a way, you 'll be able to get the next row id, like this:

public long nextId() {
    long rowId = -1;

    SQLiteDatabase db = getWritableDatabase();
    db.beginTransaction();
    try {
        ContentValues values = new ContentValues();
        // fill values ...

        // insert a valid row into your table
        rowId = db.insert(TABLE_NAME, null, values);

        // NOTE: we don't call  db.setTransactionSuccessful()
        // so as to rollback and cancel the last changes
    } finally {
        db.endTransaction();
    }
    return rowId;
}

It's work.

public static long getNextId(SQLiteDatabase db, String tableName) {
    Cursor c = null;
    long seq = 0;
    try {
        String sql = "select seq from sqlite_sequence where name=?";
        c = db.rawQuery(sql, new String[] {tableName});
        if (c.moveToFirst()) {
            seq = c.getLong(0);
        }
    } finally {
        if (c != null) {
            c.close();
        }
    }
    return seq + 1;
}

You can use cursor.getInt(i); method
i here is index of the id column

Cursor c = db.rawQuery("Select * From mSignUp", null);
            String mail = null;
            try {
                while (c.moveToNext()) {
                    mail = c.getString(0);
                    String pas = c.getString(1);
                    Toast.makeText(getApplicationContext(), "Name = " + mail + " Pass = " + pas, Toast.LENGTH_SHORT).show();
                }
            }catch (CursorIndexOutOfBoundsException e){
                Log.e("OutOfBound", Log.getStackTraceString(e));
            }
            finally {
                c.close();
            }