Here's what I've seen in my experience. (Of course they are my experiences, and reflect my opinions, I'm sure that there are many people who don't agree with them! I welcome any comments or ...)

Although ORMs (and specially Hibernate, when introduced) are very genius designs (that's why they became so popular), in many cases they are not the best solution to follow. Most of the time, when you use Hibernate (or other ORMs) the added complexity is much more than the benefit.

When your project  grows larger, sometimes performing sometimes you get stuck on how to perform some simple and stupid data  operations.

One important point when considering the data access  approach, is predictability and the way you plan your project. Even if using direct SQL commands (say if you're not even using iBATIS), and you spend a lot more time on the data access compared to using ORMs, you get simpler, thus more managable data access development. Sometimes this is of a great value for a project that needs to be strict on the time and budget.

You can also add these facts to the above:
1. In large projects, added complexity of ORMs often causes a lot of problem-resolution times (during some of which many members of the team are stuck!) which compensates for the added time of coding (and even changing) the data access layer  using direct SQL.
2. In general, a developer who knows how to operate Hibernate and resolve issues is more expensive than a developer that knows plain data access coding using JDBC or using something like iBATIS. (Same as the learning curve fact noted by Amin)
3. Fine tuning and optimizing the data access (specially in data-intensive software, which covers a large portion) is much easier when using direct SQL commands.

In general, using ORMs like Hibernate can be more effective in small-to-medium size projects overall. Also if you need a very specific feature (like highly-dynamic SQL schema during development phase, and using SQL Schema Generation), using Hibernate is good. Otherwise I don't recommend it!

If a team is using Hibernate, my recommendation is that it is absolutely necessary that one of the team members is a real pro in it. (I mean at the source code level). Otherwise expect to have stalls in your project plan. So, I think that if you have a Hibernate Pro on the team, it is reasonable to think about using Hibernate (unless the project is really small).

In one project my team used a code generator to create and maintain data access code, using Java 5. (I don't remember the name, but it's very popular, and NOT free / opensource). I was MUCH happier than when I was using Hibernate. I have friends in a few project who used Hibernate or NHibernate in their projects, and many of them defended the Hibernate seriously; but after a while they are very unhappy with it. And right now, in my current project on .NET, I'm using an EXTREMELY simple (less than 1000 LoC) library, coded by the team members, just to prevent hard-coding SQL commands in the code, and read them (and column mappings) from XML to increase maintainability. And I'm again much happier than when I was using Hibernate and NHibernate.