Here a minimal example to describe the issue:
Suppose a table is read from a SQLiteDB and store it in a Java Collection object
idRecord | Data (table stored at DB) 1 One 2 Two 3 Three 4 Four
And through an sqlite jdbc library :
Map objTable = new HashMap (); //...adding some jdbc stuff, we get a copy of DBTable in objTable
Then if object is modified, thus being.
idRecord | Data (modified table stored at objTable) 2 Two 4 FourModified 5 Five
(id 1 and 3 were deleted, 2 remain the same, 4 modified, and 5 added)
How to Update/Merge the object table with the DB ?
Why I want to merge and not to simple save the object to the table ?
I think that if the table is large enough then it has no sense in writing all the records if only some of them were modified.
Delete the whole DBtable and in a (dangerous meanwhile) loop, walk the object to write the new table.
Read the DBtable in a second java obj and then compare both (with some kind of merge alghoritm) and apply the actions (ADD, DELETE, MODIFY) directly to DB. (I would acept recomendation for that comparison alghorithm)
EDIT: Do not create the Collection in the first place, reading and writing directly from DB, passing queries all the time through JDBC
Other better approach
Two statement in the same transaction. (unfortunately SQLite doesn't support ON DUPLICATE KEY UPDATE ish functionality, but hey, it is "Lite" :P)
First, INSERT OR IGNORE then UPDATE yourtable SET data=hashTableData WHERE id=hashTableId AND data != hashTableData
You might be able to identify which have been ignored. I would try and do two prepared statements, im guessing execute will return false if the ignore clause was triggered. Try it.
If thats the case, then do the update on false.
Otherwise, loop though the data twice once for each statement and commit the transition when done :)
Track the records that have been modified in an ArrayList of something similar. Then when times comes to update the DB, update only the records that have been modified.
You can create a base class that does the above, then extend it for every table you have in your schema.
You can implement behavior similar to the (simplified) behavior of Hibernate session cache. Your memory representation can keep a "dirty" flag that would indicate which objects need to be updated in the database and which - deleted. Depending on the algorithm you use to allocate ids (application-centric vs. database-centric) you can identify objects that were added by a special value (say, 0) or by keeping this information in the dirty flag.
One more potential enhancement, is to consider eager (read all records at once) vs. lazy (read records as needed) loading of your cache.
MVC 3 Webapp-模型绑定器