Database Access in Android

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).

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.

Based on my past experience in Java I would say it is better to close the connection, it probably doesn't matter in a small Android application, but if you have 10 applications running and all of them access the database, you have 10 pending connections. Start a few more and sooner or later another application will have to wait because the SQL server can't handle any more requests.

I guess you could think of it as a file on your computer. You read data from it, and then close it when your done. Why keep a file open in your application?

Now I'm very new to Android programming so I haven't got around to implement database calls. But when I faced the same problem in a Java application a few years ago I implemented a database object, in which I had the connection to the database. "Everyone else" (the classes) had to call the database object (singleton or final methods) to get data, sort of like stored procedures but in the application instead.

Because of this I knew when the calls where made and when they stopped. I then put in a timeout, so as if nothing happened in a few minutes, I would close the connection to the db. (This also took care of some timeout exceptions because the timeout of the connection would never happen.) When a new call entered, I could easily start a new connection and use the new db connection.

Basically I abstracted away SQL calls by having methods as public Fungus[] getAllFungus() and public Fungus[] getFilteredFungus(string where).