I've got a Spring+Hibernate+MySQL after sales that exposes my model (8 different organizations) to some desktop client. To help keep synchronized, I would like the customer to regularely request the server for recent changes. The procedure might be the following:

  • Point A: The customer connects for that very first time and retrieves all of the model in the server.
  • Point B: The customer asks the server for those changes since Point A.
  • Point C: The customer asks the server for those changes since Point B.

To retrieve the alterations (point B&C) I possibly could produce a HQL query that returns all rows in most my tables which have been last modified since these retrieval. However I am afraid this is often a heavy query and degrade my performance if performed oftenly.

Because of this I had been thinking about other options as keeping another table with recent updates for a quick access. I've looked to presenting L2 query cache however it does not appear for everyone for my purpose.

Does someone know a great technique for my purpose? My primary thought would be to keep charge of synchronization and steer clear of using "automatic" synchronization tools.

Thank you

you are able to store alterations in a queue table. Triggers can populate the queue on place, update, remove. this preserves an order from the changes like place, update, update, remove. Empty the queue after download.

Draining the queue would cause issues for those who have multiple clients.... might need to consider a design to deal with that situation.

you will find several designs you are able to opt for, with trade offs. I have tried personally the queue design before, however it was just copying data to some single destination, not multiple.