Hibernate Criteria returns children multiple times with FetchType.EAGER

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?

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 :

> 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(); > > > ---------- > > > > ... > > > > ---------- > > > 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() );

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);

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;

}

Do not use List and ArrayList but Set and HashSet.

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

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.

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);

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.

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());