Android DataServices – SQLite

You’ve made your way through the Android ‘Notepad‘ tutorial, but find yourself wanting more?  Maybe you want to deal with Typed Objects instead of all of these strings everywhere? While this code isn’t generic enough to paste into your app and use as-is, it is simple enough to adapt to your own typed objects.  Using the Interface described here makes it super easy to change the way you are storing your data, without having to refactor your app itself (imagine a hybrid online/offline storage, or as we’ll see in the next post, an Object Oriented DataBase[OODB]).

If you’d like to follow along in eclipse, source code is found here: http://svn.hat6.com/hat6public/DataServiceExample/trunk.

We’re only looking at one file here, ReminderSQLiteImpl.java. I’ve tried to make certain that the comments explain everything, so I’m just dumping the code below. This class comes in at about twice the size of the Notebook’s DBAdapter class, but most of that comes from my class storing more columns of data.

/**... imports removed ...**/
public class ReminderSQLiteImpl implements IReminderDataService {
	/**
	 * Static variables describing our schema.  We use these variables so that we can refer to the
	 * constants instead of having to worry about refactoring when our schema changes.
	 */
	public static final String KEY_ROWID = "_id";
    public static final String KEY_TITLE = "title";
    public static final String KEY_ICON = "icon";
    public static final String KEY_STARTDATE = "startDate";
    public static final String KEY_ENDDATE = "endDate";
    public static final String KEY_MODIFIED = "modified";
    //TAG for logging.
    private static final String TAG = "ReminderSQLiteImpl";
    /**
     * Static variables holding information about our database itself.
     * NAME and TABLE are self explanatory.
     * VERSION allows our app to know if it has to perform any upgrades (right now it's
     * 		simply dropping the entire table and all associated data).
     * CREATE allows us to specify the SQL needed to create our DB.
     */
    private static final String DATABASE_NAME = "hatsixExamples";
    private static final String DATABASE_TABLE = "remindersDS";
    private static final int    DATABASE_VERSION = 1;
    private static final String DATABASE_CREATE =
        "create table " + DATABASE_TABLE + " (" + KEY_ROWID + " INTEGER primary key autoincrement, "
                + KEY_TITLE + " TEXT not null, "
                + KEY_STARTDATE + " TEXT, "
                + KEY_ENDDATE + " TEXT, "
                + KEY_MODIFIED + " DATE not null"
                + ");";

	/**
	 * Context is needed when interacting with the DB
	 */
	private final Context mCtx;
	/**
	 * DatabaseHelper is a private class, defined at the end of this file.
	 */
    private DatabaseHelper mDbHelper;
    /**
     * The actual Database.
     */
    private SQLiteDatabase mDb;

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     *
     * @param ctx the Context within which to work
     */
    public ReminderSQLiteImpl(Context ctx) {
		mCtx = ctx;
		open();
	}

    /**
     * Open the database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     *
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public ReminderSQLiteImpl open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

    /**
     * Create a new reminder record.  We pass in a Reminder, rather than just
     * some text values like in the Notepad Example. If the reminder is
     * successfully created return the new rowId for that reminder, otherwise return
     * a -1 to indicate failure.
     *
     * @param r the Reminder we want saved.
     * @return rowId or -1 if failed
     */
    public long createReminder(Reminder r) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, r.getTitle());
        initialValues.put(KEY_STARTDATE, r.getStart());
        initialValues.put(KEY_ENDDATE, r.getEnd());
        initialValues.put(KEY_MODIFIED,convertDateToString(new Date()));

        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    /**
     * Fetch all Reminders.  This will utilize SQLite's cursors and create an ArrayList
     * that is passed back to the caller.
     *
     * @return List of Reminders
     */
    public List<Reminder> fetchAllReminders(){
    	Cursor cursor = fetchAllRemindersCursor();
        int idColumn = cursor.getColumnIndex(KEY_ROWID);
        int titleColumn = cursor.getColumnIndex(KEY_TITLE);
        int startColumn = cursor.getColumnIndex(KEY_STARTDATE);
        int endColumn = cursor.getColumnIndex(KEY_ENDDATE);

        List<Reminder> list = new ArrayList<Reminder>();
    	if(cursor != null){
            if(cursor.moveToFirst()){

                int count = cursor.getCount();
                Log.d("ReminderSqliteImpl", "there are " + count + " records.");

                for(int i=0; i<count; i++){
                    Reminder reminder = new Reminder();
                    reminder.setId(cursor.getLong(idColumn));
                    reminder.setTitle(cursor.getString(titleColumn));
                    reminder.setStart(cursor.getString(startColumn));
                    reminder.setEnd(cursor.getString(endColumn));

                    list.add(reminder);

                    cursor.moveToNext();
                }
            }
        }
    	return list;
    }
    /**
     * Fetch Individual Reminder.  We take the 'query by example' approach, where we pass in
     * a Reminder.  This would allow us to query by Title if id wasn't set.
     *
     * @param r Reminder with id set to the Reminder we want fetched.
     * @return Reminder that was passed in, with data filled out from query.
     */
    public Reminder fetchReminder(Reminder r){
    	Cursor cursor = fetchReminderCursor(r.getId());
        int idColumn = cursor.getColumnIndex(KEY_ROWID);
        int titleColumn = cursor.getColumnIndex(KEY_TITLE);
        int startColumn = cursor.getColumnIndex(KEY_STARTDATE);
        int endColumn = cursor.getColumnIndex(KEY_ENDDATE);
    	if(cursor != null){
            r.setId(cursor.getLong(idColumn));
            r.setTitle(cursor.getString(titleColumn));
            r.setStart(cursor.getString(startColumn));
            r.setEnd(cursor.getString(endColumn));
    	}
    	return r;
    }
    /**
     * Update the reminder using the details provided. We pass in a
     * Reminder, create ContentValues from that Reminder, then update
     * the record based on rowID
     *
     * @param r the Reminder to update
     * @return true if the reminder was successfully updated, false otherwise
     */
    public boolean updateReminder(Reminder r) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, r.getTitle());
        args.put(KEY_STARTDATE, r.getStart());
        args.put(KEY_ENDDATE, r.getEnd());
        args.put(KEY_MODIFIED,convertDateToString(new Date()));

        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + r.getId(), null) > 0;
    }

    /**
     * Delete the reminder.  Rather than passing in the rowID, we pass the entire
     * reminder.  Again, by passing objects, our app is significantly simplified.
     *
     * @param r the Reminder to be deleted.
     * @return true if deleted, false otherwise
     */
    public boolean deleteReminder(Reminder r) {

        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + r.getId(), null) > 0;
    }

    /**
     * Return a Cursor over the list of all reminders in the database.
     * This is more of a convenience function, as we don't allow outside
     * classes to use it anymore
     *
     * @return Cursor over all reminder
     */
    private Cursor fetchAllRemindersCursor() {

        return mDb.query(DATABASE_TABLE, new String[] {
        			KEY_ROWID,
        			KEY_TITLE,
        			KEY_STARTDATE,
        			KEY_ENDDATE,
        			KEY_MODIFIED
        			}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the reminder that matches the given rowId
     * Again, a convenience function, as we don't allow outside classes ot use it
     *
     * @param rowId id of reminder to retrieve
     * @return Cursor positioned to matching reminder, if found
     * @throws SQLException if reminder could not be found/retrieved
     */
    private Cursor fetchReminderCursor(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, DATABASE_TABLE, new String[] {
            			KEY_ROWID,
            			KEY_TITLE,
            			KEY_STARTDATE,
            			KEY_ENDDATE,
            			KEY_MODIFIED
            			}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * convertStringToDate will take a Sqlite String and convert it to a date
     * @param s String to convert to Date
     * @return Date object
     */
	private Date convertStringToDate(String s){
		Date date = null;
		DateFormat iso8601Format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			date = iso8601Format.parse(s);
		} catch (ParseException e) {
			Log.e("Reminder.convertStringToDate", "Parsing ISO8601 datetime failed", e);
		}
		return date;
	}

	/**
	 * convertDateToString will take a Date object and return a SQLite date string
	 * @param date Date
	 * @return String
	 */
	private String convertDateToString(Date date){
		long when = date.getTime();
		int flags = 0;
		flags |= android.text.format.DateUtils.FORMAT_SHOW_TIME;
		flags |= android.text.format.DateUtils.FORMAT_SHOW_DATE;
		flags |= android.text.format.DateUtils.FORMAT_ABBREV_MONTH;
		flags |= android.text.format.DateUtils.FORMAT_SHOW_YEAR;

		String finalDateTime = android.text.format.DateUtils.formatDateTime(mCtx,
				when + TimeZone.getDefault().getOffset(when), flags);

		return finalDateTime;

	}

    /**
     * DatabaseHelper basically copied from Androids NotebookExample.
     * Here we short-circuit some functions to make our above code
     * more readable.
     */
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_NAME);
            onCreate(db);
        }
    }

}