Synchronize POJO and database representation

Scenario

• An app wants to offer a user to enter data into different lists which are stored in a database (possibly including relations between the lists).


• The lists should be displayed with widgets in the UI and update when data is changed.


• Data should be changed in the app and in the database simultaneously (to keep consistency / safety)

Current setup

• 
  

  I use an SQLite database and the native Android way via SQLiteOpenHelper and cursors to access the database.

  
  


• 
  

  The relationship between data is mainly managed by the database and my DAOs (data access objects), including filters and orders.

  
  

Now I can query the data from the database at startup of the app and put it into lists, pass the lists to adapters for list widgets and they are displayed.

But updating the data introduces some problems, for which I can think of the following strategies.

Possible Strategies

A: Query database after every dataset change (simplest)

After anything was changed in the dataset, replace all data structures in the app with new ones with a new query.

• (+) easy to implement


• (+) consistency guaranteed


• (-) probably bad performance


• (-) probably not possible to animate insertion of items in lists at specific positions (?)

B: Manually perform all organization in app AND in database

Keep the lists as Java data structures and perform every operation on the dataset in both places, in those data structures and in the database.

• (+) performance


• (-) duplication of logic


• (-) error source

Additional problem: complicated to keep filter and order properties. E.g. if the database was queried to only show entries that have dates in a specific range and the entries should be ordered by date, this must be considered in the query code and in the manual insertion to the Java lists.

Question

• 
  

  What is a good design pattern to synchronize the list entries with the database?

  
  


• 
  

  Do any of the persisent frameworks that can be used with Android solve some of the problems listed?

  
  

I think of something like a query object which includes the SQL query statement and returns a java data structure (e.g. list) as result. Now when the data is changed, this query can be updated in a way that the same result data structure can still be used, but is updated with the changes.