android cursor.moveToNext()?

Question

I am trying to query all the columns in a table into one long text view and/or string. I know this might not be the right way to do things but I have to do this. Correct me if I am wrong, I was under the impression that move next would get the next column in the row:

Cursor c = db.get();
if(c.moveToFirst){
  do{
    string = c.getString(0);
  }while(c.moveToNext);
}

I thought that this would get the first column and display all of its contents instead I get the first column and first row. What am I doing wrong? Is there a better or real way to get this information without using a ListView?

Solution 1

For clarity a complete example would be as follows which I trust is of interest. As code comments indicated we essentially iterate over database rows and then columns to form a table of data as per database.

    Cursor cursor = getActivity().getContentResolver().query(uri, projection, null, null,
            null);

    //if the cursor isnt null we will essentially iterate over rows and then columns
    //to form a table of data as per database.
    if (cursor != null) {

        //more to the first row
        cursor.moveToFirst();

        //iterate over rows
        for (int i = 0; i < cursor.getCount(); i++) {

            //iterate over the columns
            for(int j = 0; j < cursor.getColumnNames().length; j++){ 

                //append the column value to the string builder and delimit by a pipe symbol
                stringBuilder.append(cursor.getString(j) + "|"); 
            }
            //add a new line carriage return
            stringBuilder.append("\n");

            //move to the next row
            cursor.moveToNext();
        }
        //close the cursor
        cursor.close();
    }

Solution 2

The simple use is:

Cursor cursor = db.query(...);
while (cursor.moveToNext()) {
    ...
}

moveToFirst is used when you need to start iterating from start after you have already reached some position.

Avoid using cursor.getCount() except if it is required. And never use a loop over getCount().

getCount is expensive - it iterates over many records to count them. It doesn't return a stored variable. There may be some caching on a second call, but the first call doesn't know the answer until it is counted.

If your query matches 1000 rows, the cursor actually has only the first row. Each moveToNext searches and finds the next match. getCount must find all 1000. Why iterate over all if you only need 10? Why iterate twice?

Also, if your query doesn't use an index, getCount may be even slower - getCount may go over 10000 records even though the query matches only 100. Why loop 20000 instead of 10000?

Solution 3

I am coding my loops over the cusror like this:

    cursor.moveToFirst();
    while(!cursor.isAfterLast()) {

            cursor.getString(cursor.getColumnIndex("column_name"));

        cursor.moveToNext();
    }

That always works. This will retrieve the values of column "column_name" of all rows. Your mistake is that you loop over the rows and not the columns. To loop over the columns:

cursor.moveToFirst();    
    for(int i = 0; i < cursor.getColumnNames().length; i++){
        cursor.getString(i);
    }

That will loop over the columns of the first row and retrieve each columns value.

Solution 4

cursor.moveToFirst() moves the cursor to the first row. If you know that you have 6 columns, and you want one string containing all the columns, try the following.

c.moveToFirst();
StringBuilder stringBuilder = new StringBuilder();
for(int i = 0; i < 6; i++){
   stringBuilder.append(c.getString(i));
}

// to return the string, you would do stringBuilder.toString();

Solution 5

moveToNext move the cursor to the next row. and c.getString(0) will always give you the first column if there is one. I think you should do something similar to this inside your loop

int index = c.getColumnIndex("Column_Name");
string = c.getString(index);

View more solutions

Comments

[Squonk]

And if you don't know the number of columns, just use c.getColumnCount() in the for loop.

[kennytm]

As of Android 4.2, moveToNext() is implemented by moveToPosition() which itself also calls .getCount(). Surely this is just implementation detail which shouldn't be relied up on.

[Matthias]

@KennyTM is right. In fact this was the case even before Android 4.2 (see bit.ly/1s8KClM). Moreover, besides moveToNext() pretty much all other basic cursor operations (e.g. moveToFirst(), isBeforeFirst() and isFirst()) involve call(s) to getCount(). So even if getCount() is expensive it is unavoidable. Moreover, the implementation of getCount() caches the returned value, so at least subsequent calls will be cheap (see bit.ly/1uL4ZEp).

[Dracarys]

moveToFirst before a moveToNext avoid to binding results.

[user3502626]

Actually just moveToNext() won't work if you have only 1 row. I just experiment this problem.

[user3502626]

Unless you use do{}while() instead of while.