42

Solutions

Web Apps. Mobile Apps. Awesome!

Blog

Easier Android Databases

October 24, 2011
by

This post was originally written by partner Colin Andrew Ferm as part of the “Developer Diary” series at SexyToiletParty, a social app for adults who like to share what they’re doing. Despite what it was written for, the content is a standard tutorial that people might find interesting.

I know, I know, those Android boys keep trying to do things to make database work easier and more common sense. But the fact is… it’s still not really that good. The ContentResolver, for instance, seems like a nice design pattern until you get more than a few tables working in one app.

Then what?

Then you’re talking about the pain of marshaling all that data yourself, either from the Cursor object returned or into your own objects. And what a pain in the ass that is.

When developing SexyToiletParty, we decided to go a route of our own through a design pattern of our own to make things a little easier. We call it the PersistableBean.

All of our database tables are essentially representations of a single object, with foreign keys to other tables being represented as integers for ease of use. Yes, calling a related object means two database hits instead of one but we only have to make that call if we need that data and saves us the massive pain of custom views being pulled from the database.

Anyway, because this is how we model our data, it’s relatively easy for us to marshal the data into objects. And, if every object is built to the same pattern, each object can be used in pretty much the same way. In this post, I’ll be using the example of our Checkin object to demonstrate our pattern.

 

Keeping Information Together

One can define tables and fields anywhere in the app. But if all of it relates to one data object, why not keep it there? That’s what we do with constants at the top of each class. Take a look at Checkin:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
public class Checkin extends GenericDataObject implements Parcelable {
    public static final String PLACE = "place_id";
    public static final String PLACE_KEY="place_key";
    public static final String PLACE_NAME="place_name";
    public static final String PLACE_LATITUDE="place_latitude";
    public static final String PLACE_LONGITUDE="place_longitude";
    public static final String EVENT = "event_id";
    public static final String EVENT_PARENT = "event_parent";
    public static final String EVENT_KEY = "event_key";
    public static final String TIMESTAMP = "event_time";
    public static final String NOTES = "notes";
    public static final String RATING = "rating";
    public static final String PROFILE_ID = "profile_id";
    public static final String USER_DISPLAY_NAME="user_display_name";
    public static final String COMMENT_COUNT = "comment_count";
 
    public static final String TABLE_NAME = "checkin";
 
    public static final String CREATE = "CREATE TABLE " + Checkin.TABLE_NAME + " ( " + 
                                        Checkin.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                        Checkin.EXT_ID + " TEXT, " + 
                                        Checkin.PLACE + " INTEGER, " +
                                        Checkin.PLACE_KEY + " TEXT," +
                                        Checkin.PLACE_NAME + " TEXT," +
                                        Checkin.EVENT + " INTEGER, " +
                                        Checkin.EVENT_KEY + " TEXT," +
                                        Checkin.EVENT_PARENT + " TEXT, " +
                                        Checkin.TIMESTAMP + " LONG, " +
                                        Checkin.NOTES + " TEXT, " +
                                        Checkin.RATING + " INTEGER, " +
                                        Checkin.PROFILE_ID + " INTEGER," +
                                        Checkin.USER_DISPLAY_NAME + " TEXT," +
                                        Checkin.PLACE_LATITUDE + " REAL, " +
                                        Checkin.PLACE_LONGITUDE + " REAL, " +
                                        Checkin.COMMENT_COUNT + " INTEGER, " +
                                        Checkin.CREATE_DATE + " LONG, " +
                                        Checkin.MOD_DATE + " LONG) ";
 
    public static final String DROP = Checkin.DROP_TABLE + Checkin.TABLE_NAME;
public class Checkin extends GenericDataObject implements Parcelable {
	public static final String PLACE = "place_id";
	public static final String PLACE_KEY="place_key";
	public static final String PLACE_NAME="place_name";
	public static final String PLACE_LATITUDE="place_latitude";
	public static final String PLACE_LONGITUDE="place_longitude";
	public static final String EVENT = "event_id";
	public static final String EVENT_PARENT = "event_parent";
	public static final String EVENT_KEY = "event_key";
	public static final String TIMESTAMP = "event_time";
	public static final String NOTES = "notes";
	public static final String RATING = "rating";
	public static final String PROFILE_ID = "profile_id";
	public static final String USER_DISPLAY_NAME="user_display_name";
	public static final String COMMENT_COUNT = "comment_count";

	public static final String TABLE_NAME = "checkin";

	public static final String CREATE = "CREATE TABLE " + Checkin.TABLE_NAME + " ( " + 
										Checkin.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
										Checkin.EXT_ID + " TEXT, " + 
										Checkin.PLACE + " INTEGER, " +
										Checkin.PLACE_KEY + " TEXT," +
										Checkin.PLACE_NAME + " TEXT," +
										Checkin.EVENT + " INTEGER, " +
										Checkin.EVENT_KEY + " TEXT," +
										Checkin.EVENT_PARENT + " TEXT, " +
										Checkin.TIMESTAMP + " LONG, " +
										Checkin.NOTES + " TEXT, " +
										Checkin.RATING + " INTEGER, " +
										Checkin.PROFILE_ID + " INTEGER," +
										Checkin.USER_DISPLAY_NAME + " TEXT," +
										Checkin.PLACE_LATITUDE + " REAL, " +
										Checkin.PLACE_LONGITUDE + " REAL, " +
										Checkin.COMMENT_COUNT + " INTEGER, " +
										Checkin.CREATE_DATE + " LONG, " +
										Checkin.MOD_DATE + " LONG) ";

	public static final String DROP = Checkin.DROP_TABLE + Checkin.TABLE_NAME;

All of our database fields are described in one place, as is our table create statement and drop statement. Even the table name! This way, if we have to make any changes to the structure of the table, we do it here and it’s easy to recreate.

Sharp eyed readers will notice two things, that ID, EXT_ID, CREATE_DATE, and MOD_DATE are not defined here and also that the class implements Parcelable. I’ll get to the second further down in this post.

The reason is, simply, that the class extends GenericDataObject which contains some generic fields used across all our persistable classes.

Also, we use constants for everything for constistancy when being referenced and so we’re not instantiating short-lived String objects all the time. There are pluses and minuses but we’ve found overall more pluses.

Marshaling Data

The marshaling of data, once all the fields are in one place becomes much easier. And to do this, we always implement two static methods, createObject and createObjects. See an example below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
    public static Checkin createObject(Cursor cur) {
        int idCol = cur.getColumnIndex(ID);
        int extIdCol = cur.getColumnIndex(EXT_ID);
        int placeCol = cur.getColumnIndex(PLACE);
        int placeKeyCol = cur.getColumnIndex(PLACE_KEY);
        int placeNameCol = cur.getColumnIndex(PLACE_NAME);
        int eventCol = cur.getColumnIndex(EVENT);
        int eventKeyCol = cur.getColumnIndex(EVENT_KEY);
        int parentCol = cur.getColumnIndex(EVENT_PARENT);
        int timeCol = cur.getColumnIndex(TIMESTAMP);
        int noteCol = cur.getColumnIndex(NOTES);
        int ratingCol = cur.getColumnIndex(RATING);
        int createCol = cur.getColumnIndex(CREATE_DATE);
        int modCol = cur.getColumnIndex(MOD_DATE);
        int profileIdCol = cur.getColumnIndex(PROFILE_ID);
        int userDisplayNameCol = cur.getColumnIndex(USER_DISPLAY_NAME);
        int longitudeCol = cur.getColumnIndex(PLACE_LONGITUDE);
        int latitudeCol = cur.getColumnIndex(PLACE_LATITUDE);
        int commentCol = cur.getColumnIndex(COMMENT_COUNT);
        
        Checkin checkin = new Checkin();
        checkin.placeId = cur.getInt(placeCol);
        checkin.placeKey = cur.getString(placeKeyCol);
        checkin.placeName = cur.getString(placeNameCol);
        checkin.eventId = cur.getInt(eventCol);
        
        checkin.checkinTime = cur.getLong(timeCol);
        checkin.rating = cur.getInt(ratingCol);
        checkin.notes = cur.getString(noteCol);
        checkin.eventParent = cur.getString(parentCol);
        checkin.eventKey = cur.getString(eventKeyCol);
        checkin.profileId = cur.getInt(profileIdCol);
        checkin.userDisplayName = cur.getString(userDisplayNameCol);
        
        checkin.latitude = cur.getDouble(latitudeCol);
        checkin.longitude = cur.getDouble(longitudeCol);
        
        checkin.commentCount = cur.getInt(commentCol);
        
        checkin.setId(cur.getInt(idCol));
        checkin.setExtId(cur.getString(extIdCol));
        checkin.setModifiedDate(cur.getLong(modCol));
        checkin.setCreateDate(cur.getLong(createCol));
        
        return checkin;
    }
    
    public static List<Checkin> createObjects(Cursor cur) {
        List<Checkin> checkins = new ArrayList<Checkin>();
        cur.moveToFirst();
        do {
            Checkin checkin = Checkin.createObject(cur);
            checkins.add(checkin);
        } while (cur.moveToNext());
        
        return checkins;
    }
	public static Checkin createObject(Cursor cur) {
		int idCol = cur.getColumnIndex(ID);
		int extIdCol = cur.getColumnIndex(EXT_ID);
		int placeCol = cur.getColumnIndex(PLACE);
		int placeKeyCol = cur.getColumnIndex(PLACE_KEY);
		int placeNameCol = cur.getColumnIndex(PLACE_NAME);
		int eventCol = cur.getColumnIndex(EVENT);
		int eventKeyCol = cur.getColumnIndex(EVENT_KEY);
		int parentCol = cur.getColumnIndex(EVENT_PARENT);
		int timeCol = cur.getColumnIndex(TIMESTAMP);
		int noteCol = cur.getColumnIndex(NOTES);
		int ratingCol = cur.getColumnIndex(RATING);
		int createCol = cur.getColumnIndex(CREATE_DATE);
		int modCol = cur.getColumnIndex(MOD_DATE);
		int profileIdCol = cur.getColumnIndex(PROFILE_ID);
		int userDisplayNameCol = cur.getColumnIndex(USER_DISPLAY_NAME);
		int longitudeCol = cur.getColumnIndex(PLACE_LONGITUDE);
		int latitudeCol = cur.getColumnIndex(PLACE_LATITUDE);
		int commentCol = cur.getColumnIndex(COMMENT_COUNT);
		
		Checkin checkin = new Checkin();
		checkin.placeId = cur.getInt(placeCol);
		checkin.placeKey = cur.getString(placeKeyCol);
		checkin.placeName = cur.getString(placeNameCol);
		checkin.eventId = cur.getInt(eventCol);
		
		checkin.checkinTime = cur.getLong(timeCol);
		checkin.rating = cur.getInt(ratingCol);
		checkin.notes = cur.getString(noteCol);
		checkin.eventParent = cur.getString(parentCol);
		checkin.eventKey = cur.getString(eventKeyCol);
		checkin.profileId = cur.getInt(profileIdCol);
		checkin.userDisplayName = cur.getString(userDisplayNameCol);
		
		checkin.latitude = cur.getDouble(latitudeCol);
		checkin.longitude = cur.getDouble(longitudeCol);
		
		checkin.commentCount = cur.getInt(commentCol);
		
		checkin.setId(cur.getInt(idCol));
		checkin.setExtId(cur.getString(extIdCol));
		checkin.setModifiedDate(cur.getLong(modCol));
		checkin.setCreateDate(cur.getLong(createCol));
		
		return checkin;
	}
	
	public static List<Checkin> createObjects(Cursor cur) {
		List<Checkin> checkins = new ArrayList<Checkin>();
		cur.moveToFirst();
		do {
			Checkin checkin = Checkin.createObject(cur);
			checkins.add(checkin);
		} while (cur.moveToNext());
		
		return checkins;
	}

As you can see createObjects just loops through the Cursor until reaching the end and creates a list of Checkin objects generated from the createObject method. Simple, right?

Wait until you see how we unmarshal:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
    public ContentValues getValues() {
        ContentValues vals = super.getValues();
        vals.put(Checkin.PLACE, this.placeId);
        vals.put(Checkin.PLACE_KEY, this.placeKey);
        vals.put(Checkin.PLACE_NAME, this.placeName);
        vals.put(Checkin.EVENT, this.placeId);
        vals.put(Checkin.EVENT_KEY, this.eventKey);
        vals.put(Checkin.EVENT_PARENT, this.eventParent);
        vals.put(Checkin.TIMESTAMP, this.checkinTime);
        vals.put(Checkin.RATING, this.rating);
        vals.put(Checkin.PROFILE_ID, this.profileId);
        vals.put(Checkin.EXT_ID, this.ext_id);
        vals.put(Checkin.PLACE_LATITUDE, this.latitude);
        vals.put(Checkin.PLACE_LONGITUDE, this.longitude);
        vals.put(Checkin.COMMENT_COUNT, this.commentCount);
        if (this.userDisplayName != null&&!this.userDisplayName.equals("")) vals.put(Checkin.USER_DISPLAY_NAME, this.userDisplayName);
        if (this.notes != null) vals.put(Checkin.NOTES, this.notes);
        return vals;
    }
	public ContentValues getValues() {
		ContentValues vals = super.getValues();
		vals.put(Checkin.PLACE, this.placeId);
		vals.put(Checkin.PLACE_KEY, this.placeKey);
		vals.put(Checkin.PLACE_NAME, this.placeName);
		vals.put(Checkin.EVENT, this.placeId);
		vals.put(Checkin.EVENT_KEY, this.eventKey);
		vals.put(Checkin.EVENT_PARENT, this.eventParent);
		vals.put(Checkin.TIMESTAMP, this.checkinTime);
		vals.put(Checkin.RATING, this.rating);
		vals.put(Checkin.PROFILE_ID, this.profileId);
		vals.put(Checkin.EXT_ID, this.ext_id);
		vals.put(Checkin.PLACE_LATITUDE, this.latitude);
		vals.put(Checkin.PLACE_LONGITUDE, this.longitude);
		vals.put(Checkin.COMMENT_COUNT, this.commentCount);
		if (this.userDisplayName != null&&!this.userDisplayName.equals("")) vals.put(Checkin.USER_DISPLAY_NAME, this.userDisplayName);
		if (this.notes != null) vals.put(Checkin.NOTES, this.notes);
		return vals;
	}

Again, simple. And, because it extends GenericDataObject the super method handles the shared fields.

By using this design pattern, then, it becomes a snap when actually coding the app itself to use the data. No more manual binding of data, each time you need data.

The only downside to using this method is that the projection returned from the table must always be the full projection and not a subset or the marshaling done in createObject will throw an exception. But, aside from that, you have a fully populated object available for use as any other.

 

In-App Data

Two Activities within an Android app often need to communicate with one another. And, just as often, they need to share data between them. And how often is that the exact same data?

In SexyToiletParty, check-ins especially need to get passed back and forth with regularity. So how to do this?

One method is just to pass the id of a checkin in the extras bundle of the calling Intent. Then, each Activity can fetch the freshest representation of the data from the database. But databases take time. And time means AsyncTasks. And this mean showing a loading spinner or something while the data is fetched.

But what if an Activity doesn’t need the freshest data? What if it just needs anything, like the primary key of the check-in as well as the id of the user profile who made it. And maybe the way it was ranked?

Sure, each of these values could be passed in the Bundle but what a pain in the ass. Now, rather than doing manual marshaling and unmarshaling to and from the database, you’re doing it to and from Bundles. It’d be easier to just pass the whole damn object, wouldn’t it?

Here’s were slogging through the Android documentation really paid off.

Bundles can accept an object that implements the Parcelable interface. With a little bit of extra code on your part, Android will take care of passing all the data around.

So… how do you do it?

First, the method writeToParcel needs to be implemented:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    public void writeToParcel(Parcel out, int flags) {
        out.writeInt(this.placeId);
        out.writeString(this.placeKey);
        out.writeString(this.placeName);
        out.writeString(this.placeAddress_1);
        out.writeString(this.placeAddress_2);
        out.writeInt(this.eventId);
        out.writeString(this.eventKey);
        out.writeString(this.eventParent);
        out.writeLong(this.checkinTime);
        out.writeString(this.notes);
        out.writeInt(this.rating);
        out.writeInt(this.profileId);
        out.writeString(this.userDisplayName);
        out.writeDouble(this.latitude);
        out.writeDouble(this.longitude);
        out.writeString(this.eventName);
        out.writeInt(this.commentCount);
        out.writeInt(this.id);
        out.writeString(this.ext_id);
    }
	public void writeToParcel(Parcel out, int flags) {
		out.writeInt(this.placeId);
		out.writeString(this.placeKey);
		out.writeString(this.placeName);
		out.writeString(this.placeAddress_1);
		out.writeString(this.placeAddress_2);
		out.writeInt(this.eventId);
		out.writeString(this.eventKey);
		out.writeString(this.eventParent);
		out.writeLong(this.checkinTime);
		out.writeString(this.notes);
		out.writeInt(this.rating);
		out.writeInt(this.profileId);
		out.writeString(this.userDisplayName);
		out.writeDouble(this.latitude);
		out.writeDouble(this.longitude);
		out.writeString(this.eventName);
		out.writeInt(this.commentCount);
		out.writeInt(this.id);
		out.writeString(this.ext_id);
	}

Take note of the order everything is written out in. This order needs to be exactly the same when reading in when creating the new constructor that is also required:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    public Checkin(Parcel in) {
        this.placeId = in.readInt();
        this.placeKey = in.readString();
        this.placeName = in.readString();
        this.placeAddress_1 = in.readString();
        this.placeAddress_2 = in.readString();
        this.eventId = in.readInt();
        this.eventKey = in.readString();
        this.eventParent = in.readString();
        this.checkinTime = in.readLong();
        this.notes = in.readString();
        this.rating = in.readInt();
        this.profileId = in.readInt();
        this.userDisplayName = in.readString();
        this.latitude = in.readDouble();
        this.longitude = in.readDouble();
        this.eventName = in.readString();
        this.commentCount = in.readInt();
        this.id = in.readInt();
        this.ext_id = in.readString();
    }
	public Checkin(Parcel in) {
		this.placeId = in.readInt();
		this.placeKey = in.readString();
		this.placeName = in.readString();
		this.placeAddress_1 = in.readString();
		this.placeAddress_2 = in.readString();
		this.eventId = in.readInt();
		this.eventKey = in.readString();
		this.eventParent = in.readString();
		this.checkinTime = in.readLong();
		this.notes = in.readString();
		this.rating = in.readInt();
		this.profileId = in.readInt();
		this.userDisplayName = in.readString();
		this.latitude = in.readDouble();
		this.longitude = in.readDouble();
		this.eventName = in.readString();
		this.commentCount = in.readInt();
		this.id = in.readInt();
		this.ext_id = in.readString();
	}

Lastly, two helper methods need to be created to help Android do the marshaling and unmarshaling:

1
2
3
4
5
6
7
8
9
10
11
12
13
    public int describeContents() {
        return 0;
    }
 
    public static final Creator<Checkin> CREATOR = new Creator<Checkin>() {
         public Checkin createFromParcel(Parcel source) {
             return new Checkin(source);
         }
 
         public Checkin[] newArray(int size) {
             return new Checkin[size];
         }
    };
	public int describeContents() {
		return 0;
	}

	public static final Creator<Checkin> CREATOR = new Creator<Checkin>() {
		 public Checkin createFromParcel(Parcel source) {
			 return new Checkin(source);
		 }

		 public Checkin[] newArray(int size) {
			 return new Checkin[size];
		 }
	};

With just that little bit of extra code, objects implementing the Parcelable interface can be passed around between Activities like freak’n Chinese throwing stars.

One tip for best practice though, if an activity is going to update a record in the database, it should probably call the database first to get the freshest information and then update based on that. But if an activity is merely displaying some information or using parts of the object to fetch other information, then you’ve just saved yourself one database hit and hours of debugging to figure out what’s not getting passed where.

 

Summing Up

There’s no way around the fact that accessing large amounts of data through Android’s sqlite implementation is a pain in the ass. Hibernate it is not. But by laying a little groundwork ahead of time, hours of data work and debugging can be saved and spent doing something actually productive, like building your app.

I hope these tips I’ve shared help other developers out there. Because there’s a reason we developed this design pattern. You know it, and we know it.

Back to Blog

42 Tags: , , , , ,

Comments

Content for class "bottomYellow" Goes Here
Content for class "bottomBlue" Goes Here

42 Solutions

Working with 42 Solutions, you’ll find that it’s not the technology that’s important, it’s the idea. Regardless of whether your company is based on proprietary software or open source, we will find the right tools to get the job done.

Invite us to your office and we’ll send a pair of consultants armed with laptops and bright ideas, ready to solve your problem and offer solutions. We pride ourselves in getting to know how you do business and integrating into your process.