Search code examples
javahibernatehsqldb

HSQL. How to exclude duplicate elements using Distinct

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?

Solution

  • 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