Android SQLite - Primary Key Desc

Question

First create the id generator:

private int generateId() {
       // if the table is empty returns -1
       int result = -1;
       String sql = "SELECT id FROM table order by id asc";
       SQLiteDatabase db = this.getReadableDatabase();
       Cursor cursor = db.rawQuery(sql, null);
       //if any id is decremented
       if (cursor.moveToFirst()) 
        result = (cursor.getInt(0) -1);
       cursor.close();
       db.close();
       return result;
    }

this will be our initial value we can replace it with another value

 // if the table is empty returns -1
 int result = -1;

---

then use it in the insert:

public void add(Type type) {
        int id = this.generateId();
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("id", id);
        cv.put("value",type.value)
        ...
        db.insert(table, null, cv);
        cv.clear();
        db.close();
    }

Does anyone have another solution?

Answer 1

Opening/closing the database twice is inefficient; and there is a helper to read a single value from a query.

Anyway, you could move the computations into SQL:

db = getWritableDatabase();
try {
    long id = DBUtils.longForQuery(db,
                    "SELECT IFNULL(MIN(id) - 1, 0) FROM MyTable", null);
    cv.put("id", id);
    ...
} finally {
    db.close();
}