I recently got a Bugreport from my manager that she was seeing double rows in one of our screens. After a little research I found the culprit to be the following (paraphrased) HQL query.
SELECT order from Order order LEFT JOIN FETCH order.items
The resultset returned from the database will look like this:
| orderid | customerid | date | orderitemid | orderid | articlenr | amount |
|---|
| 1 | 1 | 2015-01-01 | 1 | 1 | 245 | 5 |
| 1 | 1 | 2015-01-01 | 2 | 1 | 574 | 1 |
As you can see, the order appears twice, once for each order item.
What we want to get from Hibernate is:
What we get from hibernate is:
| Order |
|---|
| Order@25AF4E |
| Order@25AF4E |
However, this is by design. As you can see you get two Orders, but they both refer to the same instance. Things are as it should be.
To fix this, see the great answer by Gavin King
1 or on StackOverflow
2.
When changing the result into:
SELECT DISTINCT order from Order order LEFT JOIN FETCH order.items
The double rows disappears, however the distinct does go through to the database (where it does nothing, as the rows are already distinct) however, it also adds the
ResultTransformer implicitly, causing me to get the one row as designed.
Talked about it with the Architect, he doesn't like the use of 'distinct' in HQL queries, because:
- it is unclear how it will be processed (locally or in the database)
- may be a cause for performance issues if the distinct is forwarded to your database
- In general, if you need a 'distinct' in your query to defeat double data, chances are that the query could use some improvements
With an explicit
ResultTransformer, the code looks like the following:
List result = session.createQuery("select o from Order o left join fetch o.items")
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.list();
And the problem is fixed.
References
- [1] JBoss - Hibernate FAQ
- 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
- [2] StackOverflow - Hibernate Criteria returns children multiple times with FetchType.EAGER
- http://stackoverflow.com/questions/1995080/hibernate-criteria-returns-children-multiple-times-with-fetchtype-eager
