Retrieving a single column of last row in sqlite/Android using ORDER ID desc limit 1

I'm attempting to create a method that will allow me to retrieve a value from the last row of a database, and then insert it into an EditText field. (This is a value that the user will not change all that often, so it would be helpful if when they do set it, it stays set when they come back to it).

My method, based on a similar method I have for getting the total of a specific column, is as such:

public String getBase() {
 Cursor mCursor = mDb.rawQuery(
   "SELECT base FROM table constants ORDER ID desc limit 1", null);
 if (mCursor.moveToFirst()) {
  return mCursor.getString(0);
 }
 return mCursor.getString(0);

Like I said, I based this on a similar method, which I found after searching around the Internet. I understand most of it, but I have no idea what the 0's in the return statements mean (or the moveToFirst method).

Anyway, in my OnClickListener (the bu开发者_如何转开发tton the user would press to save this value to db), I have the following (editBase is the EditText field I want to populate):

editBase.setText(cDbHelper.getBase());

If I run the program without this statement, it works fine and the value saves to the db. As soon as I try to run it with this, I get a force close. Any suggestions?

Thanks.

EDIT: Thanks for the responses. With some guidance from a friend, I ended up using "ORDER BY... desc limit 1" instead. This was the final method:

public double getBase() {
    final double DEFAULT_BASE = 0;
    Cursor mCursor = mDb.rawQuery(
            "SELECT base FROM constants ORDER BY _id desc limit 1", null);
    if (mCursor.getCount() == 0)
        return DEFAULT_BASE;
    else
        mCursor.moveToFirst();
        return mCursor.getDouble(0);

---

1) For querying the DB may be try this one:

https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

SQLiteDatabase has query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) method.

At least with this one you don't need to construct your SQL manually.

2) Check the doc for Cursor: http://developer.android.com/reference/android/database/Cursor.html#getString(int)

0 is a columnIndex.

---

You are probably trying to update a View when running a job on a separate thread, this almost always triggers a force close.

In this article, Painless Threading, you learn how to break of your long running work into another thread (and how you can "come back" to it). Basically you can tell the GUI thread (or event queue or ui thread) that you need a Runnable to run with access to your controls and views.

private void clicked() {
    // called from a click of a button for instance
    new Thread(new Runnable() {
        public void run() {
            // the long running job should be executed on a different
            // thread as to not stall the ui thread
            runLongWork();
        }
    }
}

private void runLongWork() {
    // this is method running on separate thread
    // so we should run a runnable on the ui thread instead to update our view's.
    this.runOnUiThread(new Runnable() {
        public void run() {
            // Now we are back in the ui thread
            editBase.setText(cDbHelper.getBase());
        }
    }
}

As for the other questions Arhimed already answered it partly. When you execute a query to the Sqlite database you get an instance of a class Cursor that sort of is a list of the results or rows that your query fetched. Using the moveToFirst method you position the cursor at the first row of results. You can then get the values from the columns in your query using the getXYZ() methods (for instance getString()) and pass an index of the column that you want. 0 in this case refers to base, the first column in your SQL query.

I you have more than one row you can use the moveToNext method to get the next row, and since it returns a bool stating that it actually "found" a next row, you can use it in a while loop to get all your rows.

while (cursor.moveToNext()) {
    // next row of data, use getString, getInt or others
}

Your query altough will always return only one value, since you limit the results to 1, so no need to use the moveToNext. moveToFirst will, like moveToNext, return true if a row was found, and false if there are no more rows available.

Your statement is as follows; you execute a query, move to the first row, and if it was successful return the string from the first column in the query (index starts at zero). If it doesn't find a row, you return the first column as a string, which will probably fail though. You should perhaps return a default value or throw an exception in that case.