NonUniqueResultException when attempting to fetch bidrectional OneToMany child collection in a DTO with join fetch

Hibernate ORM
1

Hello! I’m using Spring Data JPA and I’m in need of some help. I got the following entities:

Order Entity:

 public class Order {

	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

        @OneToOne(mappedBy = "order", cascade = CascadeType.ALL, optional = false, fetch FetchType.LAZY)
	@JsonManagedReference
	private Cart cart;

Cart Entity:

public class Cart {

	@Id
	private Long id;

	@OneToOne(fetch = FetchType.LAZY)
	@MapsId
	private Order order;

	@OneToMany(mappedBy = "cart",
			cascade = CascadeType.ALL,
			orphanRemoval = true)
	private List<OrderItem> orderItems;

OrderItem Entity:


    public class OrderItem {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id")
	private Long id;

	@ManyToOne(fetch = FetchType.LAZY)
	private Cart cart;

The following methods

@Query("from Order order " +
			"join fetch order.cart as cart " +
			"join fetch cart.orderItems " +
			"where order.id = :orderId")
	OrderDTO findDTOById(Long orderId);

And

		OrderDTO order = entityManager.createQuery("""
								select new OrderDTO(
								   order.id,
								   order.createdOn,
								   order.updatedOn,
								   order.formattedCreatedOn,
								   order.formattedUpdatedOn,
								   order.address,
								   order.orderDetails,
								   order.cart)
								from Order order join fetch order.cart.orderItems where order.id = :orderId""",
						OrderDTO.class)
				.setParameter("orderId", orderId)
				.getSingleResult();

Result in org.hibernate.NonUniqueResultException: Query did not return a unique result: 2 results were returned

The DB looks like this:

Order Table:

    +----+
    | id |
    +----+
    |  1 |
    +----+

    Cart Table: 

    +----------+
    | order_id |
    +----------+
    |        1 |
    +----------+

    OrderItem table:

    +----+---------+
    | id | cart_id |
    +----+---------+
    |  1 |       1 |
    |  2 |       1 |
    +----+---------+

However, fetching only the Cart works fine:

	@Override
	public Cart findCartByOrderId(Long orderId) {
		return entityManager.createQuery("select order.cart from Order order join fetch order.cart.orderItems where order.id = :orderId",
						Cart.class).
				setParameter("orderId", orderId)
				.getSingleResult();
	}

Fetching the entity itself Order without a DTO also works without an issue:

	@Query("from Order order " +
			"join fetch order.cart as cart " +
			"join fetch cart.orderItems " +
			"where order.id = :orderId")
	Order findByIdNoLazy(Long orderId);

It seems the issue appears when using either a Interface-based Projection or a Class-based Projection as a DTO with these particular mappings.

At the moment I can only achieve fetching an OrderDTO (the objective is to omit fetching the user) by fetching the OrderDTO without selecting order.cart and after it has been fetched I do

order.setCart(findCartByOrderId(orderId)).

I don’t know what causing this or why HB is looking for one/unique result to begin with, instead of fetching the orderItems collection. Is there anything I can do to avoid fetching the cart separately when fetching an order dto?

Thank you very much!

2

Since Hibernate 6 duplicate entity results are automatically removed in memory. This is not the case, however, for dynamic instantiation queries like the one you’re using in your OrderDTO example, and since you’re using getSingleResult() you get the non-unique result exception.

I would suggest using the entity query instead and instantiating the DTO objects programmatically to take advantage of automatic removal of duplicated results in your case.

3

Thank you very much for the answer! I was not aware of duplicate results arising from the use of join fetch. I did use a List with getResultList and indeed I got two Order entities with the same id despite only one existing in the database. I’ll use your suggestion as a solution, thanks!