• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JPA for table with versioned data

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
K D Clark
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the suggestion!  At a quick glance this looks very interesting and I'll start digging in.
 
Greenhorn
Posts: 1
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Could you hold this puppy for a sec? I need to adjust this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic