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