Accessing Data with Android Cursors (Part 2)

Retrieving Data

Retrieving data from SQLite databases in Android is done using Cursors. The Android SQLite query method returns a Cursor object containing the results of the query. To use Cursors android.database.Cursor must be imported.

About Cursor

Cursors store query result records in rows and grant many methods to access and iterate through the records. Cursors should be closed when no longer used, and will be deactivated with a call to Cursor.deactivate() when the application pauses or exists. On resume the Cursor.requery() statement is executed to re-enable the Cursor with fresh data. These functions can be managed by the parent Activity by calling startManagingCursor(). 

package com.kais.TestingData;

import java.util.Locale;

import android.app.Activity;
import android.os.Bundle;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class TestingData extends Activity {
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        SQLiteDatabase db;
        
        db = openOrCreateDatabase(
         "TestingData.db"
         , SQLiteDatabase.CREATE_IF_NECESSARY
         , null
         );
        db.setVersion(1);
        db.setLocale(Locale.getDefault());
        db.setLockingEnabled(true);
        
        Cursor cur = db.query("tbl_countries", 
         null, null, null, null, null, null);
        
        cur.close();
    }
}

Open the database as before. The query performed will return all records from the table tbl_countries. Next we'll look at how to access the data returned.

Iterating through Records

The Cursor class provides a couple of simple methods to allow iterating through Cursor data easily. Use moveToFirst() to position the Cursor pointer at the first record then use the moveToNext() function to iterate through the records. The isAfterLast() method performs a check to see if the cursor is pointed after the last record. When looping through records break the loop when this becomes false.

First add the following attribute to the TextView element in the main layout. The main layout is the xml file located at res/layouts/main.xml. We need to give this TextView element an ID that we can reference to update the view. 

android:id="@+id/hello"

Java code to iterate through entries in a cursor: 

cur.moveToFirst();
        while (cur.isAfterLast() == false) {
            view.append("n" + cur.getString(1));
            cur.moveToNext();
        }
        cur.close();

This code simply creates a cursor querying all the records of the table tbl_countries. The moveToFirst() method is used to position the cursor pointer at the beginning of the data set. Then loop through the records in the cursor. The method isAfterLast() returns a boolean if the pointer is past the last record in the cursor. Use the moveToNext() method to traverse through the records in the cursor.

Retrieving a Specific Record

The cursor also allows direct access to specific records. 

Cursor cur = db.query("tbl_countries", 
           null, null, null, null, null, null);
         cur.moveToPosition(0);
         view.append("n" + cur.getString(1));
         cur.close();