0

I'm new to Android Development and trying for the first time to implement a databinding between a RecyclerView and a SQLite Database. Now I've been searching for examples and tutorials on the topic and found less information than I expected.

The few solutions I found, like this one: Display SQLite data in RecyclerView, always seem to query the database for all data, store it recursively into a List which then is passed on to the RecyclerView.Adapter. The adapter class is then browsing through the Items by referring to the items in the List<Item>.

Now for the actual question: Is this solution smart? I'm a hobby programmer, hence I'm far from being an expert for database and / or memory design. This solution though leaves me with a headache, since querying the database for all items and storing them in an array seems not only to defeat the advantage of querying a SQLite Database, but also appears to have the risk of polluting the memory (and thereby dropping performance). Hence: Is there a better solution for binding database data to a RecyclerView or is the Array<Item>-Query-All-Solution just fine for the purpose?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
m.a.
  • 3
  • 4
  • you are absolutely 100% right - use [this](https://gist.github.com/Shywim/127f207e7248fe48400b) adapter or even better: https://github.com/blandware/android-atleap/blob/master/atleap-core/src/main/java/com/blandware/android/atleap/loader/SimpleCursorRecyclerAdapter.java - for this one i dont know why the author decided to make it `abstract` - you can simply remove that keyword – pskink May 13 '17 at 14:08
  • and no: do not use ANY pagination: the worst ever idea for querying the local sqlite database – pskink May 13 '17 at 14:11
  • @pskink : Why would you call it "worst idea" btw? – Manish Kumar Sharma May 13 '17 at 14:12
  • @pulp_fiction since it is not obvious how to implement it right, it is slow, bad UX design and finaly can lead to `OutOfMemoryError`s as well – pskink May 13 '17 at 14:17
  • @pskink : Then what are you going to do for fetching a 1000 or a million records? – Manish Kumar Sharma May 13 '17 at 14:18
  • @pulp_fiction using a `Cursor` based adapter – pskink May 14 '17 at 14:14

1 Answers1

0

I think, you are looking for Pagination. Pagination is for limited retrieval of the results from the Database and here for SQLLite(Notice the limit here). Ex:

SELECT *
FROM MyTable
WHERE SomeColumn > LastValue
ORDER BY SomeColumn
LIMIT 100

Efficient paging in SQLite with millions of records.

What is pagination?

If you have a form which allows the user to browse through the rows in a database table, what do you do if that table has hundreds or even thousands of rows? It would not be a good idea to show all those rows in a single form, instead you should split the database output into more manageable chunks or 'pages'. There are two things you must do:

  • Decide on the maximum number of database rows that can be included in each page. You may hard code this value, or
  • you can define it in a variable so that the value may be changed at runtime.

You then need to inform the user that other 'pages' are available and provide a mechanism whereby the user is able to select a different 'page' of details.

Then, whenever new data comes, call notifyDatasetChanged() on your Adapter to refresh it.

A reference for Pagination: https://www.tonymarston.net/php-mysql/pagination.html

Community
  • 1
  • 1
Manish Kumar Sharma
  • 12,982
  • 9
  • 58
  • 105
  • Of course... I actually have not thought about the right concept. Pagination is already a much better idea for implementing the adapter. Still, this leaves the question at which amount of rows pagination starts to become necessary for the performance. – m.a. May 13 '17 at 14:03
  • @m.a. The right amount is the amount that is critical to your user's experience. For example, while searching you can use an estimate of how many search results(pages) user actually clicks on average. Then set the Page limit according to that. Its a tuneup process for the page limit that ultimately depends on your needs. – Manish Kumar Sharma May 13 '17 at 14:09