Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Vin Smith
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic