I use both raw SQL and Hibernate in my applications.

For simple CRUD (Create, Read, Update, Delete) operations Hibernate is excellent and does the job damn well!

But as your data model gets more complex, you need to write more complex queries. For example if you need to build a tree with child/parent nodes. This can only be done with CTE(Common Table Expressions) https://www.postgresql.org/docs/10/static/queries-with.html

Hibernate and "all" other ORM's out there doesn't support CTE's and this is where you want to use raw SQL's instead. There are also other examples where I want to use specific function, for example the array_to_json function in PostgreSQL.