How to select the last entry of an SQLIte DB without knowing the id?

user_13418010 0
Viewed 106

I´m currently working with SQLite databases on android. i have a simple database with 4 columns (id, c1, c2, c3)

i have a methode that returns the last entry of a specific column. I don´t know the id of the entry, but i know its always the most recent one.

currently i´m doing it like this:

 public int Select(String column){
    Log.i(TAG, column +" request");
    SQLiteDatabase db = this.getReadableDatabase();


    Cursor cs = db.query(table, new String[]{column,null,null,null},null,null,null,null,null);
    if(cs!=null && cs.moveToFirst()){
        cs.moveToLast();
        return Integer.parseInt(cs.getString(cs.getColumnIndex(column)));
    }
}

at runtime, cs is always null and i can´t figure out why. What am I doing wrong?

thanks in advance

android-sqlite sqlite android java
1 Answers

If you want to get the last entry in the table then you must have a column that indicates the order of the insertions of the rows, say a datetime column like created_at and then you could do:

SELECT columnname FROM tablename ORDER BY created_at DESC LIMIT 1

If there isn't such a column, then you could use the column id but only if you have defined it as INTEGER PRIMARY KEY AUTOINCREMENT, (even without the keyword AUTOINCREMENT you can't be sure of the order because ids may be reused after deletions and insertions):

SELECT columnname FROM tablename ORDER BY id DESC LIMIT 1

So your code must be:

public Integer select(String columnName) {
    SQLiteDatabase db = this.getReadableDatabase();
    String sql = "SELECT " + columnName + " FROM " + tableName + " ORDER BY id DESC LIMIT 1";
    Cursor cs = db.rawQuery(sql, null);
    Integer result = null;
    if(cs.moveToFirst()) {
        try {
            result = cs.getInt(0); // the query returns only 1 column so it is safe to use its index
        } catch (Exception e) {
            Log.i(TAG, columnName + " Invalid integer value");
        } 
    }
    cs.close();
    db.close();
    return result;
}

You can use the above method like:

Integer value = select("yourColumnName");

and it will return the last entry or null if the table is empty or the value returned is not a valid integer.

I use Integer as the return type of the method select() because you use it too.
If the column has a different data type you must change it accordingly.

user_104988280
Related