Update/Merge SQLite DB table from java objects

Question

Here a minimal example to describe the issue:

Suppose a table is read from a SQLiteDB and store it in a Java Collection object

DB table--->Java 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)

Java Object-->DB table (Here is the question...)

---

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

Thanks4Reading

Answer 1

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 :)