Hibernate Criteria returns children multiple times with FetchType.EAGER

Question

I have an Order class that has a list of OrderTransactions and I mapped it with a one-to-many Hibernate mapping like so:

@OneToMany(targetEntity = OrderTransaction.class, cascade = CascadeType.ALL)
public List<OrderTransaction> getOrderTransactions() {
    return orderTransactions;
}

These Orders also have a field orderStatus, which is used for filtering with the following Criteria:

public List<Order> getOrderForProduct(OrderFilter orderFilter) {
    Criteria criteria = getHibernateSession()
            .createCriteria(Order.class)
            .add(Restrictions.in("orderStatus", orderFilter.getStatusesToShow()));
    return criteria.list();
}

This works and the result is as expected.

Now here is my question: Why, when I set the fetch type explicitly to EAGER, do the Orders appear multiple times in the resulting list?

@OneToMany(targetEntity = OrderTransaction.class, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
public List<OrderTransaction> getOrderTransactions() {
    return orderTransactions;
}

How would I have to change my Criteria code to reach the same result with the new setting?

Meadors

Have you tried enabling show_sql to see what's going on underneath? – Keelykeen

Please add the OrderTransaction and Order classes code as well.\ – Linn

Answer 1

This is actually the expected behaviour if I understood your configuration correctly.

You get the same Order instance in any of the results, but since now you are doing a join with the OrderTransaction, it has to return the same amount of results a regular sql join will return

So actually it should apear multiple times. this is explained very well by the author (Gavin King) himself here: It both explains why, and how to still get distinct results

---

Also mentioned in the Hibernate [FAQ][2] :

Hibernate does not return distinct results for a query with outer join fetching enabled for a collection (even if I use the distinct keyword)? First, you need to understand SQL and how OUTER JOINs work in SQL. If you do not fully understand and comprehend outer joins in SQL, do not continue reading this FAQ item but consult a SQL manual or tutorial. Otherwise you will not understand the following explanation and you will complain about this behavior on the Hibernate forum.

Typical examples that might return duplicate references of the same Order object:

List result = session.createCriteria(Order.class)
                    .setFetchMode("lineItems", FetchMode.JOIN)
                    .list();

---

<class name="Order">
    ...
    <set name="lineItems" fetch="join">

---

List result = session.createCriteria(Order.class)
                       .list();
List result = session.createQuery("select o from Order o left join fetch o.lineItems").list();

All of these examples produce the same SQL statement:

SELECT o.*, l.* from ORDER o LEFT OUTER JOIN LINE_ITEMS l ON o.ID = l.ORDER_ID

Want to know why the duplicates are there? Look at the SQL resultset, Hibernate does not hide these duplicates on the left side of the outer joined result but returns all the duplicates of the driving table. If you have 5 orders in the database, and each order has 3 line items, the resultset will be 15 rows. The Java result list of these queries will have 15 elements, all of type Order. Only 5 Order instances will be created by Hibernate, but duplicates of the SQL resultset are preserved as duplicate references to these 5 instances. If you do not understand this last sentence, you need to read up on Java and the difference between an instance on the Java heap and a reference to such an instance.

(Why a left outer join? If you'd have an additional order with no line items, the result set would be 16 rows with NULL filling up the right side, where the line item data is for other order. You want orders even if they don't have line items, right? If not, use an inner join fetch in your HQL).

Hibernate does not filter out these duplicate references by default. Some people (not you) actually want this. How can you filter them out?

Like this:

Collection result = new LinkedHashSet( session.create*(...).list() );

Linn

Even if you do understand the following explanation, you might well complain about this behaviour on the Hibernate forum, because it is flipping stupid behaviour! – Houseless

Quite right Tom, Id forgotten about Gavin Kings arrogant attitude. He also says 'Hibernate does not filter out these duplicate references by default. Some people (not you) actually want this' Id be interested when people actually ant this. – Admeasurement

@TomAnderson yes exactly. why would anyone need those duplicates? I am asking out of pure curiosity, since I have no idea... You can create duplicates yourself, as many of them as you wish .. ;-) – Saccular

Sigh. This is actually Hibernate flaw, IMHO. I want to optimize my queries, so I go from "select" to "join" in my mapping file. Suddenly my code BREAKS all over the place. Then I run around and fix all my DAOs by appending result transformers and whatnot. User experience == very negative. I understand that some people absolutely love having duplicates for bizarre reasons, but why can't I say "fetch these objects FASTER but do not bug me with duplicates" by specifying fetch="justworkplease"? – Redding

@Eran : I am facing similar kind of problem. I am not getting duplicate parent objects, but I am getting children in each parent object repeated as many times as there are number of parent objects in the response. Any idea why this problem ? – Filmore

Answer 2

In addition to what is mentioned by Eran, another way to get the behavior you want, is to set the result transformer:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Firewater

This will work for most cases....except for when you try to use Criteria to Fetch 2 collections/associations. – Tennant

Answer 3

try

@Fetch (FetchMode.SELECT) 

for example

@OneToMany(targetEntity = OrderTransaction.class, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@Fetch (FetchMode.SELECT)
public List<OrderTransaction> getOrderTransactions() {
return orderTransactions;

}

Yadirayaeger

FetchMode.SELECT increases number of SQL queries fired by Hibernate but ensures only one instance per root entity record. Hibernate will fire a select for every child record in this case. So you should account for it with respect to the performance considerations. – Harebell

@BipulKumar yes, but this is the option when we can't use lazy fetch because we need to maintain a session for lazy fetch for accessing sub objects. – Yadirayaeger

Answer 4

Do not use List and ArrayList but Set and HashSet.

@OneToMany(targetEntity = OrderTransaction.class, cascade = CascadeType.ALL)
public Set<OrderTransaction> getOrderTransactions() {
    return orderTransactions;
}

Flamsteed

Is this an incidental mention of a Hibernate best practice or relevant to the multi-child retrieve question from the OP? – Succoth

Check this java.dzone.com/articles/hibernate-facts-favoring-sets and this access.redhat.com/documentation/en-US/… – Flamsteed

Got it. Secondary to the OP's question. Though, the dzone article should probably be taken with a grain of salt... based on author's own admission in comments. – Succoth

This is a very good answer IMO. If you don't want duplicates, it's highly probable that you'd rather use a Set than a List- Using a Set (and implementing correct equals / hascode methods of course) solved the problem for me. Beware when implementing hashcode / equals, as stated in the redhat doc, not to use the id field. – Leah

Thanks for your IMO. Moreover, do not get into trouble creating equals() and hashCode() methods. Let your IDE or Lombok generate them for you. – Flamsteed

May 2 2018. I got duplicates on the parent entity. the children were fine. Only adding @Fetch(FetchMode.SELECT) to my child set helped me. YMMV – Hypallage

web.archive.org/web/20131022154531/http://java.dzone.com/… for the DZone article – Epidote

Answer 5

I have the same problem to fetch 2 associated collections: user has 2 roles (Set) and 2 meals (List) and meals are duplicated.

@Table(name = "users")
public class User extends AbstractNamedEntity {

   @CollectionTable(name = "user_roles", joinColumns = @JoinColumn(name = "user_id"))
   @Column(name = "role")
   @ElementCollection(fetch = FetchType.EAGER)
   @BatchSize(size = 200)
   private Set<Role> roles;

   @OneToMany(fetch = FetchType.LAZY, mappedBy = "user")
   @OrderBy("dateTime DESC")
   protected List<Meal> meals;
   ...
}

DISTINCT doesn't help (DATA-JPA query):

@EntityGraph(attributePaths={"meals", "roles"})
@QueryHints({@QueryHint(name= org.hibernate.jpa.QueryHints.HINT_PASS_DISTINCT_THROUGH, value = "false")}) // remove unnecessary distinct from select
@Query("SELECT DISTINCT u FROM User u WHERE u.id=?1")
User getWithMeals(int id);

At last I've found 2 solutions:

1. Change List to LinkedHashSet
2. Use EntityGraph with only field "meal" and type LOAD, which load roles as they declared (EAGER and by BatchSize=200 to prevent N+1 problem):

Final solution:

@EntityGraph(attributePaths = {"meals"}, type = EntityGraph.EntityGraphType.LOAD)
@Query("SELECT u FROM User u WHERE u.id=?1")
User getWithMeals(int id);

UPDATE: for Spring Boot 3.0 (JPA 3.1) it doen't works!!

@EntityGraph(attributePaths = {"meals"}, type = EntityGraph.EntityGraphType.FETCH/LOAD)
@Query("SELECT u FROM User u WHERE u.id=?1")
Optional<User> getWithMeals(int id);

This work in transactional tests, but LazyInitializationException for roles in application.

Found another 2 solution:

1.

@EntityGraph(attributePaths = {"meals"}, type = 
EntityGraph.EntityGraphType.LOAD)
    @Query("SELECT u FROM User u WHERE u.id=?1")
    Optional<User> getWithMeals(int id);

and @Fetch(FetchMode.SUBSELECT) for user.roles But in this case SQL rather odd:

select
        r1_0.user_id,
        r1_0.role 
    from
        user_role r1_0 
    where
        r1_0.user_id in(select
            u1_0.id 
        from
            users u1_0 
        left join
            meal m1_0 
                on u1_0.id=m1_0.user_id 
        where
            u1_0.id=?)

2. Very simple SELECT:

@Query("SELECT u FROM User u LEFT JOIN FETCH u.meals WHERE u.id=?1")
Optional<User> getWithMeals(int id);

See ORM 6.0 migration:

• forget about DISTINCT
• it is no longer necessary to use distinct in JPQL and HQL

Olin

Answer 6

Using Java 8 and Streams I add in my utility method this return statment:

return results.stream().distinct().collect(Collectors.toList());

Streams remove duplicate very fast. I use annotation in my Entity class like this:

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name = "STUDENT_COURSES")
private List<Course> courses;

I think is bether in my app to use session in method where I need data from data base. Closse session when I done. Ofcourse set my Entity class to use leasy fetch type. I go to refactor.

Claytonclaytonia

Answer 7

Instead of using hacks like :

• Set instead of List
• criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

which don't modify your sql query , we can use (quoting JPA specs)

q.select(emp).distinct(true);

which does modify the resulting sql query,thus having a DISTINCT in it.

Littell

Answer 8

It doesn't sounds a great behavior applying an outer join and bring duplicated results. The only solution left is to filter our result using streams. Thanks java8 giving easier way to filter.

return results.stream().distinct().collect(Collectors.toList());

Doubleness