Managing SQLite Connections in Android

Question

I have a (hopefully) quick question regarding handling SQLite database connections in Android. I have an app that is composed, naturally, of several activities. I have no trouble creating/updating/querying the database, as I've created a single, dedicated class to handle that work via SQLiteOpenHelper, etc.

My question is this: since these activities all share this same database, is this usually implemented as a single, static member, or should each activity own its own connection? My concern of course is the cost of re-connecting to the database in each activity.

Or, put another way, is there any reason not to just store a singleton instance?

I'm also wondering if there's something going on behind the scenes similar to .NET's connection pooling to reduce the cost of opening connections.

Thanks in advance!

score 13 · Accepted Answer · answered Mar 01 '11 at 15:13

13

Connection opening in SQLite is about 0.2ms.

The best practice tells us to open and close the connection each time we need one.

answered Mar 01 '11 at 15:13

Vladimir Ivanov

42,730
18
77
103

Thanks! Sometimes finding an "official" best practice is a challenge. – Nathan B Mar 01 '11 at 19:13

RivieraKid · Answer 2 · 2011-03-01T16:55:34.087

9

Just use SQLiteOpenHelper.getReadableDatabase() and SQLiteOpenHelper.getWriteableDatabase() - Android will manage and cache the connection so you don't need to.

In fact, refer to the official Google Using Databases documentation:

To write to and read from the database, call getWritableDatabase() and getReadableDatabase(), respectively. These both return a SQLiteDatabase

edited Mar 01 '11 at 16:55

answered Mar 01 '11 at 15:09

RivieraKid

5,923
4
38
47

Thanks for the response @RivieraKid. The official documents didn't go into a lot of detail as to the expense of opening the connections, which is where I was going with the question. Knowing that there are measures behind the scenes to manage/reuse the connections, and that there is an acceptable cost of calling those methods as I need them (rather than holding onto the database object returned) helps tremendously. – Nathan B Mar 01 '11 at 19:12
1

But should I close the returning SQLiteDatabase each time I call those functions? I have a SQLiteDatabase with a lot of small functions, each one of them calls one of the "getXXXDatabase". Should I close the database at the end of every method? Won't I lose the cache advantages? Or does Android keep it anyway? – Marco Castanho Feb 25 '16 at 17:15

Managing SQLite Connections in Android

2 Answers2

Linked