Android Design Patterns

I am a little confused. I know I need to call

SQLiteDatabase db = mOpenHelper.getReadableDatabase();

inside the extended ContentProvider class, but don't I need to call

db.close();

at some point? Moreover, if I get a cursor

Cursor mCursor = qb.query(db, projectionIn, selection, selectionArgs, null, null, sort);

don't I also need to close it later? What would take care of closing the database and the cursors? Thanks!

Hi Dimath,

Good question!

(1) When working with ContentProviders, it is perfectly fine to leaving the database connection open throughout the entire runtime of your app. ContentProviders (as well as many of the SQLiteDatabase resources associated with it, such as the database instance itself) are created and managed by the system throughout your app's lifecycle. In fact, the whole purpose of the ContentProvider is to abstract these details from the developer. I understand your confusion, as the code suggests that the database is opened once and never closed, but the fact of the matter is that doing so is perfectly acceptable. There are tons of tutorials/sample code on the developer's website that are implemented this way too (if you don't believe me :P).

(2) The same logic does not apply to cursors returned by calls to "query()". Say you made the following call:

Cursor cur = getContentResolver().query(...);

The developer should remember to call "cur.close()" on this cursor when finished. Well, most of the time at least... there are a couple exceptions. For instance, if you called "startManagingCursor(cur)" (which is deprecated as of API 11... don't use it!), you should stop managing cursor with the trivially named "stopManagingCursor(cur)" method instead. You also don't want to call "cur.close()" if you have associated your cursor with the LoaderManager (as mentioned in my post). The system will close the cursor automatically for you.

Let me know if I can clarify in any way, and thanks for bringing that up. :)

Alex

This is a great solution. I was experiencing nightmarish "database locked" scenarios with multiple services accessing the database at the same time and the open and close synchronization was driving me mad.

I just implemented your first method, and voila! Everything's magical. Having just one instance throughout the app is super sexy and makes database management a breeze.

With this solution, why would I ever need Content Providers?

Glad the post helped you out, Bhagwad!

There are definitely benefits to using ContentProviders though... don't give up on them too quickly!

Hi, nice post, I think I'll be using your first approach, but reading the Android docs: http://developer.android.com/reference/android/app/Application.html#onTerminate()

It said this method will never be called on a Real device, ¿Is there some particular reason for calling it that I'm missing?

Hi eleanzx, thanks for the comment. You are correct in that onTerminate() is not called on real devices (and if you are only working with real devices, you could probably get away with not including it all together).

The reason I overrided the onTerminate method is:

(1) According to the documentation, the method may be called in "emulated process environments" (i.e. if you are debugging your application with the emulator), so I included it just to be safe. I'm not 100% sure how process cleanup works on the emulator, but it's better to be safe than sorry!

(2) It makes the code a little easier to understand. If I didn't include it, it would look like I was opening the database without ever closing it, which goes against the whole purpose of this blog post. I thought it was less confusing this way. :)

That being said, of course it isn't necessary to close the database in onTerminate. The Application instance represents a global process and nothing more. When an Android app is terminated, its processes are removed by simply killing them. The "leak found" error message is given when the garbage collector finds and frees a SQLiteDatabase instance that has not been closed. Therefore, since the process is killed and all of the process' memory resources are removed and freed at this point, there is no reason to close the database. Again, I mostly just included it for clarity.

Let me know if that clears things up for you,
Alex

Hi Alex,

I think it's worth mentioning, that using the second approach you never need to close the database. It's obvious for you, I suppose, but wasn't that obvious for me and caused me some problems when I accessed my db from many places at once (Fragments in ViewPager).

And a question: is it possible/easy/hard to implement the third approach to retrieve data from local database with LoaderManager and ContentProvider, but to have this data at the same time updated from an external source? Now, I use an approach with two different Loaders (one offline and one online) and it works, but it seems terribly hackish and the code is ugly. Can you suggest anything?

Thanks:)

Yes, that's true. I'm actually thinking of removing approach #1 from the blog post all together... I think #2 is a much better approach. In fact, there are some cases where subclassing Application can cause you trouble. There can only be one subclass of Application, which makes it more difficult for you if you need to use other subclasses of Application later on. Using a static data member avoids that.

Now about your question... if I recall correctly, the CursorLoader will register a ContentObserver with the dataset you requested and will call forceLoad() on itself when the data set changes, correct? As long as your external source is updating the data via your ContentProvider, and your insert and update methods are calling getContentResolver().notifyChange(...) once the transaction has completed, shouldn't this be relatively simple? I don't think there is a need to have two different loaders... I think you just have to make sure your Loader is getting notified when the data set is changed.

There's one important scenario when this approach wouldn't work (or am I wrong?). When the user returns to the app without Internet connection and wants to view data he's previously downloaded (i.e. downloaded from external databse and written to internal).

I mean, if a user has Internet connection all the time, everything is great, data is perceived by the Loader as long as the Loader lives. But I want to keep the data between app restarts, access it as quickly as possible and update online whenever possible.

So one Loader to provide data from ContentProvider (offline), but what should I use to update the data?

The ContentProvider is what you use to insert/update/query data from your internal database. It knows nothing about "offline" or "online" data... it is just there to store the data your application needs to store. The Loader provides asynchronous loading of data and monitors the source of the data, delivering new results when the content changes.

In both cases (online and offline), the user loads the data that is stored in the ContentProvider. The only difference is that if the user is online, the data will be periodically updated by an external source. When the data is downloaded and inserted/updated (by calling the ContentProvider's insert/update method), the ContentProvider will recognize that changes have being made and will tell the Loader to update its data. This way you abstract the difference between "offline" and "online" data and make your code a whole lot easier to deal with. Does that make sense? Or did I misunderstand?

Hey,

just a comment about approach #1, the singleton design pattern.
You have not declared the getInstance() method as synchronized, therefore you can not guarantee a singleton approach in a multi threaded application.

This is a common mistake when using this pattern.
It is either missed out due to forgetfulness or it is missed out because the code inside does some form of lock. Either way the simplest and best approach is a sync lock on the method.

Thanks for the comment, Rob. You are correct. A couple of notes:

If we don't make the static helper method synchronized, a race condition can only ever occur the very first time getInstance is called (since this is the only time when mInstance is null, and thus, the only time that two or more threads can interleave and accidentally initialize more than one instance). In Android programming, this is incredibly rare, as more often than not, the first call to getInstance is made on the main UI thread before multiple threads are introduced in the application.

With that in mind, making the method synchronized is a bit overblown in my opinion. As Rob points out, it will eliminate potential race conditions, but it also means that you will have to pay the cost of synchronization for every invocation of the method, even in situations where synchronization is completely unnecessary.

Therefore, the best solution is to avoid calls to getInstance from multiple threads. Instead, consider holding a reference to the DatabaseHelper in the Activity and/or Fragment, and initialize it in onCreate before multiple threads are introduced.

Thanks again for pointing that out! :)
Alex

Hi Alex,

I can't help but think about the db close() method.

Is it really safe to not close the connection and leave it for garbage collection?

I think lots of others are confused about this as well...

Louis

Hi Louis,

There really is no harm in leaving the database open. SQLite itself promises that the database won’t be corrupted if the process goes down suddenly, even in the middle of an update. This means that the only other real need for closing the database is to ensure that in-process resources (locks, memory buffers, etc.) are cleaned up appropriately — none of which will really matter because the Android system will kill the entire process when the application closes.

Hope that makes sense!
Alex

Alex, why is your sample code hanging onto a reference to a supplied context as a private field in DatabaseHelper? Your example code makes no use of this object reference aside from its initial assignment.

I am wrestling with this idea of using SQLite with a "global application scope". I want to use a singleton, but this whole problem of SQLiteOpenHelper needing a Context really bugs me. I do not -want- to pass a Context to my GetInstance() method (or any other argument, for that matter; I just want to, you know, get the instance).

My question is this: is there some cleaner way to generate a Context for SQLiteOpenHelper that does not involve passing a Context reference to the factory method but which still allows trouble-free access to the same SQLite database across all the activities the app may happen to have? This has really been bothering me :-\

@Gothri

You are correct... I didn't actually need to hold a reference to the context as a class field. I just removed the "private Context mCtx" field from the sample code... thanks for the catch!

You are, however, required to pass the Context as an argument to the static factory method. Without passing a Context as an argument to the method, the DatabaseHelper would have no way of instantiating itself. The same concept applies with adapters, views, and every other class that has its public constructor take a Context as an argument.

tl;dr - The SQLiteOpenHelper cannot acquire a context by itself... it must be passed a Context as a parameter in order to be instantiated.

But come on, man... it's just one argument... it's not that bad! :P

Alex, you're right, it doesn't seem that bad. But here are my beefs:

1) opening or otherwise using a database is not -related- to the Context. You shouldn't -need- a Context to access or use a database because the Context does not have any kind of close relationship to the database.

I mean, I think I know why SQLiteOpenHelper wants a Context. I think it is because SQLiteOpenHelper wants to know where on the device to create the database file. Aside from that, there's no reason for SQLite to have a Context.

2) The Context isn't even used by the class containing the Factory method. The Context is used only when the singleton is created, but for all other uses of getInstance, the Context is passed but never needed or used.

3) If you write an ordinary Java class (i.e. a class outside the Android lifecycle paradigm or subclassed from the SDK) and you want to access an SQLite database, you're screwed ... unless you pass a Context which the Java class does not need for anything except finding the database.

That is what bothers me about passing a Context to the Factory method: it isn't needed, it's only used once, and it's burdensome. I do not understand why Context.getApplicationContext() is not implemented as a static method so that you could at least get the application context without an instance of some Context class or subclass.

It makes me want to extend the Application class just so that I can have the application context on hand somewhere if I need it:

-----

"Many of us want to write some logic outside activity class. But we are stopped from doing this by many reasons, of which one is accessing application context to interact with database. Context is available to us in activity but not in plain java class.

By following the below steps you may access application context statically across your android application :"

1. Modify the android manifest by providing a class (android:name) to application tag

2. Then write the class

public class CustomApplication extends Application{
private static Context context;
public void onCreate(){
context=getApplicationContext();
}

public static Context getCustomAppContext(){
return context;
}
}

@Gothri

1) You are correct in that the SQLiteOpenHelper needs the Context to know where to store the SQLite database on the disk.

2) How is subclassing Application any better? In my opinion passing the Context as a parameter to an argument is WAY cleaner/simpler than subclassing Application, don't you agree? If I understand correctly, you are trading a bunch of unnecessary code in your manifest and a new .java file for the ability to write DatabaseHelper.getInstance() instead of DatabaseHelper.getInstance(this)?

The whole purpose of the Context is to provide application-specific resources to classes/objects that don't have direct access to them, so quite frankly the Context is meant to be passed around like this. Go for it if you want, but I still think that there is absolutely nothing wrong with passing the Context as a parameter to the method.

Browse through the util package in the Google I/O 2012 source code and you'll find that most static utility methods take Contexts as arguments... so I definitely wouldn't call this bad practice!

Alex, for SQLite I believe that the whole problem with the Context is that the Context lacks context. Allow me to explain.

It makes sense to pass a Context to things like UI display elements such as widgets or ListAdapters because there is a logical dependency of widgets to classes that operate on the user interface and, hence, rely on Context.

In contrast, it makes no logical sense for a database to depend on a Context (and in reality it doesn't ... if their were another elegant way for the app to learn where to put the database, then there would be no dependency on the Context at all). For this reason, passing a Context to the abstract Factory method becomes part of the database implementation rather than a natural dependency.

If one uses a database adapter class to abstract the database implementation, it makes sense to wrap all the implementation-dependent features into the adapter. Upgrading or changing the adapter could potentially mean upgrading every other class in the project where method signatures appear that contained Context as an argument. This makes the code less simple and less easy to maintain.

Taking the Context out of the method signatures and wrapping it into a global application state therefore preserves the abstraction of the adapter class since the the adapter is the only class that needs to work with the Application object. It's a matter of style, but I contend that this keeps the project simpler and easier to maintain.

1) Again, the SQLiteOpenHelper definitely DOES depend on the Context, as without it it would have no way of knowing where the SQLiteDatabase exists on the disk. So your statement that "it makes no logical sense for a database to depend on a Context" is incorrect. The logic behind it is that the SQLiteOpenHelper needs to retrieve the application's information in order to know where to initialize the database on the disk. Therefore the SQLiteOpenHelper must depend on the application context.

2) Application extends Context so there is no point in holding a reference to a Context in your subclass of Application.

3) The DatabaseHelper's implementation will never have to change... so I'm not sure why you are worried about having to "maintain it in the future". The Context is the central core of how Android applications work... its not going anywhere anytime soon.

4) From the documentation on the Application class:

"There is normally no need to subclass Application. In most situation, static singletons can provide the same functionality in a more modular way. If your singleton needs a global context (for example to register broadcast receivers), the function to retrieve it can be given a Context which internally uses Context.getApplicationContext() when first constructing the singleton."

Therefore, it is clear that the people who wrote the Android framework don't approve of subclassing Application simply to retrieve the application's context... instead they recommend passing a Context as an argument to the method.

5) All that said, I'm still confused as to how you would retrieve the Context from within the DatabaseHelper if you aren't given a Context as an argument to the method... how do you plan on doing this?

Alex,

If I am using a ContentProvider (accessing a Sqlite db) with a CursorLoader to populate a ListView but then I need to make another separate query to the database within the same Activity, what it the best way to go about this?

Would you use getContentResolver() from the activity to return a new Cursor and then close it yourself? If so, would this be in a different thread and could there be any issues with Database Locked Exceptions?

Hi Jason,

You shouldn't need to worry about Database Locked Exceptions when working with a ContentProvider... if you take a look at the source code for SQLiteDatabase you'll find that access to the underlying database is already synchronized. Assuming your ContentProvider only instantiates one SQLiteDatabase (or SQLiteOpenHelper), you shouldn't run into any "database locked" exceptions.

As for performing separate queries within the Activity, you could use an AsyncQueryHandler, which is a helper class to help make handling asynchronous ContentResolver queries easier. I would avoid using "getContentResolver().query()" on the main UI thread as querying a database is a potentially expensive operation and can thus introduce lag.

Hi Alex,

My problem is related to not being able to have TWO separate connections to the same SQLite database in an Android app (or at least I did not found a way to do this until now).

The scenario I am trying to implement is the following:
- Having a UI with lists, and other forms bound to table fields, using ContentProvider (or Local DB as a singleton), where there can be all kind of interactions with the DB (mainly selects - using async calls, but also inserts, updates, deletes!!!)
- Having a service that updates regularly the data in the database, using ... and here is my problem. What should I use here?

The service should be able to update the database inside a TRANSACTION, isolated from the UI, until it is committed. In a normal desktop application this can be achieved by using two different connections to the DB.

So is there a way of achieving the same functionality in an Android application or I am stuck with "serializing" the activities (so that when the service has to update the DB only SELECTS would be permitted for the UI)?

Good remedy you offered here with example of SQLite data source with android operating system applications.generally fresh of Android applications discover this mistake and could not fix by self.by your publish they can fix this issue.

I have been trawling the web for a proper way to handle an SQL database for use through multiple activities. All I was finding was some messy method where you create a "global" database via extension of the Application class. This is a much neater and maintainable solution. Thanks!

Hi Alex,

Regarding Robert's query about a service, I am trying to do the same thing. I have a service which is periodically, say every 15 mins, checking for and inserting data to the SQLite Database and a main application which may or may not be frequently opened. With the singleton approach, should I be creating the instance with the ApplicationContext or the MAIN LAUNCHER activity context? Because from Honeycomb onwards, service is started only after the Launcher activity is first started.

Also, in this case is it ok, for the DB connection to be always open? I will not be closing the connection in either the service or the application because the other one maybe using it at the same time.

Thanks for your help.

Hmm, somehow I ended up implementing both methods #1 and #2. When inserting, updating, deleting, etc., my Content Provider calls the database helper and gets the writeable database. Are you saying that this is unnecessary or poor design? There could just be a SQLiteDatabase in the Provider?

I followed the guidelines from Pro Android 4. (see the Creating a Content Provider Section)
http://www.satyakomatineni.com/akc/display?url=DisplayNoteIMPURL&reportId=2882&ownerUserId=satya

One problem I ran into was that I had to synchronize all of the Content Provider methods in order to avoid opening multiple connections to the DB.

Hi Alex. Thank you for your code! It work very well but sometimes i get the error "java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.".
Here is the link: https://dl.dropbox.com/u/105753409/Snapshot_2013-02-20_140901.png.
Can you help me to fix it? Thank you so much! :)

Thanks for the very useful article.
Could you please write an article on what how to manage a database with multiple tables.

I use the first connection pattern, but randomly I get an exceptioN.
The connection pool for database '/data/data/xyx/xyz.db' has been unable to grant a connection to thread xyz with flags 0x1 for 270 seconds

Do you know why?

PS: I use the same database connection in many threads and services (app, widgets, auto-update services ecc..)

Hi Alex,

Thanks for all of that : articles, comments & replies. It was so helpfull.
I introduced Loaders in my application thanks to you.
But in my case ContentProvider didn't seem to be the best way... According to the documentation : "You don't need to develop your own provider if you don't intend to share your data with other applications"
http://developer.android.com/guide/topics/providers/content-providers.html

So in my case I chose to use a singleton and my cursorLoader is updated by a localBroadcastReceiver (my service in charge of update from net send the intent after any db update). Is it a good practice ?