Спящий режим n+1 проблема

У меня есть эти классы:

Заказы:

@Table(name = "ORDERS")
public class Order {

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

    @Column(name = "user_id", nullable = false)
    private Long userId;

    @JsonManagedReference
    @OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
    private Set<OrderDetail> orderDetails;

    @JsonManagedReference(value = "order-note")
    @OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
    private Set<Note> notes;

}

Подробнее

@Table(name = "ORDER_DETAILS")
public class OrderDetail {
    @Id
    @Column(name = "order_detail_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @JsonBackReference
    @ManyToOne
    @JoinColumn(name = "order_id")
    private Order order;

    @JsonManagedReference
    @OneToMany(mappedBy = "orderDetail", fetch = FetchType.EAGER)
    private Set<OrderSize> orderSizes;

    @JsonManagedReference(value="order-detail-note")
    @OneToMany(mappedBy = "orderDetail", fetch = FetchType.EAGER)
    private Set<Note> notes;
}

Размеры:

@Table(name = "ORDER_SIZES")
public class OrderSize {

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

    @JsonBackReference
    @ManyToOne
    @JoinColumn(name = "order_detail_id")
    private OrderDetail orderDetail;
}

Примечания

@Table(name = "NOTES")
public class Note {

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

    @JsonBackReference(value="order-note")
    @ManyToOne
    @JoinColumn(name = "order_id", nullable = true)
    private Order order;

    @JsonBackReference(value="order-detail-note")
    @ManyToOne
    @JoinColumn(name = "order_detail_id", nullable = true)
    private OrderDetail orderDetail;
}

Я хочу выполнить запрос (используя критерии Hibernate) и получить все заказы с соответствующими данными, размерами и примечаниями.

public List<Order> findByUser(Long userId) {
    Criteria criteria = createEntityCriteria();
    criteria.add(Restrictions.eq("userId", userId));
    return (List<Order>) criteria.list();
}

На данный момент Hibernate генерирует n таких запросов:

Hibernate:
    select
        notes0_.order_detail_id as order_de4_17_0_,
        notes0_.note_id as note_id1_15_0_,
        notes0_.note_id as note_id1_15_1_,
        notes0_.note_text as note_tex2_15_1_,
        notes0_.order_id as order_id3_15_1_,
        notes0_.order_detail_id as order_de4_15_1_,
        order1_.order_id as order_id1_16_2_,
        order1_.client_id as client_i2_16_2_,
        order1_.company_id as company_3_16_2_,
        order1_.delivery_id as delivery4_16_2_,
        order1_.discount as discount5_16_2_,
        order1_.order_date as order_da6_16_2_,
        order1_.order_name as order_na7_16_2_,
        order1_.signature as signatur8_16_2_,
        order1_.order_status as order_st9_16_2_,
        order1_.payment_method_id as payment10_16_2_,
        order1_.user_id as user_id11_16_2_
    from
        NOTES notes0_
    left outer join
        ORDERS order1_
            on notes0_.order_id=order1_.order_id
    where
        notes0_.order_detail_id=?

Я не смог придумать решение, чтобы получить все, что мне нужно, всего за один запрос.


java hibernate hibernate-criteria
person guidev    schedule 30.03.2017    source источник
comment
Рассматривали ли вы возможность использования @Fetch docs.jboss.org/ спящий режим/orm/5.2/javadocs/org/hibernate/   -  person Cristian Toader    schedule 30.03.2017
comment
@Chris Что мне передать в качестве первого параметра? критерии.setFetchMode(foo, FetchMode.JOIN)   -  person guidev    schedule 30.03.2017
comment
Я имел в виду ваши отношения сущностей, которые вызывают запросы n + 1, также аннотируйте их с помощью @Fetch(FetchMode.JOIN). Я вернусь к вам с более полным ответом, но пока вы можете попробовать это.   -  person Cristian Toader    schedule 30.03.2017
comment
@ Крис, спасибо, похоже, это не решает проблему. Я получаю те же n запросов.   -  person guidev    schedule 30.03.2017

Ответы (1)


arrow_upward
1
arrow_downward

После вставки:

  • 2 объекта нот
  • 2 объекта OrderDetail (каждый из которых содержит 2 объекта Note)
  • 1 сущность Order (содержащая 2 сущности OrderDetail и 2 сущности Note)

Использование следующих аннотаций в объекте Order:

@OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
@Fetch(FetchMode.JOIN)
private Set<OrderDetail> orderDetails = new HashSet<>();

@OneToMany(mappedBy = "order", fetch = FetchType.EAGER)
@Fetch(FetchMode.JOIN)
private Set<Note> notes;

Генерирует следующий запрос:

Hibernate: 
    select
        this_.order_id as order_id1
public List<Order> findByUserQueryDsl(Long userId) {
    System.out.println("Finding by User ID query dsl");

    EntityManager em = this.entityManagerFactory.createEntityManager();

    return new JPAQuery<>(em, HQLTemplates.DEFAULT)
            .select(QOrder.order)
            .from(QOrder.order)
            .leftJoin(QOrder.order.notes).fetchJoin()
            .leftJoin(QOrder.order.orderDetails, QOrderDetail.orderDetail).fetchJoin()
            .leftJoin(QOrderDetail.orderDetail.notes).fetchJoin()
            .where(QOrder.order.userId.eq(userId))
            .fetch();
}

4_,
        this_.user_id as user_id2
public List<Order> findByUserQueryDsl(Long userId) {
    System.out.println("Finding by User ID query dsl");

    EntityManager em = this.entityManagerFactory.createEntityManager();

    return new JPAQuery<>(em, HQLTemplates.DEFAULT)
            .select(QOrder.order)
            .from(QOrder.order)
            .leftJoin(QOrder.order.notes).fetchJoin()
            .leftJoin(QOrder.order.orderDetails, QOrderDetail.orderDetail).fetchJoin()
            .leftJoin(QOrderDetail.orderDetail.notes).fetchJoin()
            .where(QOrder.order.userId.eq(userId))
            .fetch();
}

4_,
        notes2_.order_id as order_id2_0_6_,
        notes2_.note_id as note_id1_0_6_,
        notes2_.note_id as note_id1_0_0_,
        notes2_.order_id as order_id2_0_0_,
        notes2_.order_detail_id as order_de3_0_0_,
        orderdetai3_.order_detail_id as order_de1_1_1_,
        orderdetai3_.order_id as order_id2_1_1_,
        order4_.order_id as order_id1
public List<Order> findByUserQueryDsl(Long userId) {
    System.out.println("Finding by User ID query dsl");

    EntityManager em = this.entityManagerFactory.createEntityManager();

    return new JPAQuery<>(em, HQLTemplates.DEFAULT)
            .select(QOrder.order)
            .from(QOrder.order)
            .leftJoin(QOrder.order.notes).fetchJoin()
            .leftJoin(QOrder.order.orderDetails, QOrderDetail.orderDetail).fetchJoin()
            .leftJoin(QOrderDetail.orderDetail.notes).fetchJoin()
            .where(QOrder.order.userId.eq(userId))
            .fetch();
}

2_,
        order4_.user_id as user_id2
public List<Order> findByUserQueryDsl(Long userId) {
    System.out.println("Finding by User ID query dsl");

    EntityManager em = this.entityManagerFactory.createEntityManager();

    return new JPAQuery<>(em, HQLTemplates.DEFAULT)
            .select(QOrder.order)
            .from(QOrder.order)
            .leftJoin(QOrder.order.notes).fetchJoin()
            .leftJoin(QOrder.order.orderDetails, QOrderDetail.orderDetail).fetchJoin()
            .leftJoin(QOrderDetail.orderDetail.notes).fetchJoin()
            .where(QOrder.order.userId.eq(userId))
            .fetch();
}

2_,
        ordersizes5_.order_detail_id as order_de2_2_7_,
        ordersizes5_.order_size_id as order_si1_2_7_,
        ordersizes5_.order_size_id as order_si1_2
public List<Order> findByUserQueryDsl(Long userId) {
    System.out.println("Finding by User ID query dsl");

    EntityManager em = this.entityManagerFactory.createEntityManager();

    return new JPAQuery<>(em, HQLTemplates.DEFAULT)
            .select(QOrder.order)
            .from(QOrder.order)
            .leftJoin(QOrder.order.notes).fetchJoin()
            .leftJoin(QOrder.order.orderDetails, QOrderDetail.orderDetail).fetchJoin()
            .leftJoin(QOrderDetail.orderDetail.notes).fetchJoin()
            .where(QOrder.order.userId.eq(userId))
            .fetch();
}

,
        ordersizes5_.order_detail_id as order_de2_2
public List<Order> findByUserQueryDsl(Long userId) {
    System.out.println("Finding by User ID query dsl");

    EntityManager em = this.entityManagerFactory.createEntityManager();

    return new JPAQuery<>(em, HQLTemplates.DEFAULT)
            .select(QOrder.order)
            .from(QOrder.order)
            .leftJoin(QOrder.order.notes).fetchJoin()
            .leftJoin(QOrder.order.orderDetails, QOrderDetail.orderDetail).fetchJoin()
            .leftJoin(QOrderDetail.orderDetail.notes).fetchJoin()
            .where(QOrder.order.userId.eq(userId))
            .fetch();
}

 
    from
        orders this_ 
    left outer join
        notes notes2_ 
            on this_.order_id=notes2_.order_id 
    left outer join
        order_details orderdetai3_ 
            on notes2_.order_detail_id=orderdetai3_.order_detail_id 
    left outer join
        orders order4_ 
            on orderdetai3_.order_id=order4_.order_id 
    left outer join
        order_sizes ordersizes5_ 
            on orderdetai3_.order_detail_id=ordersizes5_.order_detail_id 
    where
        this_.user_id=?
Hibernate: 
    select
        orderdetai0_.order_id as order_id2_1_0_,
        orderdetai0_.order_detail_id as order_de1_1_0_,
        orderdetai0_.order_detail_id as order_de1_1_1_,
        orderdetai0_.order_id as order_id2_1_1_ 
    from
        order_details orderdetai0_ 
    where
        orderdetai0_.order_id=?

Однако, если вы все еще сталкиваетесь с трудностями при получении данных в виде одного запроса, я бы предложил использовать QueryDSL [1] с примерами и подробностями настройки maven здесь [2].

LE:

Пример того, как ваш запрос будет создан в QueryDSL:

public List<Order> findByUserQueryDsl(Long userId) {
    System.out.println("Finding by User ID query dsl");

    EntityManager em = this.entityManagerFactory.createEntityManager();

    return new JPAQuery<>(em, HQLTemplates.DEFAULT)
            .select(QOrder.order)
            .from(QOrder.order)
            .leftJoin(QOrder.order.notes).fetchJoin()
            .leftJoin(QOrder.order.orderDetails, QOrderDetail.orderDetail).fetchJoin()
            .leftJoin(QOrderDetail.orderDetail.notes).fetchJoin()
            .where(QOrder.order.userId.eq(userId))
            .fetch();
}

Что сгенерирует следующий запрос:

select
    order0_.order_id as order_id1_3_0_,
    notes1_.note_id as note_id1_0_1_,
    orderdetai2_.order_detail_id as order_de1_1_2_,
    notes3_.note_id as note_id1_0_3_,
    order0_.user_id as user_id2_3_0_,
    notes1_.order_id as order_id2_0_1_,
    notes1_.order_detail_id as order_de3_0_1_,
    notes1_.order_id as order_id2_0_0__,
    notes1_.note_id as note_id1_0_0__,
    orderdetai2_.order_id as order_id2_1_2_,
    orderdetai2_.order_id as order_id2_1_1__,
    orderdetai2_.order_detail_id as order_de1_1_1__,
    notes3_.order_id as order_id2_0_3_,
    notes3_.order_detail_id as order_de3_0_3_,
    notes3_.order_detail_id as order_de3_0_2__,
    notes3_.note_id as note_id1_0_2__ 
from
    orders order0_ 
left outer join
    notes notes1_ 
        on order0_.order_id=notes1_.order_id 
left outer join
    order_details orderdetai2_ 
        on order0_.order_id=orderdetai2_.order_id 
left outer join
    notes notes3_ 
        on orderdetai2_.order_detail_id=notes3_.order_detail_id 
where
    order0_.user_id=?

[1] http://www.querydsl.com/

[2] http://www.querydsl.com/static/querydsl/4.1.4/reference/html/ch02s02.html

person Cristian Toader    schedule 30.03.2017