Lazy data loading from SQLite

Data appears immediately / Data appears with delay

Lazy loading data from SQLite means parsing data from Cursor on the fly when you need it (on-demand). You may wonder why you need this? To answer this question let’s see some benchmarks and sample first.

Loading 10 000 objects takes time, which means your user have to wait until all of them are loaded and this i bad for user experience. What we want - immediately display number of items which fits screen and load other when user scroll.

There is a class - CursorAdapter which you can use to implement lazy data loading, but I am sure you all are familiar with it, and ofc this article is not about it. The main problem with CursorAdapter is that it doesn’t cache data and it doesn’t work with RecyclerView.

Here is our plan. When query return Cursor object we will create and return cursor.getCount() empty Proxy objects. Those proxy object must have getters method to parse data from Cursor and cache it.

Let's start by defining simple data object class - User.

publicclassUser{privatelongid;privateintage;privateStringname;privateStringemail;publicStringgetName(){returnname;}publicvoidsetName(Stringname){this.name=name;}// other getter and setter}

This is where the magic happens. UserProxy class extends CursorItemProxy and has reference to Cursor, index and User object. If name inside User object is empty - we parse data from Cursor and cache it. Next time - cached name from User object is returned.

publicclassUserProxyextendsCursorItemProxy{privateUsermUser;publicUserProxy(@NonNullCursorcursor,intindex){super(cursor,index);mUser=newUser();}publicStringgetName(){if(mUser.getName()==null){Cursorcursor=getCursor();cursor.moveToPosition(getIndex());intcolumnIndex=cursor.getColumnIndex("name");mUser.setName(cursor.getString(columnIndex));}returnmUser.getName();}// other getter}

Now it's time to add select method to UserDAO class. Note that manageProxyCursor() method creates UserProxy objects and passes Cursor and cursor position for later use.

publicclassUserDAO{privateDatabasemDatabase;privateContextmContext;publicUserDAO(Databasedatabase,Contextcontext){mDatabase=database;mContext=context;}publicList<UserProxy>selectAllUserProxy(){Cursorcursor=mDatabase.rawQuery(mContext.getString(R.string.select_all_users),null);returnmanageProxyCursor(cursor);}protectedList<UserProxy>manageProxyCursor(Cursorcursor){List<UserProxy>dataList=newArrayList<>();if(cursor!=null){cursor.moveToFirst();while(!cursor.isAfterLast()){dataList.add(newUserProxy(cursor,cursor.getPosition()));cursor.moveToNext();}}if(dataList.isEmpty()){closeCursor(cursor);}returndataList;}publicvoidcloseCursor(Cursorcursor){if(cursor!=null){cursor.close();}}// other methods}

Finally you can load and display data. Please keep in mind the following:

Make sure to close Cursor to avoid leaks. See cleanUpDatabase() method.

You may noticed that selectAllUserProxy() method generates cursor.count() empty UserProxy and User objects. If you have 10k rows in your database it means 20k empty objects will be generated.

To fix this issue we can create custom collection which will create objects only during first reference.

publicclassLazyList<T>extendsArrayList<T>{privatefinalCursormCursor;privatefinalItemFactory<T>mCreator;publicLazyList(Cursorcursor,ItemFactory<T>creator){mCursor=cursor;mCreator=creator;}@OverridepublicTget(intindex){intsize=super.size();if(index<size){// find item in the collectionTitem=super.get(index);if(item==null){item=mCreator.create(mCursor,index);set(index,item);}returnitem;}else{// we have to grow the collectionfor(inti=size;i<index;i++){add(null);}// create last object, add and returnTitem=mCreator.create(mCursor,index);add(item);returnitem;}}@Overridepublicintsize(){returnmCursor.getCount();}publicvoidcloseCursor(){mCursor.close();}publicinterfaceItemFactory<T>{Tcreate(Cursorcursor,intindex);}}

Now add another method to UserDAO class. Note LazyList.ItemFactory.create(...) method is used to define how your object is parsed from Cursor.