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
  • 34,719
  • 24
  • 185
  • 251
Aventes
  • 509
  • 1
  • 6
  • 19

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,183
  • 2
  • 29
  • 34
  • 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