How to get max value from SQLite table column and return it to variable in MainActivity. Android Studio

Question

1. Created the table in DBHelper.java:

   @Override
   public void onCreate(SQLiteDatabase db) {
   db.execSQL("create table " + TABLE_WORKOUT + "("
           + KEY_ID + " INTEGER PRIMARY KEY,"
           + KEY_WONUMBER + " integer,"
           + KEY_WONAME + " text,"
           + KEY_WODESCRIPTION + " text,"
           + KEY_WOICON + " text,"
           + KEY_WOCOLOR + " text" + ")");}
   

2. In MainActivity.java onCreate will be created some OBJECTS, that will be shown according to numbers from column KEY_WONUMBER of TABLE_WORKOUT

3. Need to find max value in KEY_WONUMBER and give it to variable in MainActivity.java and set next number for the next OBJECT

4. Trying to do it like this:

       public String getMaxNumber(){
       SQLiteDatabase db = dbHelper.getWritableDatabase();
       Cursor c = db.query(DBHelper.TABLE_WORKOUT, new String[]
               {"MAX("+DBHelper.KEY_WONUMBER+")"},
               null, null, null, null, null);
   
       StringBuffer buffer;
       if(c.getCount()>0){
           c.moveToFirst();
           String max_id=c.getString(0);
           buffer.append(max_id);
       }
   
       c.close();
       db.close();
       return buffer.toString();
   }
   

5. Getting next mistakes in raws:

   public String getMaxNumber(){ - ';' expected

   return buffer.toString(); - *cannot return a value from a method with void result type

As a newbie in Android need your help to solve that problem

Answer 1

public String getMaxNumber(){ - ';' expected

You are missing a closing }. However fixing that then introduces buffer may not have been initialized error as you just have StringBuffer buffer; So attempting to append to it would result in a null pointer exception. You need to initialise buffer e.g. by using StringBuffer buffer = new StringBuffer("");

return buffer.toString(); - *cannot return a value from a method with void result type

This is a result of the missing closing }. see above how this error is replaced by another.

Fixes

Consider the following code and the comments therein :-

public class MainActivity extends AppCompatActivity {

    DBHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbHelper = new DBHelper(this);
        Log.d("MAXNUMBER", getMaxNumber());
    } //<<<<<<<<<< FIX 1 closes the onCreate method

    public String getMaxNumber () {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        Cursor c = db.query(DBHelper.TABLE_WORKOUT, new String[]
                        {"MAX(" + DBHelper.KEY_WONUMBER + ")"},
                //{"COALESCE(MAX(" + DBHelper.KEY_WONUMBER + "),0)"}, //Alternative will return 0 instead of null if no rows

        null, null, null, null, null);
        StringBuffer buffer = new StringBuffer(""); //FIX2 initialize buffer by adding buffer = new StringBuffer("")
        if (c.moveToFirst()) { // moveToFirst will return false if no row to move to so no need to check row count
            String max_id = c.getString(0);
            buffer.append(max_id); //FIX 1 introduces might not have been inittialized (buffer hasn't been inittialized)
        }
        c.close();
        db.close(); // can be inefficient to keep on opening and closing the database
        return buffer.toString();
    }
}

This returns (when no rows have been inserted) :-

D/MAXNUMBER: null

• see commented out query which uses COALESCE to return 0 rather than null in the returned string if you prefer to not have to handle null.