0

I have duplicate in result in hibernate query, such as:

select new ValueObject(h.id, c.firstName, c.lastName) 
from HistoryTable as h left join CustomerTable as c
where h.customerId = c.id and c.notDeleted
order by c.firstName, c.lastName

But, when i used DISTINCT, duplicate in result continue to appear

select distinct new ValueObject(h.id, c.firstName, c.lastName) 
from HistoryTable as h left join CustomerTable as c
where h.customerId = c.id and c.notDeleted
order by c.firstName, c.lastName

But my question is, if there is any possibility to using DISTINCT for excluding duplicates for creating new ValueObject in HSQLDB query?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Aventes
  • 569
  • 1
  • 8
  • 20

1 Answers1

0

Hibernate does not return distinct results for a query with left or right join. You can use Hiberante setResultTransformer for your purposes. For more detail's explanations, why and how it resolve, look:

https://developer.jboss.org/wiki/HibernateFAQ-AdvancedProblems#jive_content_id_Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword

and

How do you create a Distinct query in HQL

Community
  • 1
  • 1
Alexey Semenyuk
  • 3,263
  • 2
  • 32
  • 36
  • Thank you for thing with setResultTransformer. I have not knew this function. But really distinct is work with left outer join, and i have found cause of the fault immediately after placement of the post. – Aventes Sep 23 '14 at 20:10