I am thinking if we need to decide whether we should go for Hibernate or not, then what are the decision criteria? Is hibernate always a good choice to build the DAO layer or there are some scenarios where Hibernate should be avoided?
If you're doing very simple database operations or running at low scale, the complexity and overhead of an ORM system such as Hibernate may not be cost-effective.
However, as your needs get more and more demanding, the benefits of an ORM really begin to shine. First of all, junior developers only really need to know Java and not the kinks of raw SQL, secondly, it's easier to get data into a form that the rest of the app can deal with without doing a lot of grunt work (Hibernate + Spring is even better for that). Third, you can often define a set of standard modules for database interfacing, which can save a lot of time and effort thanks to re-usable code and standard practices.
And finally, as the volume and complexity of your database operations scale up, Hibernate can actually run more efficiently than hand-coded SQL JDBC.
SQL, like assembly language is, in theory, the most efficient way of doing things, because it directly addresses the underlying hardware (or database, in this case). However, the sheer magnitude of work involved to manually re-tune everything based on application changes and measured workload becomes too expensive. Hibernate, being an automated system can do such things with relative ease, and so can out-perform hand-coded SQL in such cases. Benchmarks have measured as much as twice the throughput in some cases. But, returning to my first statement, that depends on your workload. At the lower ends, Hibernate's own overhead would cost more than simple SQL JDBC code.
Blitzlügen - Lies or information broadcast, but when called out the broadcaster does little or nothing is done to correct them, thus allowing those who wish to believe to accept them as truth.
Lügensturm - A barrage of Blitzlügen fired in such quick succession that it is essentially impossible to correct them all.
Fire me boy! Cool, soothing, shameless self promotion: