I am writing an application on Android, where I have a certain list (RecyclerView) in which I can drag and drop rows to the right place for the user (above / below relative to each other). List items (strings) can be of different classes (under the common interface). Information about the created instances, respectively, is stored in SQLite.

At the moment, the list is sorted by all the elements of the list by date (when creating the list, they are sorted in the TreeSet and then sent by the ArrayList to the adapter). Thus, when changing the order of displaying the elements of the list, I need to fix this order and save it in the database (or maybe just in JSON?).
What are, so to speak, best practices for saving and retrieving the state of such a list (and indeed, similar data)?

Thanks in advance!

    1 answer 1

    You do not need to distill data from the database into some local arrays. Work directly with the database, it has an order of magnitude more powerful than the "pitiful" methods of collections, the SQL language, specially designed for processing, sorting, and so on. The data contained in the database, as well as performs this processing by orders of magnitude faster than Java.

    The very approach you are trying to apply looks somewhat illogical. Why store the data in the database, but process it in some primitive and slow ways, while still wasting time on intermediate retrieval from the database into the array, then into another array.

    On the question - you do not need to save some sort of changed sorting order, rewriting the database. You need the correct query in the database, which will return the data (cursor), sorted in the correct order, this is how it is done. Then this cursor, in which everything is already sorted as it should be through the SQL query, you send directly to the list adapter and fill the list itself with data from the cursor, and not from some ArrayList.

    UPD If you want to save an arbitrary custom sort, then again you need to modify not the following of the records in the database, but add one more column to each record to store the sort order. When dragging, write to this column a position in the list of the item to which it was dragged. When selecting, make a query with sorting by this column.

    That is, an exemplary scheme - we make a selection from the database with time sorting, fill this list with a selection, while dragging we write to the database the position into which we dragged (to the user sorting column for the record that was dragged), update the adapter through the selection with sorting by column with these positions everything is simple and transparent

    This will be the correct architecture of working with the database, and what you are trying to do, some kind of amateur crutch, sorry for the sharpness, I hope this will help you understand the depth of the errors :)

    See also this sorting response in the database.

    • 2
      What is the minus for? You have illogical architecture and work with the database. Man directs the right path, offering the best way to extract and save data in the database. Actually, this is the answer to your question - whalemare
    • Hmm, how can the sorting in the database help to remember the order of elements created by the user by dragging and dropping? (And is it true that sorting a couple dozen of elements in memory is more expensive than accessing the database?) - VladD
    • @VladD It's not even about "overhead", but about the architecture used. For a couple of dozens of elements, it is possible to use the database in general does not make sense. As for the "costs" of both approaches. If the data were originally not in the database, the sorting in memory could be evened up in speed with the sorting in the database, and with a small number of elements it would not be important at all, since both operations would be performed fairly quickly, BUT! here, you must first get the data from the database, then overtake them one by one in the TreeSet , then one more in the ArrayList and only then output. - pavlofff
    • This path can and should be much shorter. Selection from the database of already sorted data and work directly with the selection. Nachat custom sorting added to the answer. - pavlofff
    • one
      @VladD I do not see any problem here, how the user arranges the elements, so they will be displayed, there are no logical criteria in the algorithm - the smaller the number in the custom sorting column, the higher the list will be this element or we do not understand each other. The author initially needs to sort by time, if you need to immediately in a certain order, then do a selection immediately by the custom sorting column. When filling out the database, indicate the initial order of the elements there - pavlofff