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?
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.
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.
Good heavens! What have you done! Here, try to fix it with this tiny ad: