File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes [Hibernate] How to handle DB history - i.e., insert only, no updates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "[Hibernate] How to handle DB history - i.e., insert only, no updates" Watch "[Hibernate] How to handle DB history - i.e., insert only, no updates" New topic
Author

[Hibernate] How to handle DB history - i.e., insert only, no updates

Chris Johnston
Ranch Hand

Joined: Dec 13, 2004
Posts: 85
Hello,

I have a question about how to handle insert only, no update database schemas with Hibenrate. In short, the problem is this:

* in the database, there is a central table that holds the primary key to say a person and then off of the person table, there are tables that hold the actual person data with foreign keys in the other tables (from the person table)
* history of all data entered into the database must be kept
* this means that there are no updates, just inserts

however, in the application, and in hibernate, the objects in the object graph related to a person are simply updated, how do you handle this?

For a more complete explanation of my question, please visit my website (I even have pictures)

http://www.fuzzylizard.com/archives/2005/08/04/598/
[ August 04, 2005: Message edited by: Chris Johnston ]

www.fuzzylizard.com
Steve McCann
Ranch Hand

Joined: Oct 20, 2004
Posts: 81
If I remember correctly, Hibernate checks to see if the object exists in the database first and updates it if is there or inserts it if not. If you could create a new primary key (personID?) for the object then it should be inserted rather than updated.

I can think of two non-Hibernate ways around your problem:
1. Have a flag to show whether the object is 'live' or not and set this as an attribute of the Java class.
2. Have a database trigger which copies the old row to an audit table. Your normal table, Person, then holds live data while the historical data is there in the audit table, Person_Audit(?). (I am thinking of Oracle here - don't know if you can do it using your DB).

Steve
[ August 05, 2005: Message edited by: Steve McCann ]
Chris Johnston
Ranch Hand

Joined: Dec 13, 2004
Posts: 85
Your second option is actually the solution that we are going with, I was just wondering if there is another way.

With respect to trying to do it in Hibernate or Java, I still have a few questions:

(these comments are using the diagrams on my site where I have a person, address, and phone table and classes)
1. One possible solution is to create a new object, say a new address, when that bit of data changes. Then associate that object with a Person. This would mean that the new Address object would not have an ID and Hibernate would insert it to the address table.

However, this would mean that whenever I load a Person, I would get every record associated with that Person. Meaning, Hibernate would load the entire object graph -- i.e., every address that person ever had.

So, is there a way to tell Hibernate to only load the most recent record? I agree that some form of active flag is needed for the most current row in the history tables. As such, is there any way of telling hibernate to only load rows where active=true in a mapping file so it does not load the entire object graph?

2. To try and solve the above problem, I could once again create a new address object and associate it with a Person. Then, I could take the old object and dissasociate it with the Person. Basically replace the old with the new object. However, would this not make Hibernate delete the old record? Or would this depend on how I set the cascade attribute in my mapping file?
Steve McCann
Ranch Hand

Joined: Oct 20, 2004
Posts: 81
So, is there a way to tell Hibernate to only load the most recent record? I agree that some form of active flag is needed for the most current row in the history tables. As such, is there any way of telling hibernate to only load rows where active=true in a mapping file so it does not load the entire object graph?

Not that I know of. I have done this with an active flag, but took care of it in the HQL query. I am trying to think of how I did it but it was while ago.

To try and solve the above problem, I could once again create a new address object and associate it with a Person. Then, I could take the old object and dissasociate it with the Person. Basically replace the old with the new object. However, would this not make Hibernate delete the old record? Or would this depend on how I set the cascade attribute in my mapping file?

I think the association of the new object will also take care of the disassociation of the old one, and I don't think Hibernate will automatically delete the record (but you should check this )

Steve
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: [Hibernate] How to handle DB history - i.e., insert only, no updates