You should use Hibernate if you have a nontrivial application (definition of nontrivial varies, but I usually think of Hibernate being less applicable to applications with only ten tables or so) that use an object-oriented domain model. Not every application needs a domain model, so not every application needs ORM. But if your application does a lot of business logic - rather than just displaying tabular data on a webpage - then a domain model is usually a good thing.
Hibernate really starts to shine in applications with very complex data models, with hundreds of tables and complex interrelationships. For this kind of application, Hibernate will take away a huge amount of coding effort (perhaps up to 25%, for some applications) and will result in an application that performs better than the alternative handcrafted JDBC. This is possible because some kinds of performance optimizations are very difficult to handcode: caching, outer-join fetching, transactional write-behind, etc.
