Android: best practices for SQLite DB and ContentProvider

Question

My Android app is reading and writing to a local SQLite DB from a few different Activities and a Service. Pretty standard. But I'm not happy with the way I've got all the DB details stored as constants that I then use anywhere I access the DB. I've been advised to wrap the DB in a ContentProvider. Sounds good to me. While I'm refactoring my code, I figured I'd ask:

• What are your best practices for local DB data storage in Android?
• Where and how do you store "CREATE TABLE" statements, column names, other SQL?
• Would you mind sharing a list of the classes you instantiate and what goes into each (ContentProvider, DatabaseProvider, DatabaseHelper...)?
• How do you coordinate the structure of your local Android DB with a server-side DB available through a REST interface?

Yeah, I realize I'm getting at the perennial "where's the Android object-relation-mapping framework?" question. For now, I'm mainly curious to hear how you structure your Android apps with what's available in the standard SDK.

As always, thanks for the pointers!

Answer 1

I don't know that I have an answer, other than I don't really like how this is handled, I find it very messy as well. I usually follow the pattern given in the notepad example that comes w/ the SDK.

Due to this I am working on my own mini ORM framework, using annotation and managing all of this. So far things are working ok, but I haven't worked everything out yet.

Answer 2

For now, I'm mainly curious to hear how you structure your Android apps with what's available in the standard SDK.

I'm not a real fan of SQL and the way that it's handled in android, so I use the object database NeoDatis. It basically just lets you store / retrieve Java objects into a flat file stored on the device very easily. db40 is also another alternative Object Database that will work on android.

Haven't had any issues using this approach, you may want to note that including the NeoDatis library will increase your APK size by ~700kb.

Answer 3

There's an ORM that will handle Android's db nastiness. There's a trial and sample project here: https://www.activeandroid.com/help/sample-project/

Answer 4

[ I'll add my $0.02 for posterity. ]

We have been tuning ORMLite (http://ormlite.sourceforge.net/) on Android for a while now and it is working well. ORMLite supports Android with native database calls and also supports other databases via JDBC. You annotate your classes/fields and use base DAO classes to persist to SQLite.

• CREATE TABLE statements are handled my ORMLite's utility classes. Most SQL is taken care of by the DAO classes.
• The Android section of the online documentation explains the class hierarchy. You implement a DatabaseHelper which helps create an update your database. Your activities extend OrmLiteBaseActivity (or service or tab) which gives access to helper and DAOs.
• ORMLite does not provide a solution for merging with remove REST servers.

Hope this is somewhat helpful.