On the whole Hibernate is awesome. Yes, it has it's warts and it's very complex sometimes but once you make it a part of your infrastructure it's fantastically good. Using Hibernate with sane session management and second level caching gives on the average much better performance than would otherwise get on of hand written SQL. A good hibernate caching strategy can keep you from hitting the database at all and the best part is you just turn it on and go. If you need more performance you can use views and stored procedures for the maybe 1% of cases that can benefit from them.
Lazy initialization is a godsend for complex object graphs and HQL makes complex joins extremely readable and a breeze to write. . The biggest knock I have on Hibernate is that it's so easy to use poorly. No matter how much documentation is on the Hibernate website people insist on using it poorly.