3

I'm migrating our DAO from using Hibernate Criteria API to JPA Criteria API. I've got a class with several @ManyToOne there:

@Entity
@Table(name = "A_TABLE", schema="SCHEMA_NAME")
public class A {
    @ManyToOne
    @JoinFormula("(SELECT * FROM (SELECT B.B_ID FROM SCHEMA_NAME.B_TABLE B WHERE B.A_ID = B_ID AND (B.B_CODE = '1' OR B.B_CODE = '2') ORDER BY B.B_CREATED_TIMESTAMP DESC) WHERE ROWNUM = 1)")
    private B b1;
    @ManyToOne
    @JoinFormula("(SELECT * FROM (SELECT B.B_ID FROM SCHEMA_NAME.B_TABLE B WHERE B.A_ID = B_ID AND (B.B_CODE = '3' OR B.B_CODE = '4') ORDER BY B.B_CREATED_TIMESTAMP DESC) WHERE ROWNUM = 1)")
    private B b2;
    ...
}

@Entity
@Table(name = "B_TABLE", schema="SCHEMA_NAME")
public class B {

}

In a query I'm using JoinType.LEFT in order to get rid of generated by default CROSS JOIN's:

if (LEFT_OUTER_JOIN_ENTITIES.contains(field)) {
    path = ((From) path).join(field, JoinType.LEFT);
} else {
    path = path.get(field);
}

I'm getting right results, all the A and B records are retrieved correctly. However, after the migration I'm getting n+1 problem: all the B records are retrieved one-by-one despite using LEFT OUTER JOIN's in generated queries. Previously (when using Hibernate Criteria API) Hibernate was able to satisfy the query without n+1 having the same joins in generated SQL.

Thank you for any ideas and help!

UPDATE As an example of using the if-else above for search by "b1.fieldName" I would get the following:

criteriaBuilder.equal(root.join("b1", JoinType.LEFT).get("someFiled"), 42)
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
Dmitry Senkovich
  • 5,521
  • 8
  • 37
  • 74
  • You should also provide JPA code. What type of fetch are you using? – NiVeR Oct 17 '18 at 08:33
  • @NiVeR actually, I'm using `.join`. It is in the `if-else` branch in the question) I'm just using `.join` if I encounter e.g. search by "b1.someField". It would be the same as `criteriaBuilder.equal(root.join("b1", JointType.LEFT).get("someField"), 42)` – Dmitry Senkovich Oct 17 '18 at 08:35
  • You can use fetch join (better) or @BatchSize(size = x) – Samoth Oct 17 '18 at 08:37
  • @Samoth interesting idea. but it is `@ManyToOne`. should I use `@BatchSize(size = 1)`? regarding fetch join: it always will be eager, right? and do you mean `.fetch` method or some annotation? – Dmitry Senkovich Oct 17 '18 at 08:38
  • Yes, it will be eager. You can use it in HQL, eg. select u from User u JOIN FETCH u.addresses. BatchSize for ManyToOne: https://stackoverflow.com/questions/13048436/manytoone-and-batchsize – Samoth Oct 17 '18 at 08:46
  • @Samoth seems like in that case I would get batching for this `b1`, `b2` and etc fields. But previously Hibernate was able to load this fields with the query for `A` without any additional queries – Dmitry Senkovich Oct 17 '18 at 08:52

1 Answers1

4

The N+1 issue comes from the default FetchType.EAGER fetching strategy of the @ManyToOne associations.

So, you need to switch to FetchType.LAZY, like this:

@ManyToOne(fetch = FetchType.LAZY)
@JoinFormula("(SELECT * FROM (SELECT B.B_ID FROM SCHEMA_NAME.B_TABLE B WHERE B.A_ID = B_ID AND (B.B_CODE = '1' OR B.B_CODE = '2') ORDER BY B.B_CREATED_TIMESTAMP DESC) WHERE ROWNUM = 1)")
private B b1;

@ManyToOne(fetch = FetchType.LAZY)
@JoinFormula("(SELECT * FROM (SELECT B.B_ID FROM SCHEMA_NAME.B_TABLE B WHERE B.A_ID = B_ID AND (B.B_CODE = '3' OR B.B_CODE = '4') ORDER BY B.B_CREATED_TIMESTAMP DESC) WHERE ROWNUM = 1)")
private B b2;

If you want to automatically detect N+1 issues automatically that might affect other parts of your application, then you can use datasource-proxy.

If you need to fetch the association eagerly with Criteria API, then you should use fetch instead of join.

if (LEFT_OUTER_JOIN_ENTITIES.contains(field)) {
    path = ((From) path).fetch(field, JoinType.LEFT);
} else {
    path = path.get(field);
}
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911