Implementing a history / versioning solution for a Hibernate-based application (with a twist)

Question

First, the main facts: Java webapp, Spring, Hibernate, MySQL.

The situation is that I have a complex object model, for example. a car . It consists of many objects (Engine, Tires, ...) with one-to-many and one-to-many relationships between them.

Now there are a lot of cars, and from time to time someone checks the car and creates a Test Report . The report applies to many parts of the car, displaying their properties, etc.

Until now, the system did not support the ability to update the properties of the car and its parts after they are submitted to the system. This means that if the color of the chassis or the number of tires has been changed, old reports reflect this change, which we do not want.

Well, now this feature has been requested. Cars and their parts must be modifiable, and version history must be created. In older reports, you must reference the old versions of the parts and their meanings.

I looked at “ slowly changing dimensions ” and it seems that version control of the car and its parts can be done using Approach Type 6.

A thing (twist) that I find it difficult to figure out (possibly due to my limited experience in sleep mode):

How can I collect copies of my report with Hibernate so that they refer to the correct versions of each part of the car? There is a date in the reports, and each version of the car parts will have date ranges when they were valid, so I think I could do it with some complex HQL / SQL language. But is there a simpler, more automatic way to do this with Hibernate?

Answer 1

I used the approach you suggested (type 6) with Hibernate, and it worked fine for me. Queries for reports are a little more complicated, but not so much, because all requests required the same sentence (for example, 'and: reportTime> = x.startTime and (: reportTime <x.endTime or x.endTime zero)').

I created an interface and a base class (the interface is only needed for temporary subclasses whose parent was not temporary) for objects that support this approach with 2 properties (e.g. startTime and endTime) and a base class for DAO working with temporary objects that often needed in some functionality. Things I put in this DAO base:

• Preventing changes to instances whose startTime has passed (except for setting endTime in the future)
• Automatically close (i.e. fill the end time) of the previous instance if a new instance was added (e.g. oldInstance.endTime = newInstance.startTime)
• Adding a standard sentence for selecting current objects at the time of the query for HQL queries.
• Work with duplicates, if for some reason two valid instances / versions were immediately found (I ordered my queries with "startTime desc" and took only the first data)

The only part where I found that it is really messy is working with regular SQL queries, which are also executed against the database, where additional sentences are needed for joined tables or subselects.

Answer 2

You can look at JBoss envers for version control of your objects. I'm not sure if it is suitable for your use, but take a look at it.

Answer 3

MySQL supports triggers . Set the trigger so that whenever the line has been changed, the trigger copies the line to the archive table along with the timestamp. This saves all previous versions of the data with which you can run reports.