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
SQLiteCursor. A Cursor implementation that exposes results from a query on a SQLiteDatabase . This interface provides random read-write access to the result set returned by a database query.
The sqlite3. Cursor class is an instance using which you can invoke methods that execute SQLite statements, fetch data from the result sets of the queries. You can create Cursor object using the cursor() method of the Connection object/class.
Cursors are what contain the result set of a query made against a database in Android. The Cursor class has an API that allows an app to read (in a type-safe manner) the columns that were returned from the query as well as iterate over the rows of the result set.
Cursor
, rather than trying to pass in umpteen parameters.query()
is much more verbose than rawQuery()
for limited added value, if you know SQL.CREATE TABLE
clause via concatenation is self-imposed pain, not mandated by SQLite or Android.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.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With