File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Complicated queries in Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Complicated queries in Hibernate" Watch "Complicated queries in Hibernate" New topic
Author

Complicated queries in Hibernate

Ray Clark
Ranch Hand

Joined: Aug 16, 2012
Posts: 52
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")?

Thank You.
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1666
    
    7

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.
http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html_single/#filters

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


[How To Ask Questions][Read before you PM me]
Ray Clark
Ranch Hand

Joined: Aug 16, 2012
Posts: 52
Ok, so if you had a "complicated query", would you do it in HQL or with the criteria builder?
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1666
    
    7

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:

(read the post by Joshua Davis)
http://stackoverflow.com/questions/4401240/criteria-v-s-hql-who-is-faster

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).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Complicated queries in Hibernate