Tutorial part 2

Since we have scheduler to retrieve data maybe we want to put data into database. On Android we have SQLite as part of environment so choice is easy.

Subclassing SQLiteOpenHelper

Things are straightforward here. There are two methods which must be implemented:

@Override
public void onCreate(SQLiteDatabase arg0) {
    arg0.execSQL(createSQL);
}
@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
    arg0.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
    onCreate(arg0);
}

variable createSQL is String containing simple SQL statement to create table:

CREATE TABLE tweets (_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    user_name TEXT NOT NULL, date_name TEXT NOT NULL, 
    text_name TEXT NOT NULL)

Self-incrementing primary key _id is required by Android, to use it in data binding, must be named like this.
Another thing which is required is constructor:

public DbHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

Beside context we need to pass to super-class constructor DB name, optional CursorFactory and version.
Using helper looks like this:

DbHelper dbW = new DbHelper(this);
SQLiteDatabase db = dbW.getReadableDatabase();
Cursor cursor = db.query("tweets", new String[] { "_id", DbHelper.DATE,
        DbHelper.TEXT, DbHelper.USER }, null, null, null, null, null,
        null);
cursor.moveToFirst();
...
do something with cursor
...
cursor.close();

It is obviously called DbHelper, since query needs to be done readable database is enough. Query method looks odd and since we are selecting everything we just provide table name and collection of columns. Once when work with cursor is done we need to close it, SQLite is quite tough on resource managemet. Other parameters of query method are selection, order, limit, having, group by. If we want to specify selection it will be something like this:

cursor = db.query("route", new String[] { "_id",
        DbHelper.ADDRESS, DbHelper.PHONE,
        DbHelper.SURNAME }, DbHelper.STATUS + "="
        + Integer.toString(filterEquals) + " and "
        + DbHelper.ADDRESS + " like '%" + searchString + "%'",
        null, null, null, null, null);

So selection is “where”.
Instead of this pseudo OO approach raw SQL can be used:

String updateSQL = "UPDATE my_table SET complete = 4  WHERE status > 0;";
dbW.getWritableDatabase().execSQL(updateSQL);

It is certainly easier at the beginning to use normal SQL – less unknown stuff to fight with.
If we are take a look at code for this (and other) tutorial, we see that I never used directly SQL except inside provider. If you use it directly be prepared for really strict resource management and Android likes executing things on different threads what doesn't go nicely with SQLite which doesn't let you close cursors created on different tread from yours. Cure for this is to call startManagingCursor and leave to Android to close dangling cursor. Do not be discouraged with deprecation of startManagingCursor if you can't control execution it is much better than leaking resources. All that nightmare suddenly stops if you do not use SQLite DB directly but wrapped up in provider. Political approach, you are free to do what you want but we will make you use what we want you to use.
Code for this tutorial (and others) is here git://github.com/FBulovic/grumpyoldprogrammer.git
Next part of this tutorial will follow.