Sometimes It's Easier to Just Write Your SQL
Object-relational mapping (in the JPA compliant sense) is so ubiquitous nowadays in the Java development scene, that we rarely question ourselves if we really need it, and if not using it may be a better option.
In my experience, some systems benefit from using JPA technology
(most systems actually), but some do not, in fact I think some systems
become unnecessarily complicated and bigger because of it.
Imagine a mission critical system, in which you handle very big tables (be it in number of rows or sheer data size) and/or complicated queries. Usually in this scenario, the optimization and proper usage of the database is a bigger issue than how long it takes a developer to code a specific query or update sentence.
The scene I see repeating itself in a lot of projects is a code base that starts with very beautiful and elegant ORM based queries (be it API based, HQL or similar) and maybe a few native SQL queries for very specific features, but once the system is deployed to a production environment and faces reality you start to migrate to a code base where most (or all) of the database related interactions are expressed as native SQL queries so you can squeeze every ounce of performance your DBMS is capable of, and use all those vendor-specific features that make your use case 10 times faster than solving them on the Java side.
Wouldn’t it be better then, in cases similar to this one, to use a framework or design pattern lighter than a JPA compliant framework, and include the design and coding of SQL sentences from the beginning of the project?
If you think so, I encourage you to re-visit the DAO pattern and consider coding your data access layer yourself, or better yet, use a lightweight framework like MyBatis that provides a lot of useful features but is still based on writing your own SQL sentences.