Best place to close database connection

Question

I was looking for a while for answer on my question but I didn`t get what I need. I have an application with a ListView, and form where I can add new record to DB. So there is not much queries to do.

How to handle connections to db ? Should I close it after getting what I want or should I keep it open whole time until app is closed ? I want to know what is the best way while thinking about performence and battery life.

Real

Great explanation here.. #14002522 – Afc

Answer 1

According to this post by a Google engineer (Dianne Hackborn), there's nothing wrong with leaving the database connection open:

Android made a deliberate design decision that is can seem surprising, to just give up on the whole idea of applications cleanly exiting and instead let the kernel clean up their resources. After all, the kernel needs to be able to do this anyway. Given that design, keeping anything open for the entire duration of a process's life and never closing it is simply not a leak. It will be cleaned up when the process is cleaned up.

So, for simplicity, I would extend the Application class to provide a single well-defined entry point for your code, and open the database connection in its onCreate(). Store the DB connection as a field in your Application, and provide an accessor method to make the connection available to rest of your code.

Then, don't worry about closing it.

Wooton

For quik fix this is good answer, but probably this can be done better by making good program flow and first of all planing. – Senlac

@JānisGruzis how would any alternatives be "better" than something which always works, and is extremely simple? – Wooton

Well leaving database connection open seems like bad practice, even if its said that it will be closed after application is dead, I haven't met description of this behavior in Android documentations and if so, that always means more job for Android (keeping connections and closing them). Also what happens if two applications work side by side (they could be working in same time as Service and Activity) and they both keep connection opened. Off course this isn't the case and that is the reason I gave vote for this solution. – Senlac

@JānisGruzis It's a function of Linux kernel on which Android is based; when a process (i.e. your application) terminates, the OS will clean up everything that was not persisted (e.g. to disk): all memory is reclaimed, all handles closed, etc. That being said, I prefer to "be explicit" about lifetimes .. – Ciborium

what about an android.database.sqlite.DatabaseObjectNotClosedException that I get sometimes? if I wouldn't catch it my app will get force closed – Tiffaneytiffani

This seems a little strange considering the documentation on getWritableDatabase(): Create and/or open a database that will be used for reading and writing...Once opened successfully, the database is cached, so you can call this method every time you need to write to the database. (Make sure to call close() when you no longer need the database.) – Salonika

@Salonika and the point at which you are absolutely certain you "no longer need the database" is when your app is being closed by the OS. So let the OS handle it for you. :) – Wooton

@GrahamBorland, That's one way to look at it. So, what you mean is the documentation should really be read as ...Make sure to not get in the way of the OS doing its job! Well...in my app it seems to be doing fine with the database, so I think I'll trust it with this one... :) – Salonika

... and not just any "Google engineer" either! – Academy

what if the database is used in multiprocess? – Hattie

As i am working on chat app i am always close database at onPause and onStop and not opening manually cuz i am calling getWritableDatabase(): before any database work – Enforce

Answer 2

In general I'd close the connection in the onDestroy() function of the Activity which opened the connection. I'd close() a cursor from a database in the function which uses the cursor.

public MyActivity extends Activity{
    private myDatabase mDatabase; // myDatabase extends SQLiteOpenHelper
    private Cursor mCursor;

    public MyActivity(Context context){
        super(context);
        initMemberVariables();
    }

    public ElementButton(Context context, AttributeSet attrS){
    super(context, attrS);
        initMemberVariables();
    }

    public ElementButton(Context context, AttributeSet attrS, int defStyle){
        super(context, attrS, defStyle);
        initMemberVariables();
    }

    private void initMemberVariables(){
        mDatabase = new PSEdb(this.getContext());
    }

    private void getData(){
        mCursor = mDatabase.MyGetterFunction();
        while(mCursor.moveToNext()){
            try{
                // populate your data
            }catch(CursorIndexOutOfBoundsException ex){
                // handle the exception
            }
        }
        mCursor.close();
    }

    @Override
    public void onDestroy(){
        super.onDestroy();
        mDatabase.close();
    }
}

Motherwell

Answer 3

Establishing the connection to the database is expensive. If connections are not in short supply, and the database is local, I'd keep the connection open rather than establishing it for each write operation to the database, as you'd typically do in a client-server application that needs to scale to accommodate a large number of concurrent users.

Rapprochement

Yes I found that opening might be expensive. So if I understand you good I should keep the connection open. But what if user will go to home screen. Should I close it in onPause() and open again in onResume() ? How can I pass it between Intents ? – Real

@Real i think and i could totally be wrong, the idea is to use singletons. Now, you could go by that by using a global variable -- hard for testing. Or, you could use a dependency injection library e.g., hilt and dagger 2 so it can manage the entire process for you. DI usually comes with supporting packages to help with the mock and testing. – Cardcarrying