I'm having trouble trusting the Magic Pixies. I admit, it's a common theme of mine, and maybe it's just NIH syndrome-by-proxy, but Magic Pixies - able and willing though they are - can only do your job for you if you ask them in just the right way. And sometimes they seem like they're being deliberately dumb and obstinate little buggers that just won't do as they're told, dammit!
(ahem) Perhaps I should elaborate here...
The Magic Pixies in question here are the ones that do the "hard" work for you in Hibernate, the near-as-dammit de facto standard ORM system for Java. Or Persistence Management. Or whatever term you prefer. Pete Bell has been blogging in great detail about the process of writing his own cf-based ORM framework, so if you're not familiar with ORM systems then you're probably best going to check out his blog, because this is a pure and simple straightforward bit of spleen venting.
The basic idea of Hibernate is that you can write your object model just as you choose, and then map your objects to persistent entities using (surprise surprise) an XML config file - or, if you're using Java 5 and EJB 2 and Hibernate 3, then you can dispense with the XML config file (yay!) and use annotations instead (double yay!) So long as you construct your mappings correctly, then you "don't need to worry about" the SQL - the Magic Pixies of Hibernate will auto-generate your db schema and SQL queries, and magically do your CRUD for you with a sprinkling of their Magic Pixie Dust™.
"But what if I want to do something a bit more complex than basic CRUD?" I cried.
"Like what?" said the Magic Pixies
"Like a left outer join?" I replied
"Oh, you don't need to worry about all that nasty SQL" said the Magic Pixies, " because that would tie your code to your database system, and that's a BAD THING! BAD Al! BAAAAAD!"
"Gosh, sorry Magic Pixies," I said, rather sheepishly, "I promise to use that nice abstracted Criteria API that you so generously provided in future"
"And so you should!" said the Magic Pixies, "Remember, you do the code, we do the data, otherwise we'll have harsh words with our union rep, OK?"
"Ok! Ok!" I said. "Now could you stop hitting me with that rolled-up newspaper please?"
"So long as you promise to be good"
"I do! I do!"
"But what if I want to do something more complex than that?" I asked.
The Magic Pixies looked a bit puzzled.
"What on Earth could you want to do that's more complex than that?" they replied.
"Well, what if I wanted to find a set of objects of type X that didn't have any corresponding objects of type Y that match certain criteria?"
"Pfffft!" said the Magic Pixies, " that's easy, you just create Criteria along the association paths!"
"Er, huh?" I said.
"You just create a Criteria object for class X, and then create another Criteria object using that Criteria object by passing the name of the property of class X which refers to the encapsulated class Y contained within class X!"
"Huh?" I said.
"Or if you really want to, you can use HQL"
"Yes, Hibernate Query Language. It's almost like SQL, but not quite. Because we wouldn't want you using SQL - SQL's tied to database platforms, and that's BAAAAAD"
"So how do I do it HQL?"
"You create a query that queries along the association paths and properties of the objects"
"Oh, right, OK" I said. "But what if class X doesn't have class Y as a property?"
"Er..... huh?" said the Magic Pixies.
"Class X has no property that refers to class Y"
"Well then, you won't need to query for it, will you?" said the Magic Pixies, a touch too smugly for my liking.
"But I do!" I insisted.
"Er.... huh?" said the Magic Pixies.
"Well, say if I had a table of ItemLinks...." I began.
"A WHAT of ItemLinks????"
"Sorrysorrysorry! I mean an ItemLink object..."
"...that represented a weighted link between two Items, such as might be calculated by some very complicated fuzzy logic and Natural Language Processing"
"...and a separate LinkPreference object that represented an preference expressed by a Person as to whether their ItemLink to a particular object would be public or not"
"Erm... can you give us an example?"
"Sure - this clever NLP stuff might detect that Bob from SysAdmin has been talking a lot about clustering database servers, and he might want to share that link so that he is known as an expert in that field."
"OK, with you so far..."
"But it might detect that the boss has been talking with his secretary about a dirty weekend in Brighton, and they really wouldn't want that shared at all, would they?"
"Erm, isn't that an outmoded stereotype that just reinforces age-old gender-typecast notions of sycophantic star-crossed secretaries as prey for the equally-stereotypical notion of amoral boss-as-alpha-male-predator?"
"Alright, alright, but you get the idea!" (that Magic Pixie was really starting to get on my titty ends)
"Yes, I follow you"
"So what if I want to query for all ItemLinks that have been created in the last, say, two weeks, and that don't have a corresponding LinkPreference?"
"Well, you could query for ItemLinks that have LinkPreference set to null"
At this point I was starting to snort quite heavily.
"But I told you, ItemLink doesn't have a property that refers to LinkPreference! The two are completely independent!"
"Well then you shouldn't want to query for them" said the Magic Pixies
"But I DO!"
"Well, can't you follow the association path up from ItemLink to Item and then down to LinkPreference?"
"Well, yes, I could, but wouldn't that result in the Items table being read in the query when there's absolutely no need for it?"
The Magic Pixies looked down at their feet
"And isn't that horribly inefficient?"
They started fiddling with their shorts
"And it's not that simple anyway, because it's a compond join on TWO properties!"
"WHAT was that?"
"yes!" said the Magic Pixies, with bottom lip sticking out.
"So can you perform this raw SQL query in your own way?"
LEFT OUTER JOIN link_prefs
ON item_links.item_id = link_prefs.owner_item_id
AND item_links.other_item_id = link_prefs.linked_item_Id
item_links.created_at < ?
AND link_prefs.shared IS NULL
"...might do, if you ask us nicely..."
(sigh) "OK, can you pleeeeeeease do it?"
They conferred for a moment in hushed whispers, and then turned back with a very smug-looking smile, and said
"Yes, we can - but we're not going to"
"You have to ask us in the right way"
Steam was starting to emerge from my ears
"And what IS the right way to ask you?"
They grinned even wider
"We're not going to tell you!"
And I stormed out of the room.
You see, the trouble I have with ORM systems is that they're all well and good as far as they go, and yes they can save large amounts of "donkey work" But sooner or later you nearly always come up against something that would be almost trivially easy to do with raw SQL, but the nice insulated ORM abstraction just can't deal with. I know that I'm probably looking at this from the "wrong" direction, I'm thinking about the data rather than the objects, but until the Magic Pixies start to play a bit more nicely, I'm always going to be a bit suspicious of them.
(deep breaths..... calm.... happy thoughts..... nearly Christmas....)