OR Mapping layers

Having just released version 2 of my EoD SQL API (see yesterdays post) today with some massive bug fixes, I’ve been thinking about the way I code, and some of the code I’ve seen others write in my life time. Having worked in a largely “Enterprise” (note the upper-case) environment: EJB, EJB, EJB, and Very Large Database Systems, I’ve seen a lot of SQL code, a lot of OR mapping, and a lot of headaches and pain caused by both (and sometimes by a lack of coffee). So what have been my thoughts on the subject? Well this is gonna sounds really self-serving, but: most OR mapping layers I’ve worked with really suck.

JDO was one of the best (mainly cause I was working with JDO Genie), and that was mainly cause the JDO Genie work-bench let me write lovely little objects, and then built the database schema for me. Having grown up with OO as my first understanding in programming… this was really cool for me. However, once I tried to use this system with a 20 year old legacy database that had over 200 tables of data, I found myself wishing I could just use SQL instead (though my understanding of SQL at the time was almost non-existent), but the application I was working on, was not written by me… I was just the maintainer.

After trawling through several other implementations of JDO, and one or two home-grown OR mapping layers, I worked on a system that was based on Hibernate, Spring and XDoclet. At first I thought this combination was awesome, it dealt with Spring had some lovely code to deal with a CLOB restriction in Oracle’s database drivers (we were working against a 10g database). Most of the config was generated for us, and hibernate did all the mapping to the database. Wonderful! Until I started getting weird errors and bugs (even my unit tests started failing). Why? To this day I have no idea. less than 2 weeks before the project deadline, I swapped all my Hibernate, Spring and XDoclet code for direct JDBC SQL to the database in desperation. The project was released, and to my knowledge is still working fine.

I guess what all this amounts to is: I like working with SQL cause then I’m not having to talk to the tables, I can write any query I want, and not have to create 120 view’s in the database to keep my OR mapping layer working and happy. But at the end of the day, you still wind up needing objects in memory. Probably why I really loved the EoD RI from Java 6 beta, and why I started the EoD SQL project.