Selecting one row from sqlite database using rawQuery and rowid in Android

Question

I've created an external SQLite database that looks something like this:

What I want to do in my Android application is to load all the values from one row using ROWID and put them into array. For example:

Cursor cursor = db.rawQuery("SELECT * FROM Dairy WHERE ROWID = 1", null);

This would return all the values from row 1: 51, 35, 63. However, this always returns just first value, in this case 51. Also, the cursor.getCount() always returns 1.

Here's my complete code:

db = getReadableDatabase();

Cursor cursor = db.rawQuery("SELECT * FROM Dairy WHERE ROWID = 1", null);

yData = new int[cursor.getCount()];

if (cursor.moveToFirst()) {
    for (int i = 0; i < cursor.getCount(); i++) {
        yData[i] = cursor.getInt(0);
        cursor.moveToNext();
    }
    cursor.close();
}
db.close();

Answer 1

If I correctly understand you need to return value of every column from the single row. Since you select only single row and want to get value from every column you need only to get the index of every column. and next iterate through them. Here is the code how it can be done.

Cursor cursor = db.rawQuery("SELECT * FROM Dairy WHERE ROWID = 1 Limit 1", null);

if (cursor.moveToFirst()) {
    String[] columnNames = cursor.getColumnNames();
    yData = new int[columnNames.length];

    for (int i = 0; i < columnNames.length; i++) {
        // Assume every column is int
        yData[i] = cursor.getInt(cursor.getColumnIndex(columnNames[i]));
    }  
}

cursor.close();
db.close();

Answer 2

The getInt (int columnIndex) method returns the requested column and not the entire row. Whereas, moveToFirst () and moveToNext () will move the cursor to the first or next row if the cursor is not empty or if the cursor is not past the last row if moveToNext () is called. Otherwise both will return false.

So, on success, a cursor contains a complete row, and you can access the elements either by provide 0 based indices directly or getting a column index by getColumnIndex(String columnName) providing a column name to get the index of that column and accessing that column then.

You can refer to the android developers guide, Cursor.

Answer 3

You always get the same value:

if (cursor.moveToFirst()) {
    for (int i = 0; i < cursor.getCount(); i++) {
        //cursor.getInt(0); You probably want to get 0, 1 & 2
        yData[i] = cursor.getInt(0);
        cursor.moveToNext();
    }

    cursor.close();
}