How to get max _id value of a SQLite table using Anko with Kotlin?

Question

DBRecordTable._ID is a INTEGER + PRIMARY_KEY+ AUTOINCREMENT, I hope to get the max _id value of a table, zero will be return if no data row in the table.

I try to write the code select(tableName,DBRecordTable._ID).orderBy(DBRecordTable._ID, Desc).limit(1), but it's not correct, how can I fix it?

Code

class DBRecordHandler(private val mDBHelper: DBRecordHelper =DBRecordHelper.instance,
                      private val tableName:String =DBRecordTable.TableNAME,
                      private val idField:String=DBRecordTable._ID
                      ) {

 fun getMaxID():Long=mDBHelper.use{
      var myList=select(tableName,DBRecordTable._ID).orderBy(DBRecordTable._ID, Desc).limit(1); 
 }

}



class DBRecordHelper(mContext: Context = UIApp.instance) : ManagedSQLiteOpenHelper(mContext, DB_NAME, null, DB_VERSION) {

    companion object {
        const val DB_NAME = "record.db"
        const val DB_VERSION = 5
        val instance by lazy { DBRecordHelper() }
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.createTable( DBRecordTable.TableNAME , true,
            DBRecordTable._ID to INTEGER + PRIMARY_KEY+ AUTOINCREMENT,           
            DBRecordTable.CreatedDate to INTEGER
        )
    }   

}

try this https://stackoverflow.com/questions/7064279/android-and-sqlite-retrieve-max-id-from-table/34244876 — PushpikaWan, Nov 18 '18 at 03:10
can you try with select(tableName,DBRecordTable._ID).whereargs(SELECT MAX(DBRecordTable._ID) FROM tableName) — karan, Nov 20 '18 at 05:41

aminography · Accepted Answer · 2018-11-22T07:58:27.050

3

You can find maximum id of the table contents using SQL MAX() function:

private fun getMaxID(): Int {
    var maxId = 0
    mDBHelper?.use {
        select(DBRecordTable.TableNAME, "MAX(${DBRecordTable._ID}) as maxId").exec {
            moveToNext()
            maxId = getInt(getColumnIndex("maxId"))
        }
    }
    return maxId
}

edited Nov 22 '18 at 07:58

answered Nov 21 '18 at 05:49

aminography

21,986
13
70
74

You're welcome. As you are using `ManagedSQLiteOpenHelper`, it is possible to replace `mDBHelper?.writableDatabase?.apply` with `mDBHelper?.use` as you did in question content. I have searched in `anko` source codes (https://github.com/Kotlin/anko/tree/master/anko/library/static/sqlite/src/main/java) and found nothing to do this procedure simpler. – aminography Nov 22 '18 at 08:04

How to get max _id value of a SQLite table using Anko with Kotlin?

1 Answers1