CODEWITHSUNDEEP
postgresqlhibernatespring-bootormjhipster
nassim
nassim

Reputation: 147

Hibernate make too much queries

I have a performances problem into my application.

I have three entities with the relations below:

one Order => many OrderLines => Many Cards

The load of entities from database takes too much time, the orderLines are loaded with eager loading while Cards are loaded with lazy loading. Lazy loading is necessary to let the application fluent.

I need to configure eager loading for one case and I need help to do it.

I'm using jhsipster stack: spring-boot with JPA

I tried to write Hql query

@Query(value = "from Order as order "
        + "inner join order.orderLines as orderLines "
        + "inner join orderlines.cards gc "
        + "where order.id= :order")
Order findWithDependencies(@Param("order")Long order);

I have un error: Unexpected token order (line 2)

I tried it with native query

@Query(value = "select * "+ 
        "from order_table ot "+
        "left join order_line ol ON ot.id = ol.order_id "+
        "left join  giftcard gc ON gc.orderline_id = ol.id "+
        "where ot.id=:order", nativeQuery= true)
Order findWithDependenciesNative(@Param("order")Long order);

But when i do 

orderLine.getCard()

From code, Hibernate still do queries to get data from database and that causes my problem of performances.

Necessary code of entities:

public class Order implements Serializable {
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "order_id", nullable = false)
    @Size(min = 1)
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    @JsonProperty("detail")
    @Valid
    private List<OrderLine> orderLines = new ArrayList<>();
}
public class OrderLine extends AbstractAuditingEntity implements Serializable, Comparable {
    @OneToMany
    @JoinColumn(name = "orderline_id")
    @JsonIgnore
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    private Set<Giftcard> giftcards = new HashSet<>();
}

public class Giftcard extends AbstractAuditingEntity implements Serializable, Comparable {
    @ManyToOne(optional = true)
    @JoinColumn(name= "orderline_id")
    @JsonIgnore
    private OrderLine orderLine;
}

My code actually

{
    Order order = orderRepository.findWithDependenciesNative(linkFile.getOrder());
    //Some code
    List<OrderLine> orderLines = order.getOrderLines();
    orderLines.sort(Comparator.comparing(OrderLine::getId));
    for (OrderLine orderLine : orderLines) {
        Stream<Card> cards = orderLine.getCards().stream().sorted(Comparator.comparing(Card::getCardNumber));
        for (Card card : cards.collect(Collectors.toList())) {
        //Some code
        }
    }

The problem is in the second foreach, for every card hibernate execute two queries and the request with join doesn't load datas like eager configuration. Have you an idea to help me?

Many thanks

Upvotes: 2

Views: 933

Answers (1)

Sudhakar
Sudhakar

Reputation: 3180

Use "join fetch" to avoid lazy loading. A "fetch" join allows associations or collections of values to be initialized along with their parent objects using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections. See the more information on join fetch:Difference between JOIN and JOIN FETCH in Hibernate

@Query("Select order from Order as order "
        + "inner join fetch order.orderLines as orderLines "
        + "inner join fetch orderlines.cards gc "
        + "where order.id= :order")
Order findWithDependencies(@Param("order")Long order);

Upvotes: 3

Related Questions