Forums Register Login

JPA for table with versioned data

+Pie Number of slices to send: Send
I would like to build a set of versioned data, such that I can easily retrieve any version of data.

Tables would be structured as such:

OID
key column
data
version id (numeric system-wide increasing value over time)

So for instance if I have a simple table for a document which stores it's title, I may have:

row 1: oid=xx1, docid=yy1, title=My First Title, version=15
row 2: oid=xx2, docid=yy1, title=My Revised Title, version=267

The most common query would be to get the current version of an object (so if I ask for the title for docid=yy1 I would get "My Revised Title").

However, another common query would be to get the version of an object as of a certain period in time (as referenced by the version id).  So for instance if I ask for the title of docid=yy1 where the revision is <= 200, the answer would be "My First Title".

There would actually be many tables built like this, and thus I may want to join across tables, but still be able to query for the most recent record as of a specific version id.

I have built a custom JDBC API to deal with this, but I would prefer to be able to handle via JPA.

Does anyone have a recommendation for an elegant way to handle this in JPA?  Should I switch to another technology altogether?

Thanks,
ken

1
+Pie Number of slices to send: Send
Hibernate envers already supports this, basically for auditing. For every operation (create, update, delete) it inserts a record in a separate table. That means that you get two tables for each entity (XXX and XXX_AUD). You can then query the auditing tables, and it will match records together based on their own revisions.
+Pie Number of slices to send: Send
Thanks for the suggestion!  At a quick glance this looks very interesting and I'll start digging in.
1
+Pie Number of slices to send: Send
Hi,

funny, by chance I was looking this morning for the same issue, just 15 hours after you posted here.

Anyway, for eclipselink I found this:

https://stackoverflow.com/questions/22141391/tracking-changes-using-history-policy-in-eclipse-link
https://stackoverflow.com/questions/25032275/eclipselink-history-of-related-objects
https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Advanced_JPA_Development/Customizers
https://wiki.eclipse.org/EclipseLink/Examples/JPA/History

This is a nice approach and might help in certain situations. In my case it's not enough, I'd like to do something like git in the database, so I actually need to be able to do branching and work on different 'current' versions in parallel. But the above might help as an example how to implement such stuff.

Regards,
Andreas
You don't know me, but I've been looking all over the world for. Thanks to the help from this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1067 times.
Similar Threads
Draw the line in JPanel using variables from the database
Draw the line in JPanel using variables from the database
GUI display(rendering) problem
display
getting object instance inside nested h:dataTable
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 19, 2024 00:11:25.