Sorry to hear that you don't like Hibernate. I'm using it for a project right now, and I greatly prefer it to ActiveRecord. The trick is to use a framework (yes, a framework!) like Grails to autogenerate a lot of the configuration crap for you.
I suspect that your large project was built by "Java Engineers" who tried to stuff way too much into the ORM layer. That's an abuse of the tool, not an indictment of the tool itself.
It was definitely done by "Java Engineers", but the ORM layer is pretty clean. My problem is that the Java way is very class-centric, not really OO. Why use a class at all, if all you have are getters and setters? It makes sense to me for the ORM layer just to build a list or hash from the result set. A class has no intrinsic benefit, because it's just a struct with extra baggage. One way to get the SQL complexity out of the way is to use a stored procedure, but as far as I can tell Hibernate doesn't support them.
I suspect that your large project was built by "Java Engineers" who tried to stuff way too much into the ORM layer. That's an abuse of the tool, not an indictment of the tool itself.