Trying out OrmLite by migrating LogMyLife #androiddev

In addition to ActiveAndroid I also took a look at OrmLite for Android. It’s a little larger than ActiveAndroid, but doesn’t dwarf my project like DB4O would. And the license is right for an open source dude like myself. Also it looks like it covers more functionality, also allowing you to get at the native DB connection (which doesn’t look possible with ActiveAndroid). And you aren’t required to subclass anything, though convenience base classes are provided. All good stuff.

When I tried out the demo project (which I also used in my TriDroid presentation), it looked reasonably straightforward. So just now I downloaded the JAR to take a look at what it would look like to convert LogMyLife to use OrmLite. What I didn’t notice is that the demo project is based on version 4.6, while the current version has jumped up to 4.10 a few days ago, which is apparently a significant release. I figured out things were different pretty quickly, though, because the first time I tried to annotate a DTO, the @DatabaseTable annotation couldn’t be found. I needed to download and link the ormlite-core JAR in addition to the ormlite-android JAR (which would be all that’s needed in previous releases). I suppose if I were using a maven POM to manage my build this would all be taken care of… but I haven’t gotten around to that.

I hope the docs are still reasonably accurate… I might be a little bit of a guinea pig for this release. We’ll see.

Bringing in the OrmLite helper

So I just went down a long rabbit trail about where exactly to put the OrmLite plumbing. The examples all have a helper that you have to keep track of per-Activity. But didn’t I just demonstrate for myself that an application-wide DB handle worked fine? So I’m thinking about just putting the plumbing in my Application subclass and seeing what happens. The complication seems to be multi-threading – the Google Group had a discussion pointing to this really interesting article on concurrent Sqlite access. I don’t really have multiple threads accessing the DB in LogMyLife, with the possible exception of my broadcast handlers – I’m not actually sure if they come in as a separate thread, or even a separate process, or what. That’s something for me to figure out soon. In the meantime, I guess I’ll go ahead with my scheme to try it in my Application class.

So first, I have to define a helper with callbacks for when I’m opening a connection and need to make sure the schema is in place, which needs to subclass OrmLiteSqliteOpenHelper (which subclasses the SqliteOpenHelper I’m used to) and define what to do in when the DB is being created or updated. Looks like it can also define some simple interactions for getting DAOs. This needs to be configured into the OpenHelperManager which is used to actually create my helper in context; the method of configuration is either to configure a string in my strings.xml and let magic happen behind the scenes, or to explicitly set a factory object on OHM that will return my helper. As it’s not much trouble and (I think) clearer, I do the latter.

Nuts and bolts

Soon I run into a little issue. OrmLite refers to SQLException all over the place, but it’s referring to java.sql.SQLException, generally used for JDBC, as opposed to the android.database.SQLException that’s usually used for Sqlite exceptions. It would be a nuisance just to have to be conscious of which is relevant. But the real difference is that the android.database.SQLException is a runtime exception, while java.sql.SQLException is a checked exception, so I have to deal with it somehow in any of my code that uses the DB. This forces me to re-think exception handling. Checked vs. un-checked exceptions is kind of an old argument, but I thought after all our experiences with Java the scales were starting to tip toward unchecked? As far as I know Java is the only major language using checked exceptions widely; the dynamic languages gaining popularity don’t really even have the ability to use them. So it’s kind of disheartening to see this throwback; although I imagine it arises from the JDBC nature of the rest of the OrmLite package.

Now I go to create a Dao by subclassing BaseDaoImpl, and I’m given a choice of four different constructors (but must include one). The most obvious-seeming one is evidently to be used with a DI container, so I look at the other ones. Eventually I realize that it’s probably best to use the one that’s like the generic DAO constructors, e.g. BaseDaoImpl.createDao(getConnectionSource(), Tracker.class).

While I’m working on the numerous errors Eclipse is reporting from my brutal refactoring, I realize that my BroadcastReceivers don’t have access to my Application subclass, so they have their own code for instantiating the DB connection. There’s no helper base class for them – reasonable, given they don’t really have a life-cycle. Suddenly just defining a string looks like a much nicer path for configuring the helper class.

An example query

And now I come up against one of my first query refactorings. I have a method like so in my existing domain code:

public Alert fetchNextAlert() {
 return fetchAlert(
  db_ALERT_ENABLED + " = 1 AND " + db_ALERT_SKIP_NEXT + " != 1 AND " +
  db_ALERT_NEXT_TIME + " > datetime('now', 'localtime')",
  null, null, null, db_ALERT_NEXT_TIME + " ASC", "1"
 );
}

fetchAlert takes these raw SQL constraints and turns them into a query for a single row from the Alerts table, then converts that row into an Alert DTO. It’s all fairly ugly code, to be sure. Here’s my first crack at a replacement in my OrmLite DAO:

public Alert fetchNextAlert() throws SQLException { 
 return queryForFirst(queryBuilder()
  .orderBy(C.db_ALERT_NEXT_TIME, true)
  .limit(1)
  .where().eq(C.db_ALERT_ENABLED, true)
  .and().ne(C.db_ALERT_SKIP_NEXT, true)
  .and().gt(C.db_ALERT_NEXT_TIME, new Date())
  .prepare());    
 }

Now this is reasonably clear and concise, and in line with how other ORM libraries work. But one thing troubles me: as far as I can tell, there’s no way to get raw SQL in as part of a where clause; I couldn’t duplicate the ” > datetime(‘now’, ‘localtime’)” constraint, and in my experience, when it comes to time, you want to speak the DB’s language. But perhaps I’m wrong this time – certainly the code above captures the intent. If absolutely needed, I could run a raw query to return the ID of the Alert I want, then just use standard DAO methods to turn that into an Alert DTO; two queries instead of one, though.

I have reasonably-sized swaths of code compiling at this point; time for a break.