Handling SQLite tables and cursors on Android

Question

I'm having a hard time to optimize backwards compatibility, complexity and best practice for SQLite database handling on Android. I found two not deprecated ways to manage SQLite databases and cursors:

• Directly through android.database.sqlite
• ContentProvider, CursorLoader and LoaderManager

I'm trying to design the database implementation future proof. This means I would like to implement a best practice promoted by Google. I found a tutorial on implementing ContentProvider and LoaderManager.

If I follow Lars Vogels proposals, my code is blown up with duplications and unnecessary complexity. It does make sense for some tables in my database. But it would make no sense to implement this for a mapping table with three fields (for example). Furthermore I'm having problems with ActionbarSherlock and the Callback Interface of LoaderManager (there's a solution but it would double my data handling classes).

Direct handling of database and cursors via android.database.sqlite is provoking problems with resource management (close your cursors!) and makes me responsible for task handling.

My Questions:
How are you handling SQLite databases on Android?
When do you go the extra mile and implement ContentProvider and LoaderManager?
How do you stay backwards compatible?

My current approach:
I created a class that separates database I/O (via android.database.sqlite) from activities. All methods open and close the cursors they use during their execution (outside of my activities) and return the objects or the data as needed (instead of a cursor). I/O operations are performed in AsyncTasks. This approach seems very deprecated.

Answer 1

I recently had the same plain sqllite / content provider dilemma, and it looks like content provider are the most common approach to solve the problem.

Even if the official doc states that

You don't need to develop your own provider if you don't intend to share your data with other applications

they added the option to have unexported content providers using

android:exported="false"

All the books I read, including Reto Meier's Professional Android Development 4, suggest using content providers.

Moreover, at the cost of a lot of boilerplate code, you can forget about multithreading and open cursor issues.

Anyway, I have to say that the biggest advantage you get from the content provider / cursor loader combo is that your loader is automatically notified whenever the underlying data changes.

If you use plain sqllite you need to implement some way to get the client classes notified whenever the data changes in background. For example, I used broadcasts to notify any activity that the tables where updated inside an intentservice.

Finally, I was a bit disappointed by all the code duplication that you are ranting about, and I decided to write a python script to generate the content provider in my place using just a description of the data model. You probably have to modify the generated class (or better, to extend it), but I think it saves quite a lot of time. You can find it here

Conclusion:

• if you want to export your data to other apps (not so common), you need to go for content provider
• if you want to observe the data / update the ui because your dataset might be changed in background, content provider + loader is extremely powerful
• if you have a preloaded data set, and maybe you update it in the same activity that display the data, or you need to perform simple operations with your tables, maybe a sqllite helper class is just enough