Android Basics – Learn about the changeCursor method to refresh Cursor data

You have seen how to use a Cursor to fetch data from a SQLite database. And then how to use a CursorAdapter to display the data in a ListView.

One problem with a Cursor is that it does not track the database changes. So if the data gets updated a cursor won’t present the updated version until the Activity gets recreated.

To illustrate this I have added a ListView in the layout xml, to display the surveys which have been added to the database.

To overcome this problem in the Activity onRestart method you need to use the changeCursor method. Refer to the below code segment.
Layout XML Code

<ListView
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:id="@+id/displaySurveyDataId"/>

MainActivity Code

@Override
public void onRestart(){
    super.onRestart();

    Log.v(TAG, "Check for data refresh");

    try {
        SurveyDBHelper surveyDBHelper = new SurveyDBHelper(this);
        SQLiteDatabase db = surveyDBHelper.getReadableDatabase();

        Cursor cursor = surveyDBHelper.getSurveyData(db);

        ListView lv = (ListView) findViewById(R.id.displaySurveyDataId);

        CursorAdapter ca = (CursorAdapter)lv.getAdapter();
        ca.changeCursor(cursor);

    }catch (SQLiteException e){
        Log.v(TAG, "Exception "+e.getMessage());
    }
}

Please follow the video to understand this better. Also the source code is available here for reference.

Android Basics – Learn how to insert records to the SQLite Database

So far you have seen how to fetch data from the SQLite database using Cursor. Inserting and updating data is also pretty simple.

The following code inserts data to the survey table.

try {
    SurveyDBHelper surveyDBHelper = new SurveyDBHelper(this);
    SQLiteDatabase db = surveyDBHelper.getReadableDatabase();

    ContentValues cv = new ContentValues();
    cv.put(SurveyDBHelper.SURVEY_TABLE_NAME_COLUMN, userNameId.getText().toString());
    cv.put(SurveyDBHelper.SURVEY_TABLE_EMAIL_COLUMN, userEmailId.getText().toString());
    cv.put(SurveyDBHelper.SURVEY_TABLE_AGE_COLUMN, userAgeId.getText().toString());

    db.insert(SurveyDBHelper.SURVEY_TABLE, null, cv);

    db.close();
}catch (SQLiteException e){
    Log.v(TAG, "Exception "+e.getMessage());
}

In the above code fragment you need to create a Content Value object which stores key value pairs. This object is then used for insert.

Follow the video to understand this better. The source code is available here for reference.

Android Basics – Learn how to Navigate a Cursor to get data

In an earlier Article you saw how to populate data, from the database, to a cursor using the query() method.

Once the Cursor has data you can iterate over it. On every iteration you can get data corresponding to a row in the table.

There are four main methods available to navigate through a Cursor:

  • moveToFirst()
  • moveToLast()
  • moveToPrevious()
  • moveToNext()

In the example below we demonstrate how to use moveToFirst() method

//Sample Code to query the database
try {
    SurveyDBHelper surveyDBHelper = new SurveyDBHelper(this);
    SQLiteDatabase db = surveyDBHelper.getReadableDatabase();

    Cursor cursor = surveyDBHelper.getSurveyData(db);

    if (cursor.moveToFirst()) {
        //Get the data
        String name = cursor.getString(1);
        String email = cursor.getString(2);
        Log.v(TAG, "Name "+name);
    }

    cursor.close();
    db.close();
}catch (SQLiteException e){
    Log.v(TAG, "Exception "+e.getMessage());
}
//End

Source code is available here for reference.

Android Basics – Learn how to get a reference to the Database

In the last lecture you learnt how to query your database. In order to query or perform any operation on the database you need to get a reference to it.

This can be done using getReadableDatabase() or getWritableDatabase()

Refer to the sample code below, in which these methods are used to get the SurveyDBHelper object in MainActivity class

SurveyDBHelper surveyDBHelper = new SurveyDBHelper(this);
SQLiteDatabase db = surveyDBHelper.getReadableDatabase();

Follow the video to understand this better. You can refer to the source code here.

Android Basics – Learn how to use Cursor to get data from SQLite

In the last lecture you learnt how to upgrade and downgrade your database.

Now that you have a database and a table in place let’s try to understand how to query the database to get data.

Cursor gives you access to the database. In order to use a Cursor you need to use a Query.

To read from a database, use the query() method, passing it your selection criteria and desired columns.

Here is how the query method looks like

query(String table,
String[] columns,
String selection,
String[] selectionArgs,
String groupBy,
String having,
String orderBy,
String limit)

Here is a simple example from the Survey app code base

 

public Cursor getSurveyData(SQLiteDatabase db){

    // Define a projection that specifies which columns from the database you will actually use after this query.

    String[] projection = {
            "_id",
            SURVEY_TABLE_NAME_COLUMN,
            SURVEY_TABLE_EMAIL_COLUMN,
            SURVEY_TABLE_AGE_COLUMN
    };

    // How you want the results sorted in the resulting Cursor
    String sortOrder = "_id" + " DESC";

    return db.query(
            SURVEY_TABLE,
            projection,
            null, null, null, null,
            sortOrder
    );
}

 

Please follow the video and refer to the source code.

References

Android Basics – Learn How to Update SQLite Database

In the last lecture you learnt how to create a database and also a table.

Let’s consider the following scenario. You roll out the Survey app and people start to use that. They take surveys and capture the data, which gets stored in the survey table. Everything looks good.

A month later you want to introduce a new column to the survey table. The column is meant to capture the “license” number as well.

So how to push this change ? For this scenario SQLiteOpenHelper onUpgrade() and onDowngrade() method comes in handy.

There can be two scenarios

1. A user downloads your app for the first time. In this case onCreate() method will be called.
2. A user updates the already installed app. In this case onUpgrade() or onDowngrade() method will be called.

So how does SQLite figure out if the database is out of date?

It’s based on the database version number. If you remember we defined a field in the helper class.

static final String DB_VERSION=”1″;

This version is passed to the constructor and if its greater than what was specified earlier then upgrade happens via onUpgrade() method.

Let’s look at the onUpgrade() method

This method takes three parameters.

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

So the logic you write is based on the oldVersion and newVersion parameters.

 

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    if(oldVersion < 1){

        db.execSQL("CREATE TABLE "+SURVEY_TABLE+" (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                        +SURVEY_TABLE_NAME_COLUMN+" TEXT, "
                        +SURVEY_TABLE_EMAIL_COLUMN+" TEXT, "
                        +SURVEY_TABLE_AGE_COLUMN+" INTEGER);"
        );

    }
    if(oldVersion < 2){
        //Perform Updates
        db.execSQL("ALTER TABLE "+SURVEY_TABLE+" ADD COLUMN "+SURVEY_TABLE_LICENSE_COLUMN+" TEXT");
    }
}

This code is available in Github here.