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.