-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
vinayb
 Post subject: How to avoid multiple SELECT when using "new" in HQL
PostPosted: Fri Dec 11, 2009 7:26 pm 
Beginner
Beginner

Joined: Thu May 28, 2009 10:25 am
Posts: 21
Consider a simple class heirarchy
Code:
@Entity
@Table(name = "ABSTRACTEMPLOYEE")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
public abstract class AbstractEmployee {

   @Id
   @Column(name = "ID")
   @GeneratedValue(strategy = GenerationType.AUTO)
   private long id;

   @Column(name = "NAME")
   private String name;

   @Column(name = "ADDRESS")
   private String address;

   @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH })
   private Car car;
.
.
.
}


This query gets around the N+1 select issue for the Car objects by using LEFT JOIN FETCH
Code:
emp = (List<AbstractEmployee>) session.createQuery("from AbstractEmployee a LEFT JOIN FETCH a.car").list();


The SQL generated is pretty sane:
Code:
Hibernate: /* from AbstractEmployee a LEFT JOIN FETCH a.car */ select abstractem0_.ID as ID2_0_, car1_.ID as ID1_1_, abstractem0_.ADDRESS as ADDRESS2_0_, abstractem0_.car_ID as car5_2_0_, abstractem0_.NAME as NAME2_0_, abstractem0_2_.CE_HOURLYRATE as CE1_4_0_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_0_, abstractem0_.DTYPE as DTYPE2_0_, car1_.LICENSEPLATEID as LICENSEP2_1_1_, car1_.MAKE as MAKE1_1_, car1_.MODEL as MODEL1_1_, car1_.YEAR as YEAR1_1_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID





However, I want to instantiate a new 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
Code:
emp = (List<EmployeeData>) session.createQuery("select new com.me.dbstuff.data.EmployeeData(a, a.address) from AbstractEmployee a").list();


This is what I see in the debug logs. How can I avoid all the extra SELECTs while still being able to instantiate the DTO EmployeeData object?

Thanks in Advance
Code:
Hibernate: /* select new com.me.dbstuff.data.EmployeeData(a, a.address) from AbstractEmployee a */ select abstractem0_.ID as col_0_0_, abstractem0_.ADDRESS as col_1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Hibernate: /* load com.me.dbstuff.data.AbstractEmployee */ select abstractem0_.ID as ID2_1_, abstractem0_.ADDRESS as ADDRESS2_1_, abstractem0_.car_ID as car5_2_1_, abstractem0_.NAME as NAME2_1_, abstractem0_2_.CE_HOURLYRATE as CE1_4_1_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_1_, abstractem0_.DTYPE as DTYPE2_1_, car1_.ID as ID1_0_, car1_.LICENSEPLATEID as LICENSEP2_1_0_, car1_.MAKE as MAKE1_0_, car1_.MODEL as MODEL1_0_, car1_.YEAR as YEAR1_0_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID where abstractem0_.ID=?
Top
 Profile  
 
vinayb
 Post subject: Re: How to avoid multiple SELECT when using "new" in HQL
PostPosted: Mon Dec 14, 2009 3:57 pm 
Beginner
Beginner

Joined: Thu May 28, 2009 10:25 am
Posts: 21
Result transformer did the trick (result data is captured in a list of EmployeeData transfer objects):
Code:
         Query query = session
         .createQuery("select ae as employee, ae.address as employeeAddress from AbstractEmployee ae LEFT JOIN FETCH ae.car ")         
         .setResultTransformer(Transformers.aliasToBean(EmployeeData.class))
         ;
               
         emp = query.list();


and the resulting SQL:
Code:
Hibernate: /* select ae as employee, ae.address as employeeAddress from AbstractEmployee ae LEFT JOIN FETCH ae.car  */ select abstractem0_.ID as col_0_0_, abstractem0_.ADDRESS as col_1_0_, car1_.ID as ID1_1_, abstractem0_.ID as ID2_0_, car1_.ID as ID1_1_, abstractem0_.ADDRESS as ADDRESS2_0_, abstractem0_.car_ID as car5_2_0_, abstractem0_.NAME as NAME2_0_, abstractem0_2_.CE_HOURLYRATE as CE1_4_0_, abstractem0_3_.FE_ANNUALSALARY as FE1_5_0_, abstractem0_.DTYPE as DTYPE2_0_, car1_.LICENSEPLATEID as LICENSEP2_1_1_, car1_.MAKE as MAKE1_1_, car1_.MODEL as MODEL1_1_, car1_.YEAR as YEAR1_1_ from ABSTRACTEMPLOYEE abstractem0_ left outer join BASIC_EMPLOYEE abstractem0_1_ on abstractem0_.ID=abstractem0_1_.BASIC_EMPLOYEE_ID left outer join CONTRACT_EMPLOYEE abstractem0_2_ on abstractem0_.ID=abstractem0_2_.CONTRACT_EMPLOYEE_ID left outer join FULLTIME_EMPLOYEE abstractem0_3_ on abstractem0_.ID=abstractem0_3_.FULLTIME_EMPLOYEE_ID left outer join CAR car1_ on abstractem0_.car_ID=car1_.ID
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.