No way you would create a join with pure SQL, filling all the objects fields by hand is just too much pain. I found HQL very intuitive to use, it is almost exactly like SQL, only with a few shortcuts. The learning curve was not so steep either.

Hibernate does one thing, and does it well. With the annotations, it has improved even more. What parts of it do you consider magic? Magic is not necessarily a bad thing - I like the magic of higher level programming languages that shield me from the gory details of assembly language. I just don't like it if code is not readable anymore without knowing the magic.

I will look into Cayenne on occasion. Hibernate is the industry standard now, though, and for a good reason in my opinon.

I would like to move to a more dynamic language, though...

Maybe using GORMS (the ORM from Grails, based on Hibernate) standalone in a Java project would be nice.