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 execute 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.

  • PhoneStateListener onSignalStrengthsChanged stops being called in Service
  • Using Job Scheduler in Android API <21
  • ProgressDialog and AlertDialog cause leaked window
  • android: determining a symbolic link
  • Google maps api v2 zooming near the marker
  • Detect 'home button pressed' event in android service displaying a UI (similar to facebook chatheads)
  • Related posts:

    Using the new &ldquo;manifestmerger&rdquo; property in Android
    Is it okay to change a ListView&#039;s adapter dynamically?
    How to add a calendar event on an Android device with a given date?
    Actionbar list navigation width - wrap content
    How to dynamically generate the raw resource identifier in android?
    Storing file in Android Database (Custom Content Provider) using _data field - v1.6
  • osmdroid - higher zoom level?
  • Output one character per two keys in Android Keyboard
  • Android emulator still slow as hell even with Intel image, why?
  • ListView does not update until keyboard is hidden
  • Best way for local HTML file to communicate with (read/write) local .xlsm Excel file
  • Google Cloud messaging implementation in Java using MySql
  • 7 Solutions collect form web for “Getting the next AUTO_INCREMENT value of a SQLite database”

    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.

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

    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 equal to the value of the current
     * AUTOINCREMENT + 1.
     */
    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 = {"name", "seq"};
    
        Cursor cursor = mWritableDatabase.query(sqliteSequenceTableName, columns, null, null, null, null, null);
    
        long autoIncrement = 0;
        /*
         * We can indeed fail here ( If an insert call was not previously executed on <code>tableName</code>),
         * but no worries:
         * <code>autoIncrement + 1</code> will be returned either way.
         */
        if (cursor.getCount() > 0) {
            int indexName = cursor.getColumnIndex(columns[0]);
            int indexSeq = cursor.getColumnIndex(columns[1]);
    
            while (cursor.moveToNext()) {
                /*
                 * Are we currently looking at <code>tableName</code> ?
                 */
                if (cursor.getString(indexName).equals(tableName)) {
                    /* Great, take the AUTOINCREMENT value and get out */
                    autoIncrement = cursor.getLong(indexSeq);
                    break;
                }
            }
        }
    
        cursor.close();
    
        return autoIncrement + 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();
                }
    

    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;
    }
    
    Android Babe is a Google Android Fan, All about Android Phones, Android Wear, Android Dev and Android Games Apps and so on.