Querying and working with Cursors in SQLite on Android
android, sqlite
Solution
- 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.
- Assuming you are stuck with model objects, have them load themselves out of a `Cursor`, rather than trying to pass in umpteen parameters.
- `query()` is much more verbose than `rawQuery()` for limited added value, if you know SQL.
- Assembling your `CREATE TABLE` clause via concatenation is self-imposed pain, not mandated by SQLite or Android.
- 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.
- `String` inherits from `CharSequence`, so all those casts can be dropped.
Problem
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