Most simple queries won't benefit much from hand-coded SQL and it's always there for the minority of the ones that do.

And again, there is the middle ground of writing something like custom HQL that returns an object(s) of interest.

Again, there is a huge amount of error-prone boilerplate that is avoided simply by doing that, the next time you add a column you don't have to chase down 27 different hand-coded functions manually populating one field at a time.