Android when should I be opening and closing db connections?

Question

Overview: Opening db connections in the onStart and in async tasks has gotten to be quite complex. Is it bad practice to have globally available db connections? If it is bad what is a better way?

Details: I have an application that connects to the sqlite3 database in several activities. At first there weren't many places where I needed to access the database so I was just opening and closing each time I needed access. Then there were more places that needed to access it so, as suggested on another Stack Overflow question, I started opening the database connections in the onStart method of the activity that needed a connection and closing it in the onStop method.

This worked fine until I started needing connections in some asynchronous tasks that outlived the activity. Since the onStop method for the activity had been called and connections had been closed, by the time the async task tried accessing the database it was failing. As a solution I created separate connections for each async task that were opened in the onPreExecute method and closed in the onPostExecute method.

This has resulted in a lot of opening and closing of connections, and I'm wondering if creating globally available db connections in the application context is a good idea. It would definitely clean up a lot of code and remove any unclosed db exceptions that are happening if I forget to close a connection or the app experiences a force close. Any one else tried this / see any problems with this approach?

Answer 1

I ran into similar problems some weeks ago. I use several classes that have persistence in a SQLite db.

As I wanted to decouple the activities from persistence I created static inner classes (called Managers) for those that need persistence . Each time I instantiate a Manager a db connection is created and after using the manager I explicitly close it. If I had global managers to access the tables, they should be synchronized due to concurrency and I do not know when I should close those db connections. So I did it this way.

Hope this helps!