Just Doesn’t Behave Consistently.
I’m using JDBC and Hibernate for one of my projects. The backend is MySQL. We want our users (predominantly biologists and researchers of the non-computational variety) to pick up our product and run with it with as little effort as possible. MySQL isn’t trivial to set-up so we opted to package the product with a preconfigured embedded Derby database. They won’t even know it’s there ’cause it’s that easy.
Right?
Well, not quite. Hibernate supports a variety of different SQL dialects but there are still some bugs. I tracked down the problem to the way Hibernate generates the DDL for Derby databases. By default, it forces Derby to take full control over identity columns. That means you can’t explicitly set the unique IDs manually.
That really really sucks if you’re trying to copy an entire object graph over verbatim from MySQL to Derby. Trying to remap the 300k IDs from the old values to the new Derby-generated ones isn’t an option either. That’s just the sample data set. The full version (so far) has over 50M entries.
You’d think that an object model over a relational database would make it easy to swap out different database back ends. Sadly, this isn’t the case. All the popular abstractions for database connectivity still require you to speak whatever SQL dialect your backend speaks.
Free beer/dinner for the first person who does a proper abstraction of SQL that incorporates the full semantics of the language in a nice API form that’s portable enough that you could use the same front end with any database back end with no modifications.
I’ll throw in dessert if you actually implement it.