Database Access in Android

Question

I am creating an android app that is basically a listing of information on Mushrooms. I get this information from an sqlite database. I have a global singleton with a services class inside it in which I use to access my db. Almost every activity accesses the db. Is it better to leave my db open all the time or open and close it as I need the data?

If the best practice is to leave it open all the time, where do I need to make sure to close it and what is the worst case scenario if I left it open when the activity was destroyed?

Answer 1

Your best option here is to refactor so that your application accesses the database via a ContentProvider. Your ContentProvider implementation is the only thing with a handle to the database open.

This gives you several advantages:

• only one thing has the database open, so your problem just goes away.
• lots of standard support classes to automate stuff like database management.
• better integration with the standard Android list-management views, which are all designed to work automatically with cursors provided by ContentProviders.
• All your data can be addressed by URI (typically of the form 'content://com.fnord.mushroom/mushroom/43'), which means that other applications can access your data too.

Using a ContentProvider, it's possible to glue three or four standard classes together to produce a browser interface to your database and never actually have to write any real logic.

On the negative side, ContentProviders only really support access via a limited interface --- in SQL terms, you get INSERT, SELECT, UPDATE and DELETE with no nested clauses. If you're doing complicated SQL stuff it can be a bit painful to route requests from your app to the ContentProvider and back again. However, most people don't need to do this (and if you do, custom intents are the way to go).

Answer 2

I would open the db as needed. That way you know for sure the connection is closed once the particular activity that opened it is finished. Although Android has built in checks to make sure it closes upon application termination, it doesnt hurt to be on the safe side. I'm also guessing having it open all the time could cause leaks or something.