304

I frequently see code which involves iterating over the result of a database query, doing something with each row, and then moving on to the next row. Typical examples are as follows.

Cursor cursor = db.rawQuery(...);
cursor.moveToFirst();
while (cursor.isAfterLast() == false) 
{
    ...
    cursor.moveToNext();
}
Cursor cursor = db.rawQuery(...);
for (boolean hasItem = cursor.moveToFirst(); 
     hasItem; 
     hasItem = cursor.moveToNext()) {
    ...
}
Cursor cursor = db.rawQuery(...);
if (cursor.moveToFirst()) {
    do {
        ...                 
    } while (cursor.moveToNext());
}

These all seem excessively long-winded to me, each with multiple calls to Cursor methods. Surely there must be a neater way?

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Graham Borland
  • 60,055
  • 21
  • 138
  • 179

10 Answers10

531

The simplest way is this:

while (cursor.moveToNext()) {
    ...
}

The cursor starts before the first result row, so on the first iteration this moves to the first result if it exists. If the cursor is empty, or the last row has already been processed, then the loop exits neatly.

Of course, don't forget to close the cursor once you're done with it, preferably in a finally clause.

Cursor cursor = db.rawQuery(...);
try {
    while (cursor.moveToNext()) {
        ...
    }
} finally {
    cursor.close();
}

If you target API 19+, you can use try-with-resources.

try (Cursor cursor = db.rawQuery(...)) {
    while (cursor.moveToNext()) {
        ...
    }
}
Graham Borland
  • 60,055
  • 21
  • 138
  • 179
  • 20
    so if you wanted to do this iteration with a cursor in an abritrary position beforehand, you would use cursor.moveToPosition(-1) before the while loop? – Sam May 16 '13 at 03:31
  • 2
    but before this we have to check for cursor is 'null'. Is there any alternative for that null check? – Ganapathy C Jul 02 '13 at 12:24
  • 14
    An SQLite database query will never return null. It will return an empty Cursor if no results are found. ContentProvider queries can sometimes return null, though. – Graham Borland Jul 02 '13 at 17:27
  • 8
    Just to add a few cents... Don't check if cursor has data by calling moveToFirst() before you are going to iterate over cursor - you will lose the first entry – AAverin Nov 13 '13 at 16:36
  • 48
    If using a `CursorLoader`, make sure you call `cursor.moveToPosition(-1)` before iterating, because the loader reuses the cursor when the screen orientation changes. Spent an hour tracking down this issue! – Vicky Chijwani Feb 22 '14 at 21:39
  • @VickyChijwani what is actually happening here? How can the cursor be at a negative position? – adamdport Dec 17 '14 at 15:53
  • @adamdport the cursor is just a wrapper over the underlying data. Moving it to position -1 means placing it "right before the first data item". Although there's no real data at that position, it's a convenient way to think and reason about the code. It's similar to C++'s `std::vector::end()` (iterator at a position "right after the end"), if that helps. – Vicky Chijwani Dec 17 '14 at 19:27
  • @adamdport I just saw [the other question](http://stackoverflow.com/q/27530128/504611) you created. The answer is: yes, `moveToPosition(-1)` is only intended as a convenience. – Vicky Chijwani Dec 17 '14 at 19:39
  • For me even this solution didn't help. don't know why it kept on showing the GC logs. Had to do the coding based on the count! ```int count = cursor.getCount(); for (int i = 0; i < count; i++) { products.add(loadObject(cursor)); cursor.moveToNext(); }``` – rahulrvp Nov 30 '16 at 09:49
  • *Please note: If using this with a CursorLoader, **do not call cursor.close()**. The API does that automatically. See this page: https://developer.android.com/guide/components/loaders.html – RightHandedMonkey Jun 07 '17 at 18:36
  • I think is answer is unsafe. see @Jörg Eisfeld answer – Karue Benson Karue Jul 09 '17 at 13:21
122

The best looking way I've found to go through a cursor is the following:

Cursor cursor;
... //fill the cursor here

for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
    // do what you need with the cursor here
}

Don't forget to close the cursor afterwards

EDIT: The given solution is great if you ever need to iterate a cursor that you are not responsible of. A good example would be, if you are taking a cursor as argument in a method, and you need to scan the cursor for a given value, without having to worry about the cursor's current position.

Alex Styl
  • 3,982
  • 2
  • 28
  • 47
  • 8
    Why call three different methods, when you can do it with just one? Why do you think that is better? – Graham Borland May 27 '13 at 14:01
  • 11
    This is the safest way to go if you're reloading a pre-existing cursor and want to be sure that your iteration starts from the beginning. – Michael Eilers Smith Aug 05 '13 at 04:49
  • 9
    I agree it's clearer than the simpler alternative. I generally favour clarity to brevity. A similar variation with while loop - http://android.codota.com/scenarios/51891850da0a87eb5be3cc22/android.database.Cursor?tag=out_2013_05_05_07_19_34&fullSource=1 – drorw Oct 15 '13 at 14:31
50

I'd just like to point out a third alternative which also works if the cursor is not at the start position:

if (cursor.moveToFirst()) {
    do {
        // do what you need with the cursor here
    } while (cursor.moveToNext());
}
Jörg Eisfeld
  • 1,299
  • 13
  • 7
  • 1
    There is a redundant check. You can replace the if + do-while, with simple while as given in accepted solution, which is also simpler/more readable. – mtk Apr 19 '14 at 19:20
  • 8
    @mtk no, it's not redundant, that's the point - if the cursor is being reused, it might be at a position, hence the need to explicitly call moveToFirst – Mike Repass May 16 '14 at 21:44
  • This is only useful if you have an else statement with logging ; otherwise [Graham Borland's answer](http://stackoverflow.com/a/10723771/94363) is more concise. – rds Jul 22 '14 at 09:33
  • 5
    As [Vicky Chijwani's](http://stackoverflow.com/questions/10723770/whats-the-best-way-to-iterate-an-android-cursor#comment33274077_10723771) comment points out, in real world usage the Graham Borland's answer is unsafe and requires `moveToPosition(-1)`. So both answers are equally consise as they both have two cursor calls. I think this answer just edges out Borland's answer as it doesn't require the `-1` magic number. – Jade Sep 09 '14 at 17:32
  • I actually think its useful in the case that you want to know that the cursor had no values as it is easy and makes sense to add an else to the if statement here. – chacham15 Jan 01 '15 at 20:44
  • I would not say it is the best solution. It is one solution. It highly depends on how you use the cursor. Did you do anything beforehand? Which class implementation of Cursor is used? Where does it come from? And so on. I would add specific information where and when to use this code. Not just pasting it. – JacksOnF1re Aug 11 '20 at 08:47
10

Below could be the better way:

if (cursor.moveToFirst()) {
   while (!cursor.isAfterLast()) {
         //your code to implement
         cursor.moveToNext();
    }
}
cursor.close();

The above code would insure that it would go through entire iteration and won't escape first and last iteration.

Stefan Sprenger
  • 1,050
  • 20
  • 42
Pankaj
  • 7,908
  • 6
  • 42
  • 65
9

How about using foreach loop:

Cursor cursor;
for (Cursor c : CursorUtils.iterate(cursor)) {
    //c.doSth()
}

However my version of CursorUtils should be less ugly, but it automatically closes the cursor:

public class CursorUtils {
public static Iterable<Cursor> iterate(Cursor cursor) {
    return new IterableWithObject<Cursor>(cursor) {
        @Override
        public Iterator<Cursor> iterator() {
            return new IteratorWithObject<Cursor>(t) {
                @Override
                public boolean hasNext() {
                    t.moveToNext();
                    if (t.isAfterLast()) {
                        t.close();
                        return false;
                    }
                    return true;
                }
                @Override
                public Cursor next() {
                    return t;
                }
                @Override
                public void remove() {
                    throw new UnsupportedOperationException("CursorUtils : remove : ");
                }
                @Override
                protected void onCreate() {
                    t.moveToPosition(-1);
                }
            };
        }
    };
}

private static abstract class IteratorWithObject<T> implements Iterator<T> {
    protected T t;
    public IteratorWithObject(T t) {
        this.t = t;
        this.onCreate();
    }
    protected abstract void onCreate();
}

private static abstract class IterableWithObject<T> implements Iterable<T> {
    protected T t;
    public IterableWithObject(T t) {
        this.t = t;
    }
}
}
Sergey Shustikov
  • 15,377
  • 12
  • 67
  • 119
  • This is a pretty cool solution, but it hides the fact that you are using the same `Cursor` instance in each iteration of the loop. – npace Mar 07 '16 at 11:50
6
import java.util.Iterator;
import android.database.Cursor;

public class IterableCursor implements Iterable<Cursor>, Iterator<Cursor> {
    Cursor cursor;
    int toVisit;
    public IterableCursor(Cursor cursor) {
        this.cursor = cursor;
        toVisit = cursor.getCount();
    }
    public Iterator<Cursor> iterator() {
        cursor.moveToPosition(-1);
        return this;
    }
    public boolean hasNext() {
        return toVisit>0;
    }
    public Cursor next() {
    //  if (!hasNext()) {
    //      throw new NoSuchElementException();
    //  }
        cursor.moveToNext();
        toVisit--;
        return cursor;
    }
    public void remove() {
        throw new UnsupportedOperationException();
    }
}

Example code:

static void listAllPhones(Context context) {
    Cursor phones = context.getContentResolver().query(ContactsContract.CommonDataKinds.Phone.CONTENT_URI, null, null, null, null);
    for (Cursor phone : new IterableCursor(phones)) {
        String name = phone.getString(phone.getColumnIndex(ContactsContract.CommonDataKinds.Phone.DISPLAY_NAME));
        String phoneNumber = phone.getString(phone.getColumnIndex(ContactsContract.CommonDataKinds.Phone.NUMBER));
        Log.d("name=" + name + " phoneNumber=" + phoneNumber);
    }
    phones.close();
}
18446744073709551615
  • 16,368
  • 4
  • 94
  • 127
  • +1 for nice and compact implementation. Bugfix: `iterator()` should also recalculate `toVisit = cursor.getCount();` I use `class IterableCursor implements Iterable, Iterator {...` that receives a class that `extends CursorWrapper implements MyInterface` where MyInterface defines getters for database properties. This way i have a cursor based Iterator – k3b Feb 22 '17 at 16:19
4

The Do/While solution is more elegant, but if you do use just the While solution posted above, without the moveToPosition(-1) you will miss the first element (at least on the Contact query).

I suggest:

if (cursor.getCount() > 0) {
    cursor.moveToPosition(-1);
    while (cursor.moveToNext()) {
          <do stuff>
    }
}
Lars
  • 9,976
  • 4
  • 34
  • 40
3

The cursor is the Interface that represents a 2-dimensional table of any database.

When you try to retrieve some data using SELECT statement, then the database will 1st create a CURSOR object and return its reference to you.

The pointer of this returned reference is pointing to the 0th location which is otherwise called as before the first location of the Cursor, so when you want to retrieve data from the cursor, you have to 1st move to the 1st record so we have to use moveToFirst

When you invoke moveToFirst() method on the Cursor, it takes the cursor pointer to the 1st location. Now you can access the data present in the 1st record

The best way to look :

Cursor cursor

for (cursor.moveToFirst(); 
     !cursor.isAfterLast();  
     cursor.moveToNext()) {
                  .........
     }
Raj Shah
  • 668
  • 1
  • 9
  • 23
2
if (cursor.getCount() == 0)
  return;

cursor.moveToFirst();

while (!cursor.isAfterLast())
{
  // do something
  cursor.moveToNext();
}

cursor.close();
Hun
  • 3,652
  • 35
  • 72
0

Initially cursor is not on the first row show using moveToNext() you can iterate the cursor when record is not exist then it return false,unless it return true,

while (cursor.moveToNext()) {
    ...
}
ρяσѕρєя K
  • 132,198
  • 53
  • 198
  • 213
kundan kamal
  • 674
  • 7
  • 16