Returning SQLite query and returning as text

Question

I have a query (getMinScore) which takes the min value from my table, I need to take this and display it in a text field but in it's current state it displays

High Score: android.database.SQLiteCursor@.......

I don't know what this means, how can I get the string value from this?

I am calling the query from my GameView class as in the second block of code. Really appreciate any help!

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "scores.db";
public static final String TABLE_NAME = "scores_table";
public static final String COLUMN_ID = "ID";
public static final String COLUMN_SCORE = "SCORE";

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table " + TABLE_NAME +" (ID INTEGER PRIMARY KEY AUTOINCREMENT, SCORE INTEGER)");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

public boolean insertData(String score) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_SCORE, score);
    long result = db.insert(TABLE_NAME, null, contentValues);
    System.out.println("Data inserted" + score);

    if(result == -1) {
        return false;
    }
    else {
        return true;
    }

}

public Cursor getAllData() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from " + TABLE_NAME, null);
    return res;
}

public Cursor getMinScore() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select min (SCORE) from " + TABLE_NAME, null);
    return res;
}

public boolean updateData(String id, String score) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COLUMN_ID, id);
    contentValues.put(COLUMN_SCORE, score);
    db.update(TABLE_NAME, contentValues, "ID = ?", new String[] { id });
    return true;
}

public Integer deleteData(String id) {
    SQLiteDatabase db = this.getWritableDatabase();
    return db.delete(TABLE_NAME, "ID = ?", new String[] { id });
}
}

GameView.java

String minScore = mydb.getMinScore().toString();
TextPaint tp = new TextPaint();
tp.setColor(Color.GREEN);
tp.setTextSize(40);
tp.setTypeface(Typeface.create("Courier", Typeface.BOLD));
canvas.drawText("Moves: " + String.valueOf(turns), 10, 1180, tp);
canvas.drawText("High score: " + mydb.getMinScore(), 10, 1280, tp);

That is all I can see, the rest displays as a solid block of colour after that. — jb2002, Feb 19 '17 at 20:52

score 0 · Answer 1 · answered Feb 19 '17 at 20:54

A Cursor is an object that gives you access to the results of a database query by iterating over the rows. You can't just print out the cursor, you need to check if there is a next row and then pull the value out from it.

You could change your getMinScore method to return the minScore value instead of returning the cursor:

public int getMinScore() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select min (SCORE) from " + TABLE_NAME, null);

    if(res.moveToNext()) {
        // return the integer from the first column
        return res.getInt(0);
    } else {
        // there were no results
        return -1;
    }
}

Have a read of the documentation to understand the cursor methods.

score 0 · Answer 2 · answered Feb 19 '17 at 21:03

0

You have to get your value from the returning Cursor object. In your case you can use

int score = res.getInt(res.getColumnIndex("SCORE"));

Be sure to check the returning cursor is not NULL by checking cursor count > ZERO.

Change your method

public Cursor getMinScore() {
SQLiteDatabase db = this.getWritableDatabase();
Cursor res = db.rawQuery("select min (SCORE) from " + TABLE_NAME, null);
return res;

}

To

public int getMinScore() {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select min (SCORE) from " + TABLE_NAME, null);
    if (res.getCount() > 0) {
        return res.getInt(res.getColumnIndex("SCORE"));
    } else {
        return -1; // Some defualt value when no data is retrieved 
    }
}

answered Feb 19 '17 at 21:03

Zeerak Hameem

147
7

1

`min()` always returns a value. – CL. Feb 19 '17 at 21:05
I added the check just to avoid the exception. If any. – Zeerak Hameem Feb 19 '17 at 21:08
What exception? Without any table rows, `min()` would return a result row with a NULL value, which `getInt()` would convert into `0`. – CL. Feb 19 '17 at 21:11
Agreed but If it returns null, the condition wil be false. And only else part runs. getInt() won't be called in that case. – Zeerak Hameem Feb 19 '17 at 21:13
Using this method results in it just outputting 0. Would it output 0 if it wasn't inserting data properly? Is there an easy way to check? – jb2002 Feb 19 '17 at 21:32
@ZeerakHameem, as `min()` always returns a value, this query always will have 1 row. This condition will never be false. – jlasierra Feb 19 '17 at 21:55
Yes @jb2002, it seems that your table is empty. See http://stackoverflow.com/questions/17529766/view-contents-of-database-file-in-android-studio for some ways to view your database. – jlasierra Feb 19 '17 at 22:00
@jlasierra yes agreed, i added the check just to consider the possibility that cursor could be null while querying the database. In this specific case 1 row will be returned. Thanks for pointing out :) – Zeerak Hameem Feb 20 '17 at 06:07
@jb2002 if you want to check that data is properly inserted in the database. You can use the result of your insertData method. If the data is properly added it will return a positive value probably 1. If the data is not added properly it will return -1. – Zeerak Hameem Feb 20 '17 at 06:09

score 0 · Answer 3 · answered Feb 19 '17 at 22:54

you Should always close Cursor to prevent memory leak

so

   public int getMinScore() {
        int result = -1;

        Cursor res = db.rawQuery("select min (SCORE) from " + TABLE_NAME, null);

        if (res.moveToFirst()) {
            result = res.getInt(0);
        }

        if (!res.isClosed()){

            res.close();
        }
        return result;
    }
}

i think it better to user method open and close instead of calling getWritableDatabase() every time

public void open() {
    bdd = dbHelper.getWritableDatabase();
}

public void close() {
    bdd.close();
}

Returning SQLite query and returning as text

3 Answers3