This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
I spent a few years working on a team that did queries against a Data Warehouse. Some of these queries were really complicated so most of them were done by an Application DBA whose expertise was SQL. Now I am learning Hibernate and the examples that I've seen are pretty simple joins between tables. If I had a need for a massive query to retrieve data (one that spanned 5 pages or more of printout because of all of the joins and subqueries), I would think that it would be best to put that in a View and have Hibernate query against the View.
1) Is that the best approach for complicated queries?
2) Can Hibernate query against a view simply by setting that @Table(name="viewname")?
As long as your mappings and caches are correct, Hibernate will have no trouble generating queries (even very complex ones) that are usually more efficient than most developers would write themselves. Setting things up correctly is of course the catch because when things are not correct the application may work, but operate less optimally than had an ORM not been used. To quote Gavin King (Hibernate founder) from his book:
Given a persistence task, many optimizations are possible. Some (such as query hints) are much easier to achieve with hand-coded SQL/JDBC. Most optimizations, however, are much easier to achieve with automated ORM. In a project with time constraints, hand-coded persistence usually allows you to make some optimizations. Hibernate allows many more optimizations to be used all the time. Furthermore, automated persistence improves developer productivity so much that you can spend more time hand-optimizing the few remaining bottlenecks. Finally, the people who implemented your ORM software probably had much more time to investigate performance optimizations than you have. Did you know, for instance, that pooling PreparedStatement instances results in a significant performance increase for the DB2 JDBC driver but breaks the InterBase JDBC driver? Did you realize that updating only the changed columns of a table can be significantly faster for some databases but potentially slower for others? In your handcrafted solution, how easy is it to experiment with the impact of these various strategies?
That said yes, if a view exists Hibernate would treat it just like it does any other table. However some databases may not handle inserts or updates on views so that is something to be aware of. You can also use Hibernate Filters which are similar to views but applied at the HIbernate session level. There is more on that in the reference documentation.
Also there are advantages and disadvantages to views, query performance however is not one of them, the execution plans are identical between the SQL and database views. Of course if you have a complicated query to construct a view, and developers or data analysts that are not so good at SQL that would be a good argument to perhaps use a view. If you only needed to access specific parts of the view you may be joining in tables you don't need creating extra IO overhead for large datasets than if you had just used the base tables. In this case the view may harm performance. Views are often used to restrict access to sensitive data.
Hopefully I answered more questions than I created
If the query was dynamic in nature I might consider criteria builder but otherwise I would use HQL just because it is easier to read. I read an interesting post on this awhile back I found it again here:
Actually I am not quite sure why it was said that JPA does not have Criteria. It does. But the other points are still valid. In reality most of my development these days is JPA with hibernate as a persistence provider. I usually write JPQL/CriteriaBuilder and resort to Hibernate specific stuff only when the JPA spec can't give me what I need (Happens more frequently than I would like).