Before I start, a big thank you to Max over at the Hibernate User forum for helping me out. The quality of the code is only ever half the reason why open source tools are the best way to go. The other half of the reason is Max, and people like him, ready to help out a complete stranger. Thanks, Max. ;-)
Anyway, what I've been busy with over the last few days is building some mappings from Hibernate to an existing legacy, err, sorry, I mean a vintage database. Hibernate is lovely. Lovely lovely lovely. But I think it's fair to say that there's a lot more work involved in mapping to an existing database than there is if you are able to just let Hibernate get on with its thing and create its own schema.
The, err, vintage database is a quarter of a century old, most of it. It's not in bad shape - I've worked with a whole lot worse - but obviously it's not put together with modern best practices in mind. Loads of natural composite keys. Sigh. Also, the data types available at the time the system was first build were quite limited. There were no booleans or datetimes available, for instance, so these are stored as CHAR(1) and DECIMAL(7,0) columns respectively, so I've had to put together my own custom user types for these. Just to make life that little bit more painful, not are the only are the dates stored as seven digit numbers (in CYYMMDD format, where a C value of 0 is for last century, 1 for this - common practice on a '400), but they can also hold all zeros or all nines!
It's an iSeries (a.k.a AS/400) DB2 database, which Hibernate supports up to a point, but what I'm going to do when I come up against platform specific things like multi-member files I don't know. I'm sure I'll think of something.
There are also a few tables with no unique keys. That's really no unique keys - it's valid for there to be multiple identical records. I'm pretty sure I'll have to go back to plain old JDBC for those tables. Thankfully, this isn't common. Even twenty five years ago it was obvious that that was a bad idea!
I'm off to work out how to do a one-to-many relation where both sides have composite keys...
Posted to Java by Simon Brunning at May 11, 2005 02:01 PMHi,
I'm a big fan of Hibernate. It's my favorite ORM tool. I've also had the agony of mapping Hibernate to an old database. It can be painfull specially if the database is denormalized and/or has a poor design.
I've seen Matt Raible write about the same issue. He says that iBatis is his favorite when the database schema is not getting along with hibernate. It's a shame I've only read that after my experiences. I've never used iBatis but a short look at it's API and what Matt says seems true. Next time I have an 'new persistence layer/old database' combination I'll have a better look.
And what precisely is wrong with a composite natural key?
It's only poor toolsets (or lazy design) that force you to put in an artificial sequenced primary key column for every table.
I say that we should fight the temptation.
Posted by: Andy Todd on May 11, 2005 11:36 PM25 years old? Amazing. I hope you are recording your paleontological findings for posterity.
My favourite thing about Hibernate is its pragmatism. If there is something that Hibernate can't do, I can always call Session.connection() to get the underlying JDBC connection and away I go.
My team has been using Hibernate to map a very poorly designed, old database into a new, idealized schema. We've found it an ideal tool to do so, and have been able to hide most of the less-ideal aspects of the legacy database from the client code. We've also been able to migrate parts of the database with each code release, and have not had any database-related breakages or bugs so far, largely in part to a set of unit tests that are run in conjunction with an automated database patchset.
One of the issues we keep running into with this 40GB+ production database is data quality -- the old schema and code had no constraints, so it's difficult to make assumptions about the state of the data. This has hurt us with Hibernate enumerated types, for instance, if a phantom row with a bad value sneaks under the radar. We address those issues via patch/constrain/codefix releases, where we rationalize the column content, apply constraints as appropriate, and roll out code that rules out the possibility of re-introducing bad data. It's worked so far.
Posted by: jeremy on May 12, 2005 05:28 AMNatural keys are just *so* 20th century, Andy. ;-)
Once you've got your mappings right, it's sweet, as you say. Jeremy. Non-unique keys and multi-members aside, that is. The problem is with writing the mappings in the first place. Doable, but tricky.
Posted by: Simon Brunning on May 12, 2005 08:53 AMAnd 20th century is bad because? I quite like the period myself, having been born in it.
My rule is that if a natural primary key has more than two elements and is used in a foreign key then you *may* be justified in replacing it with an artificial sequential key. But you'd better be prepared to justify yourself.
Posted by: Andy Todd on May 12, 2005 10:36 AMThanks for at least going back and putting your solution in the forum. It was helpful to be able to search and find a working solution to this issue. I've been working on it for a while too, so seeing your example was helpful.
Posted by: Thomas R. Hall on September 7, 2005 10:55 PM