Querying and working with Cursors in SQLlite on Android

Question

Hi

Not sure if I'm the only one who feels this...

I find working with the sqlite api in android a complete pain in the butt and pretty soul destroying. Has anyone got any tips/helpers to make my life easier?

Here's an example of what I'm talking about.

//create code

db.execSQL("CREATE TABLE " + CUSTOMER_TABLE_NAME + " ("
                        + GENERIC_ID_KEY+ " INTEGER PRIMARY KEY NOT NULL, " 
                        + PHONE_KEY + " INTEGER NOT NULL, "
                        + CUSTOMER_NAME_KEY+ " TEXT NOT NULL, "
                        + EMAIL_KEY + " TEXT NOT NULL, "
                        + ADDRESS_KEY +" TEXT);");


//get code
    Cursor mCursor = mDb.query(true, CUSTOMER_TABLE_NAME, new String[] {GENERIC_ID_KEY,
                        ADDRESS_KEY, PHONE_KEY, EMAIL_KEY,CUSTOMER_NAME_KEY}, GENERIC_ID_KEY + "=" + customerDbId, null,
                                null, null, null, null);

        Customer customer = new Customer (customerDbId, (CharSequence)mCursor.getString(mCursor.getColumnIndexOrThrow(CUSTOMER_NAME_KEY)),
                            (CharSequence)mCursor.getString(mCursor.getColumnIndexOrThrow(PHONE_KEY)),
                            (CharSequence)mCursor.getString(mCursor.getColumnIndexOrThrow(EMAIL_KEY)),
                            (CharSequence)mCursor.getString(mCursor.getColumnIndexOrThrow(ADDRESS_KEY)));

This a simple exmple of creating a simple customer object from a db query; some of my code is far nastier than this. Hand crafting queries in this way leads to all sort of errors I don't find until runtime.

Any tips greatly appreiciated!

Ok after the tips below I now have this:

  db.execSQL("CREATE TABLE customer (_id INTEGER PRIMARY KEY NOT NULL, " 
                        + "phone_number INTEGER NOT NULL, "
                        + "name TEXT NOT NULL, "
                        + "email TEXT NOT NULL, "
                        + "address TEXT);");


    //get code
String q = "SELECT * FROM customer WHERE _id = " + customerDbId +";"
        Cursor mCursor = mDb.rawQuery(q, null);

        Customer customer = new Customer (mCursor);

in the Customer, I access the fields like this

mName = cursor.getString(2)

Ahh, I feel much better :)

Cheers Si

Answer 1

1. Don't use model objects if you do not have to. I've concluded, unless there is significant business logic that can only be represented via model objects, that they are more trouble than they are worth in a mobile platform.
2. Assuming you are stuck with model objects, have them load themselves out of a Cursor, rather than trying to pass in umpteen parameters.
3. query() is much more verbose than rawQuery() for limited added value, if you know SQL.
4. Assembling your CREATE TABLE clause via concatenation is self-imposed pain, not mandated by SQLite or Android.
5. Don't use getColumnIndexOrThrow() from custom-written code. You wrote the query, so you know what order the columns are coming back in. Only use something like getColumnIndexOrThrow() if you are creating some abstract library that does not know the details of the Cursor it was given.
6. String inherits from CharSequence, so all those casts can be dropped.

Answer 2

I tested a lot of my SQL in SQLite before copying them over to Android as strings. It's easier for me to debug when I can directly interact with the command line.

Another technique I use is saving as much of my queries as possible as string constants or string resources.

You also don't need SQL such as INTEGER NOT NULL since SQLite uses duck typing/manifest typing. It does help for type affinity though..