I have so many tables that need to store historical data inside. Each time some column get changed, the row will be cloned and a new verion number is assigned to it. Different versions of rows exist together in the same table. Moreover, tables have one-to-many or many-to-many relationships. For example:



Every time the name get changed, a new version will be created. The id will be the same and the system id is new (generated using sequence). Version get increased. Also, in each table, there are effective date and end date columns to indicate the valid period of the row.

In this case, if I do a one-to-many mapping from parent to child table. It will return me all the children records. But most of the time I just need one, which is determined by the date I pass (effective date <= date <= end date). The same for the case from child to get parent.

It seems that Hibernate doesn't support this kind of data fetching. Currently I have to manually write query to search for the specific child record and construct the object graph by myself. Is there any better way to do that?

Take a look at virtualization (aka filtering), it may buy you what you need w/o having to hand code your queries.

Your implementation is somewhat similar to the piece of work that we implemented except that our 'version' tables hang off the main ones. That way we have some static data, such as PKs that never change in the main tables and time effective columns down in the child version table.

Every other child relationship comes off the main 'parent' table and then you can set up filters on the 1 to manys and all other relationships.

Hibernate can definitely handle what you want to do as we've been using it in that manner since whenever filters were introduced. There are a few limitations (i.e. they can't implement filters on Subselects yet) but alot of the initial bugs have already been hammered out and you'll be stepping into a more mature implementation than what we did when we first got started with them.

Have you tried to create views for this stuff ?

Thank you all for the reply! You are right. Actually I plan to add one more table to store the parent id.



And then make the parent id --> parent table and child table one to many. Otherwise I can't link them together. This kind of approach seems a little bit ugly (table with only one column). But it seems to be the only way to do it.

For the filtering stuff, are you using this kind of code? I think it works. Let me try.



Baliukas, my requirement is to get any record by any specified date which falls into the period between effectiveDate and endDate. Is view a solution for that? If so, how to achieve it? Thanks!

Use Filters!

I'm with gavin on this one, use filters. Your requirements seem much 'simpler' than the ones we have. i.e. it seems that you want to keep historical data simply for the sake of historical data.

In our case, we have to support an 'amendment' procedure where the user can go back to a particular period and modify the data for just that period. So we end up with lots of different time slices, some of which end up overlapping.

We use a combination of effective/inactive and create dates to pick out the most effective record for any time period. Some of the filters get nasty but we use a template and then generate the exact ones using xdoclet (custom xdt).

But as for the filter use themselves, don't get confused with the collection filter stuff off of the session. I'm talking about the filters that you can define in your mapping files and then enable on the session.

i.e.

Thanks for the replies! It is exactly what I need.

I write some test code and find out that if I put the filter in a one-to-many relationship, the filter only take affect before the collection is initialized. That is:



So Gavin, is it possible to make the case 2 also work? Since when a user calls enableFilter, he must be expecting that the filter will take effect in the following calls until he invokes disableFilter.