15.2. Using native SQL queries

HQL, JPA QL, or criteria queries should be flexible enough to execute almost any query you like. They refer to the mapped object schema; hence, if your mapping works as expected, Hibernate's queries should give you the power you need to retrieve data any way you like. There are a few exceptions. If you want to include a native SQL hint to instruct the database management systems query optimizer, for example, you need to write the SQL yourself. HQL, JPA QL, and criteria queries don't have keywords for this.

On the other hand, instead of falling back to a manual SQL query, you can always try to extend the built-in query mechanisms and include support for your special operation. This is more difficult to do with HQL and JPA QL, because you have to modify the grammar of these string-based languages. It's easy to extend the Criteria API and add new methods or new Criterion classes. Look at the Hibernate source code in the org.hibernate.criterion package; it's well designed and documented.