Back in 1999, I worked on a Java-based CRM product called "E-volve". It was the height of the internet bubble, and we were going after the enterprise market, so we had to be 100% Java, use EJBs, XML, you name it.
At the time entity beans seemed like a good idea, but they were poorly supported in EJB 1.0 to say the least. In fact, they were all but useless for a big system like ours, and so we decided to write our own O/R mapper.
I worked on it for a few months, and even though it ended up a bit of a mess due to internal politics, the result had some interesting ideas. It worked, but it wasn't what you would call a nice system. In fact, it was a bit of a pain, and people used it because they had to, not because they wanted to.
And I really, really hate that.
To help sell the system, we tried to replace our in-house mapper with a 'standard' O/R mapper that was en-vouge at the time. A few month and a million dollars later the project failed miserably. Bubble burst, company went under, and I left with a distinct feeling that O/R mapping is not was not it.
On my next projects, I decided to go back to basics and use vanilla JDBC. Simple, efficient, well understood. However, I quickly remembered why I didn't like it: building dynamic SQL via large amounts of hard to understand code, trying to make sense of 20 question marks in a row, etc. It was feeling like a perverse mixture of high level language with bits of assembly thrown in for no good reason. JDBC was not the answer either.
But it wasn't until I had to work again with a complex O/R mapper that I've decided to do something about it. Through the years I've dealt with the problems enough times that I had a pretty good idea what I wanted: a framework that is simple, flexible, and powerful. And above all, one that people would use because they enjoy working with it.
The root problem with O/R mappers is conceptual rather then an implementation shortcoming. Most O/R mappers try to isolate you from the database as a matter of principle. This is a generous goal in theory, but in practice it fails on a number of levels.
First, it creates a rather tight coupling between your model and the database layout. For simple projects this is may not be a concern, but a lot of these projects will end up growing, and months later you will find yourself in a tight spot because you didn't take the time to define a proper interface to your database.
Second, it reinvents SQL, poorly. Any data access technology needs a language to interact with the data. SQL is such a language, and while not perfect, it is well known and understood. The problem space is difficult, and SQL took a long time to get where it is today: it has good implementations, it is well understood, and it is accepted by virtually everybody. Reinventing the wheel in this case would be a gargantuan task, and as a result most efforts will settle for less. Even if you manage to get your language as powerful as SQL, it will not be SQL. People won't know it, there will be less documentation, and so on, and so forth. At the end of the day, what is the point? You would be replacing a tedious but simple task (that of writing JDBC code) with a complex and less obvious one (that is using a difficult new language that you don't understand). Not a good tradeoff.
Third, you loose control. All such efforts will ultimately generate SQL, but in ways that you do not control. To add insult to injury, a lot of these frameworks force you into making compromises when designing your model, due to understandable practical limitations . These effects will not be visible at the beginning of the project, when things are small and performance doesn't matter, but have a tendency to rear their ugly head towards the end of the project. And then what?
Not much, really. It accomplishes what it is advertized to do, and it does a fair job at it. Being the base database access technology for a language such as Java is not simple: you need to be able to support obscure corner cases, various access patterns, and plethora of programming styles.
With all that power and flexibility, there is a certain degree of complexity and verbosity that bubbles up to the API level. Trying to match arguments with question marks in prepared statements for example is awkard at best. Doing the same thing by generating dynamic SQL is even worse. And this is just the tip of the iceberg.
Most applications do not require all that JDBC has to offer most of the time. In such cases, the above mentioned complexity is simply unnecessary, and just detracts from the task at hand. Even in complex applications, the bulk of SQL usage is fairly simple. Ideally, we shouldn't complicate the majority of the use cases for the sake of a few exceptions.