Update / Join SQLite DB table from java objects

Question

Here is a minimal example to describe the problem:

Suppose a table is read from SQLiteDB and stored 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 the sqlite jdbc library:

Map objTable = new HashMap (); // ... adding some jdbc stuff, we get a copy of DBTable in objTable

Then, if the object is modified, thereby.

 idRecord | Data (modified table stored at objTable) 2 Two 4 FourModified 5 Five 

(id 1 and 3 were deleted, 2 remained unchanged, 4 changed and 5 added)

Java Object -> DB Table (here's the question ...)

---

How to update / merge an object table using a database?

Why do I want to join, and not just save the object in a table ?

I think that if the table is large enough, then it makes no sense to write all the records, if only some of them have been changed.

• Delete the entire DBtable and in a loop (dangerously the same), go through the object to write a new table.

• Read the DBtable in the second java obj, and then compare both (with some alghoritm merge) and apply the actions (ADD, DELETE, MODIFY) directly to the DB. (I would recommend a recommendation for this comparison algorithm)

• EDIT: Do not create the collection in the first place, read and write directly from the database, transmit requests through JDBC all the time

• Another best approach

Thanks4Reading

Answer 1

Two operators in one transaction. (unfortunately, SQLite does not support ON DUPLICATE KEY UPDATE ish functionality, but hey, this is "Lite": P)

First, INSERT OR IGNORE then UPDATE yourtable SET data = hashTableData WHERE id = hashTableId AND data! = HashTableData p>

You may be able to determine which ones were ignored. I would try to make two prepared statements, and I assume that execute will return false if the ignore clause was suggested. Give it a try.

If so, update to false.

Otherwise, loop the data twice two times for each statement and make the transition when you're done :)

Answer 2

Keep track of entries that have been changed in ArrayList, something like that. Then, when it comes time to update the database, update only those changes that have been changed.

You can create a base class that does the above and then extend it for each table in your schema.

Answer 3

You can implement behavior similar to the (simplified) behavior of a Hibernate session cache. Your memory view may contain a dirty flag that indicates which objects need to be updated in the database and which ones to delete. Depending on the algorithm that you use to extract identifiers (application-oriented and databases), you can identify objects that were added with a special value (for example, 0), or by storing this information in a dirty flag.

Another potential improvement is to take into account intolerance (read all entries immediately) compared to lazy ones (read entries as needed) when loading your cache.