• Post Reply Bookmark Topic Watch Topic
  • New Topic

JPA for table with versioned data  RSS feed

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • 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: 21135
87
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!