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.

Android Basics – Learn how to use the SQLiteOpenHelper class to setup App Database

SQLiteOpenHelper enables you to create and manage the database. To understand this better create a new class called SurveyDBHelper and extend this class.

In this class you will write the code to create the database tables needed for the Survey App.

public class SurveyDBHelper extends SQLiteOpenHelper

The Survey App will store the information in a survey table. The survey table will store – name, email and age.

It’s an Android convention to call your primary key columns _id.

Following are the data types that can be used

INTEGER – Numbers
TEXT – Character
REAL – Floating Points
NUMERIC – Booleans and Date
BLOB – Binary large object

Keeping this in mind you can use the CREATE TABLE SQL statement to create a new table.

@Override
public void onCreate(SQLiteDatabase db) {
 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);"
 );
}

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


Android Basics : New App to demonstrate SQLite Database Concepts

To understand how to use SQLite with your App, you will be creating a new App.

The App will be called SurveyApp. And it will let users fill in few details and store it in the database.

Follow the video to get started. The new App source code is available here.

Android Basics – Introduction to SQLite Database

Why you need a Database?

A scenario can be if your app needs to store data. Like when a user registers then you can save the information for later use.

There can be other reasons also. For example if a user has weak or no network then your app can display the last saved data to the user.
What is SQLite Database?

Android comes with a lighweight database which can be used with your apps. SQLite is an open source lighweight database.

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

Why use SQLite?

1. It’s lightweight – It uses a file to store data. So no processing time is needed and hence you conserve battery.

2. It’s fast and stable and can handle transactions.

3. It’s optimized for a single user.

How does SQLite store data?

Android creates a data folder for each app where the files reside. Two files are created to store the data.

/data/data/{unique-domain-name}/databases/example-db

This folder can only be accessed by the App.

Reference – https://www.sqlite.org/