Iterate through rows from Sqlite-query

Question

I have a table layout that I want to populate with the result from a database query. I use a select all and the query returns four rows of data.

I use this code to populate the TextViews inside the table rows.

Cursor c = null;
c = dh.getAlternative2();
startManagingCursor(c);
// the desired columns to be bound
String[] columns = new String[] {DataHelper.KEY_ALT};
// the XML defined views which the data will be bound to
int[] to = new int[] { R.id.name_entry};

SimpleCursorAdapter mAdapter = new SimpleCursorAdapter(this, 
         R.layout.list_example_entry, c, columns, to);
this.setListAdapter(mAdapter);

I want to be able to separate the four different values of KEY_ALT, and choose where they go. I want them to populate four different TextViews instead of one in my example above.

How can I iterate through the resulting cursor?

score 111 · Answer 1 · answered Oct 05 '12 at 08:06

111

Cursor objects returned by database queries are positioned before the first entry, therefore iteration can be simplified to:

while (cursor.moveToNext()) {
    // Extract data.
}

Reference from SQLiteDatabase.

answered Oct 05 '12 at 08:06

happydude

3,869
2
23
41

5

+1 Also `cursor.moveToPosition(-1)` will deal with shared cursors. – Maurice Lam May 22 '13 at 17:30
Exception raised if i want to iterate through rows and the rows contains join data – EngineSense Mar 17 '16 at 08:58

score 82 · Accepted Answer · edited Jun 18 '15 at 15:09

82

You can use below code to go through cursor and store them in string array and after you can set them in four textview

String array[] = new String[cursor.getCount()];
i = 0;

cursor.moveToFirst();
while (!cursor.isAfterLast()) {
    array[i] = cursor.getString(0);
    i++;
    cursor.moveToNext();
}

edited Jun 18 '15 at 15:09

tread

10,133
17
95
170

answered Feb 07 '11 at 11:17

chikka.anddev

9,569
7
38
46

This makes sense, but I don't understand how I should use it. Should I still use the ListAdapter? Where do I specify the textviews each value should go to? – kakka47 Feb 07 '11 at 12:18
I got it working by skipping the ListAdapter and use a mTv4.setText(ids[3]); to specify the value of each textview. Thanks a lot! – kakka47 Feb 07 '11 at 13:16
I faced the same issue , i did as you did but I forgot to increment i (i++) so only the first row is there !!! – Muhannad A.Alhariri Sep 17 '13 at 10:44
sorry how do you initialize cur? I saw someone use Cursor cur = sampleDB.rawQuery("SELECT * FROM " + Constants.TABLE_NAME, null); but I don't know sampleDB I don't know how to initialize sampleDB – UmAnusorn Nov 03 '13 at 08:16
I'd also check to make sure the cursor isn't null and that is has values (via the `.getCount()` method). I still up voted though! – Jabari Nov 21 '13 at 06:43

Juozas Kontvainis · Answer 3 · 2011-11-18T11:07:24.260

29

for (boolean hasItem = cursor.moveToFirst(); hasItem; hasItem = cursor.moveToNext()) {
    // use cursor to work with current item
}

edited Nov 18 '11 at 11:07

answered Jul 22 '11 at 14:28

Juozas Kontvainis

9,461
6
55
66

score 20 · Answer 4 · edited Jan 23 '12 at 14:46

20

Iteration can be done in the following manner:

Cursor cur = sampleDB.rawQuery("SELECT * FROM " + Constants.TABLE_NAME, null);
ArrayList temp = new ArrayList();
if (cur != null) {
    if (cur.moveToFirst()) {
        do {
            temp.add(cur.getString(cur.getColumnIndex("Title"))); // "Title" is the field name(column) of the Table                 
        } while (cur.moveToNext());
    }
}

edited Jan 23 '12 at 14:46

tobsen

5,328
3
34
51

answered Feb 07 '11 at 11:14

chiranjib

5,288
8
53
82

I am almost sure that your line `if (cur = null)` wasn't what you wanted to write... I guess it should be `if (cur != null)` instead. Also you are instantiating (and therefore overwriting) `temp` before you `add` things. – tobsen Jan 22 '12 at 16:24
I also fixed the other part of the code. Hope you don't mind. ;) – tobsen Jan 23 '12 at 14:46

score 4 · Answer 5 · answered Jan 28 '14 at 12:47

Found a very simple way to iterate over a cursor

for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()){


    // access the curosr
    DatabaseUtils.dumpCurrentRowToString(cursor);
    final long id = cursor.getLong(cursor.getColumnIndex(BaseColumns._ID));


}

score 3 · Answer 6 · answered Apr 24 '13 at 01:49

3

I agree to chiranjib, my code is as follow:

if(cursor != null && cursor.getCount() > 0){
  cursor.moveToFirst();
  do{
    //do logic with cursor.
  }while(cursor.moveToNext());
}

answered Apr 24 '13 at 01:49

Jason H

369
5
5

Deepak Kataria · Answer 7 · 2017-11-24T11:16:43.487

public void SQLfunction() {
    SQLiteDatabase db = getReadableDatabase();
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

    String[] sqlSelect = {"column1","column2" ...};
    String sqlTable = "TableName";
    String selection = "column1= ?"; //optional
    String[] selectionArgs = {Value}; //optional

    qb.setTables(sqlTable);
    final Cursor c = qb.query(db, sqlSelect, selection, selectionArgs, null, null, null);

   if(c !=null && c.moveToFirst()){
        do {
           //do operations
           // example : abcField.setText(c.getString(c.getColumnIndex("ColumnName")))

          }
        while (c.moveToNext());
    }


}

NOTE: to use SQLiteQueryBuilder() you need to add

compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+' in your grade file

Iterate through rows from Sqlite-query

7 Answers7

Linked

Related