Android: Fragments, SQLite and Loaders

Question

So I've come to a point where I need to implement an SQLite database for my app. Following "The Busy Coder's guide to Android Development" I have created a DatabaseHelper class that extends SQLiteOpenHelper.

One of my use cases is to run a query against the database and display the results on a ListView within a Fragment (I use fragments from the support library).

From what I understand, using managedQuery() is not really appropriate and even if it were it isn't recommended due to the fact that some of the logic encapsulated inside this method is actually executed on the main thread, specifically reQuery() which to my understanding is performed when the Activity is restarted.

So I've been trying to get acquainted with the Loader class for the first time, only to see this:

"The only supplied concrete implementation of a Loader is CursorLoader, and that is only for use with a ContentProvider"

My initial thought was to implement my own content provider and perhaps prevent other apps from getting access to it, then I read the following in the ContentProvider documentation via developer.android.com:

"You don't need a provider to use an SQLite database if the use is entirely within your own application."

I've also been playing with this:

https://github.com/commonsguy/cwac-loaderex

Yet I am not familiar with this project, and not sure if it can be used on a production environment.

So, right now all I can think of is creating a bunch of AsyncTask instances within my Fragment and manage their lifecycle appropriately, make sure they're cancelled whenever needed and whatnot.

Are there any other options?

Answer 1

I think implementing content provider is a good idea, no matter that data will not be accessible outside of the application. It provides very modern interface and from my experience makes your application error prone to database locking issues and other db-specific problems.

I've implemented it in my latest project and I was very happy to use it.

Answer 2

I recommend OrmLite library, a lightweight Object Relational Mapping that can work for Android. This library will make your life easier . You don't need to create or update database by hand, you don't need to focus on managing database connection, all queries select, insert, update will be easier with a DAO approach (usually you don't need to write your own sql query) and a lot of features. They have some examples that you can start with.

And if you want to use the Loader, there is a ORMLite Extras , additional functionality for ORMLite available on github (You can use the support package which is compatible with support android library). Here is an example usage on my previous project:

public class EventsFragment extends Fragment implements LoaderCallbacks<Cursor>{
   private static final int LOADER_ID = EventsFragment.class.getName().hashCode();
   @Override
   public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    getLoaderManager().initLoader(LOADER_ID, null, this);
   }

    @Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
        Bundle savedInstanceState) {
    View layoutRoot = inflater.inflate(
            R.layout.fragment_events, null);
    lvEvents = (ListView) layoutRoot.findViewById(R.id.lvEvents);   

    adapter = new EventAdapter(getActivity(), null, null);
    lvEvents.setAdapter(adapter);

    return layoutRoot;
}

    @Override
    public Loader<Cursor> onCreateLoader(int arg0, Bundle arg1) {
       try {
           PreparedQuery<Event> query = getDatabaseHelper().getEventDao().getQuery();
           return getDatabaseHelper().getEventDao().getSQLCursorLoader(query );
        } catch (Exception e) {
        e.printStackTrace();
    }

    return null;
}

@Override
public void onLoadFinished(Loader<Cursor> arg0, Cursor cursor) {
    adapter.swapCursor(cursor);
    try {
        adapter.setQuery(getDatabaseHelper().getEventDao().getQuery());
    } catch (SQLException e) {
        e.printStackTrace();
    }
      }

@Override
public void onLoaderReset(Loader<Cursor> arg0) {
    adapter.swapCursor(null);
}

    private OrmliteDatabaseHelper getDatabaseHelper(){
         return ((MainActivity)getActivity()).getDatabaseHelper();
    }
 }

The adapter

 public class EventAdapter extends OrmliteCursorAdapter<Event>{

public EventAdapter(Context context, Cursor c, PreparedQuery<Event> query) {
    super(context, c, query);
}

@Override
public void bindView(View itemView, Context context, Event item) {
    TextView tvEventTitle = (TextView) itemView.findViewById(R.id.tvEventTitle); 
    TextView tvEventStartDate = (TextView) itemView.findViewById(R.id.tvEventStartDate);

    tvEventTitle.setText(item.getTitle());
    tvEventStartDate.setText(item.getFormatStartDate());
}

@Override
public View newView(Context context, Cursor arg1, ViewGroup arg2) {
    LayoutInflater inflater = LayoutInflater.from(context);
    View retView = inflater.inflate(R.layout.event_item_row, arg2, false);
    return retView;
}
 }

And a custom Dao which provides PreparedQuery for cursor adapter above:

public interface IEventDao extends Dao<Event, Integer>{
    PreparedQuery<Event> getQuery() throws SQLException;
    OrmliteCursorLoader<Event> getSQLCursorLoader(Context context, PreparedQuery<Event> query) throws SQLException;
}

public class EventDao extends AndroidBaseDaoImpl<Event, Integer> implements IEventDao{

public EventDao(ConnectionSource connectionSource) throws SQLException {
    super(connectionSource, Event.class);
}

public EventDao(ConnectionSource connectionSource,
        DatabaseTableConfig<Event> tableConfig) throws SQLException {
    super(connectionSource, tableConfig);
}

@Override
public PreparedQuery<Event> getQuery() throws SQLException{
    return queryBuilder().prepare();
}
}

Hope this can help!