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 Want to avoid multiple SELECT when using Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Want to avoid multiple SELECT when using "new" in HQL" Watch "Want to avoid multiple SELECT when using "new" in HQL" New topic
Author

Want to avoid multiple SELECT when using "new" in HQL

Vin Smith
Greenhorn

Joined: May 22, 2009
Posts: 5
Consider a simple class heirarchy



This query gets around the N+1 select issue for the Car objects by using LEFT JOIN FETCH


The SQL generated is pretty sane:


However, I want to instantiate a new Data Transfer Object EmployeeData using various data (employee + employee.address) (yes, I know this example is a bit contrived since we can get the address from the employee anyway, .. .but in real life, we might want other data from a JOIN with another table etc etc ... . I use the following HQL



This is what I see in the debug logs.
1. How can I avoid all the extra SELECTs while still being able to instantiate the DTO EmployeeData object? Alternatively, is there another preferred way of obtaining various bits information from (potentially different classes) and returning them in a concise and convenient manner (like my DTO object).
2. Are all the "load" operations actually executing new selects (as per the hibernate session.load() method)?

Thanks in Advance

Vin Smith
Greenhorn

Joined: May 22, 2009
Posts: 5
To answer the 2nd question, yes, . there is a separate SELECT query statement executed for each of the "load" statements". I turned on SQL query logging on the database and observed this in real time.
xsunil kumar
Ranch Hand

Joined: Dec 14, 2009
Posts: 133
Hibernate Query language will use N+1 number of queries for many to one mapping. One for first object and N for fetching associated entities. If you want to avoid N+1 number of quries, then you can try with Native query.
Vin Smith
Greenhorn

Joined: May 22, 2009
Posts: 5
Not necessarily, .. here's one way I found to do achieve something close without invoking N+1 queries, using a result transformer (only a single query)



resulting in



I still haven't managed to get the result transformer to a data transfer object to work correctly yet, though.
Vin Smith
Greenhorn

Joined: May 22, 2009
Posts: 5
Okay, success with the DTO transformation. Here's how you do it:



The key is to use the exact member names i.e. employee, employeeAddress in the select statement (as opposed to arbitrary aliases such as emp, empAddr, etc ...) , as I'm guessing these are used in a reflection type mechanism to determine the appropriate setters

Only 1 select.

Joy!!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Want to avoid multiple SELECT when using "new" in HQL
 
Similar Threads
fetch="subselect" and fetch="join" not working as expected with HQL
Hibernate generated query for a inheritance mapping
composite-id, one-to-one, formula, and an outer join
Hibernate generating queries with duplicate columns
join in 3 tables