I am using Hibernate JPA 1.0.
I have the following type of model and I consider manyToOne and oneToOne relationships "eagerly" fetched and oneToMany "lazily" fetched.
I want to fetch Entity A and all its associations where a.id=?
- A oneToMany B
- B oneToOne C
- C oneToMany D
- B oneToOne E
- E oneToMany D
- B oneToOne F
- F oneToMany D
- B oneToOne C
Is it possible to load this entity in a single query? Or in a subset of queries baring in mind the "n+1 selects problem"!
So far my solution to loading all of A associations was to perform the following:
"Select DISTINCT a from A a JOIN FETCH a.bs WHERE a.id=:aID"
And then iterate using code in order to fetch all other associations.
Collection B bs = A.getBs();
for (final B b : bs) { b.getCs().getDs().size(); b.getEs().getDs().size(); b.getFs().getDs().size(); }
Obviously there must be a better way of doing this.
3 Answers
Answers 1
You might be interested in reading this article. I have tested both
@Fetch(FetchMode.SUBSELECT)
and also using a Set instead of List, in combination with fetch = FetchType.EAGER it works.
Answers 2
Use a FETCH JOIN. From the JPA 1.0 specification:
4.4.5.3 Fetch Joins
A FETCH JOIN enables the fetching of an association as a side effect of the execution of a query. A FETCH JOIN is specified over an entity and its related entities.
The syntax for a fetch join is
fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH join_association_path_expression
The association referenced by the right side of the FETCH JOIN clause must be an association that belongs to an entity that is returned as a result of the query. It is not permitted to specify an identification variable for the entities referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities cannot appear elsewhere in the query.
The following query returns a set of departments. As a side effect, the associated employees for those departments are also retrieved, even though they are not part of the explicit query result. The persistent fields or properties of the employees that are eagerly fetched are fully initialized. The initialization of the relationship properties of the employees that are retrieved is determined by the metadata for the Employee entity class.
SELECT d FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptno = 1
A fetch join has the same join semantics as the corresponding inner or outer join, except that the related objects specified on the right-hand side of the join operation are not returned in the query result or otherwise referenced in the query. Hence, for example, if department 1 has five employees, the above query returns five references to the department 1 entity.
Of course, use it wisely, don't join too many tables or you will kill performances.
Answers 3
Looking for an answer drawing from credible and/or official sources.
How about JBoss ORM documentation?
https://docs.jboss.org/hibernate/orm/current/userguide/html_single/chapters/fetching/Fetching.html
There are a number of scopes for defining fetching:
static
Static definition of fetching strategies is done in the mappings. The statically-defined fetch strategies is used in the absence of any dynamically defined strategiesSELECT Performs a separate SQL select to load the data. This can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed). This is the strategy generally termed N+1.
JOIN Inherently an EAGER style of fetching. The data to be fetched is obtained through the use of an SQL outer join.
BATCH Performs a separate SQL select to load a number of related data items using an IN-restriction as part of the SQL WHERE-clause based on a batch size. Again, this can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed).
SUBSELECT Performs a separate SQL select to load associated data based on the SQL restriction used to load the owner. Again, this can either be EAGER (the second select is issued immediately) or LAZY (the second select is delayed until the data is needed).
dynamic (sometimes referred to as runtime)
Dynamic definition is really use-case centric. There are multiple ways to define dynamic fetching:Fetch profiles defined in mappings, but can be enabled/disabled on the Session.
HQL/JPQL and both Hibernate and JPA Criteria queries have the ability to specify fetching, specific to said query.
Entity Graphs Starting in Hibernate 4.2 (JPA 2.1) this is also an option.
And to prove the answer above, here's an example:
FetchMode.SUBSELECT To demonstrate how FetchMode.SUBSELECT works, we are going to modify the FetchMode.SELECT mapping example to use FetchMode.SUBSELECT:
Example 17. FetchMode.SUBSELECT mapping example:
@OneToMany(mappedBy = "department", fetch = FetchType.LAZY) @Fetch(FetchMode.SUBSELECT) private List<Employee> employees = new ArrayList<>();
Now, we are going to fetch all Department entities that match a given
filtering criteria and then navigate their employees collections.Hibernate is going to avoid the N+1 query issue by generating a single SQL statement to initialize all employees collections for all Department entities that were previously fetched. Instead of using passing all entity identifiers, Hibernate simply reruns the previous query that fetched the Department entities.
Example 18. FetchMode.SUBSELECT mapping example:
List<Department> departments = entityManager.createQuery( "select d " + "from Department d " + "where d.name like :token", Department.class) .setParameter( "token", "Department%" ) .getResultList(); log.infof( "Fetched %d Departments", departments.size()); for (Department department : departments ) { assertEquals(3, department.getEmployees().size()); }
-- Fetched 2 Departments
SELECT d.id as id1_0_ FROM Department d where d.name like 'Department%' -- Fetched 2 Departments SELECT e.department_id as departme3_1_1_, e.id as id1_1_1_, e.id as id1_1_0_, e.department_id as departme3_1_0_, e.username as username2_1_0_ FROM Employee e WHERE e.department_id in ( SELECT fetchmodes0_.id FROM Department fetchmodes0_ WHERE d.name like 'Department%' )
0 comments:
Post a Comment