Best practices to query SQLite database in ListFragment with CursorLoader?

Question

I'm using Android Compatibility Library in my project. I've set up ListFragment as described in the DevGuide (http://developer.android.com/reference/android/app/Fragment.html), and using a simple CursorLoader Christian made be used without content provider (CursorLoader usage without ContentProvider).

The question is where, in my ListFragment / parent Activity, should I open database, return the Cursor, create Adapter and setListAdapter?

So in my app, I have TitlesFragment, DetailsFragment, FragmentLayoutActivity, DetailsLayoutActivity.

Is the best practice...

• to open database in ListFragment's onActivityCreatedand close it in ListFragment's onDestroy like in code sample below

  @Override
  public void onActivityCreated(Bundle savedInstanceState) {
      super.onActivityCreated(savedInstanceState);
      // Open database
      playersDatabaseHelper = new PlayersDBAdapter(getActivity());
      playersDatabaseHelper.open();
      getLoaderManager().initLoader(0, null, this);
      ...
  }
  
  @Override
  public void onDestroy() {
      super.onDestroy();
      if (playersDatabaseHelper != null) {
          playersDatabaseHelper.close();
      }
  }
  

• query database and return the cursor in onCreateLoader, and create the Adapter and setListAdapter in onLoadFinished like in code sample below

  @Override
  public Loader<Cursor> onCreateLoader(int id, Bundle args) {
      // Now create and return a CursorLoader that will take care of
      // creating a Cursor for the data being displayed.
      return new MyCursorLoader(getActivity()) {
          @Override
          public Cursor loadInBackground() {
              playersCursor = playersDatabaseHelper.getAllPlayers();
              return playersCursor;
          }
      };
  
  }
  
  @Override
  public void onLoadFinished(Loader<Cursor> loader, Cursor cursor) {      
      // Create an empty adapter we will use to display the loaded data.
      playersAdapter = new RowAdapter(getActivity(), playersCursor, R.layout.players_overview_row);
  
      // Allocate the adapter to the List displayed within this fragment.
      setListAdapter(playersAdapter);
  
      playersAdapter.swapCursor(cursor);
  
      // The list should now be shown.
      if (isResumed()) {
          setListShown(true);
      } else {
          setListShownNoAnimation(true);
      }
  }
  

Am I on the right track or should I move some of those somewhere? Thanks for your time!

Answer 1

Sorry no experience in CursorLoader yet and Fragment, but I already experienced use of SQLiteOpenHelper in the context of concurrent access by different threads & activities.

I will assume that PlayersDBAdapter is internally using a SQLiteOpenHelper class. but it is not clear what your methods open() and close() are doing?

What I did:

• define your SQLiteOpenHelper as an application wide singleton, not activity wide as you seem to do
• instantiate SQLiteOpenHelper single instance in your Application onCreate
• DON'T release SQLiteOpenHelper instance in any activity onDestroy, as when an activity stops, another one might still have to open the DB
• I guess SQLiteOpenHelper instance should be cleared in application onTerminate (not sure as onTerminate is in practice almost never called)
• I have DBAdapter object which gets a SQLiteDatabase reference with mySQLiteOpenHelper.getWritableDatabase()
• these DBAdapter are typically allocated in activity onCreate and released in onDestroy

At least this works, no crashs in an application with several thousands users. Suggestions to improve that are welcome :-)