How to correctly handle database in ListFragment?

Question

I am building a very simple app that contains a SQLiteDatabase which I want to display in a ListFragment, using a custom SimpleCursorAdapter.

My code is working fine, but I'm not sure if I'm doing things the correct way. I have searched a lot for (authoritative) examples of this, but have only found either overly simplified examples using ArrayAdapter, or overly complicated examples using ContentProvider.

ListFragment

public class CallListFragment extends ListFragment{

    private CallListDbHelper dbHelper;
    private SQLiteDatabase db;
    private Cursor cursor;
    private CallListAdapter adapter;



    @Override
    public void onResume() {
        super.onResume();

        // Create a database helper
        dbHelper = new CallListDbHelper(getActivity());

        // Get the database
        db = dbHelper.getReadableDatabase();

        // Get a cursor to the entire call list from the database
        cursor = db.query(                                                          // SELECT
                CallEntry.TABLE_NAME,                                               // FROM ...
                new String[] {                                                      // <columns>
                        CallEntry._ID,
                        CallEntry.COLUMN_NUMBER,
                        CallEntry.COLUMN_TIME },
                null,                                                               // WHERE ... (x = ?, y = ?)
                null,                                                               //   <columnX, columnY>
                null,                                                               // GROUP BY ...
                null,                                                               // HAVING ...
                CallEntry.COLUMN_TIME + " DESC"                                     // ORDER BY ...
        );

        adapter = new CallListAdapter(getActivity(), cursor);
        setListAdapter(adapter);
    }


    @Override
    public void onPause() {
        super.onPause();

        // Close cursor, database and helper
        if( null !=cursor ) cursor.close();
        if( null != db ) db.close();
        if( null != dbHelper ) dbHelper.close();
    }

}

Adapter

public class CallListAdapter extends SimpleCursorAdapter {


    private static final String[] FROM = {
        CallListContract.CallEntry.COLUMN_NUMBER,
        CallListContract.CallEntry.COLUMN_TIME
    };

    private static final int[] TO = {
        R.id.phoneNumber,
        R.id.time
    };



    public CallListAdapter(Context context, Cursor cursor){
        this(context, R.layout.listitem_call, cursor, FROM, TO);
    }


    private CallListAdapter(Context context, int layout, Cursor cursor, String[] from, int[] to) {
        super(context, layout, cursor, from, to, 0);
    }

}

Well, the fragment looks good. CallListAdapter might be also interesting. — J. Dow, Apr 28 '16 at 14:07

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

For something simple it will work. But...

Do you use your DB in one place?

How big is your DB?

How often do you hit onResume/onPause?

General recommendation is onResume/onPause should be as fast as possible, but DB operations can be blocking...

Especially first touch (creation) of DB can be time-consuming and potentially you can get ANR.

Don't use Activity as a Context or you may can get memory leaks. The recommended way is to use the Context of your Application in conjunction with singleton pattern, so you'll not bother to close DB.

CursorLoader needs the cursor to be open in order to function and will call close() on the cursor for you. As for SimpleCursorAdapter I don't see auto-close feature in the source code =(.

edited May 23 '17 at 12:07

Community

1
1

answered Apr 28 '16 at 14:14

Maxim G

1,479
1
15
23

Perhaps some example code to clarify what you mean by all that..? – Magnus Apr 28 '16 at 14:32
I was under the impression that a `CursorLoader` requires a `ContentProvider`, which just seems waaaay to overkill for what I'm doing. They even say so in [Creating a Content Provider](http://developer.android.com/guide/topics/providers/content-provider-creating.html) (under "Before you start building). – Magnus Apr 28 '16 at 14:38
@Maxim G are your sure with close() being called automatically? I don't remember it that way and i couldn't find andy references to this. – J. Dow Apr 28 '16 at 14:56
So I read the post on not needing to close the database. But, by "database" and "DB", it's not entirely clear if people mean the `SQLiteOpenHelper`, or the `SQLiteDatabase`? I'm guessing it's the former, because the latter can be opened either for reading or writing, so how would you make it into a singleton and never close it? – Magnus Apr 28 '16 at 15:03
Edited answer, still need some tests. – Maxim G Apr 28 '16 at 15:03
@J.Dow For SimpleCursorAdapter I don't see auto-close feature in the source code. – Maxim G Apr 28 '16 at 17:39
@BadCash Neither SQLiteOpenHelper nor SQLiteDatabase should be closed. The whole application process will be cleaned by user exit. – Maxim G Apr 28 '16 at 17:40
How then should I open the database? For some queries I just need to read from the database (i.e. `getReadableDatabase()`, and for others I need to write to it (i.e. `getWritableDatabase()`). Are you saying I should call `getWritableDatabase()` once, so that I can use both reading and writing after that? It sounds a bit sketchy... – Magnus Apr 28 '16 at 17:46

How to correctly handle database in ListFragment?

ListFragment

Adapter

1 Answers1