JPA SQL and Fetching tuning ( EclipseLink )

When you use JPA in your project and your model project contains many entities with some Eager fetching relation attributes then you probably notice that EclipseLink can fire a lot of SQL queries on the database. This works ok & fast on Dev or Test but in production these queries can lead to big problems ( higher load and more data).
In our project we also use ADF DataControls, Web Services or Entity Transient attributes which does not support Lazy loading. So we need to tune this entities fetching.

In this blogpost I will use the department and employee entity of the HR Oracle demo schema to explain the JPA options you have to control the SQL statements and the JPA relation Fetching.

These two entities has the following relations

• Department has a Manager
• Department has Employees
• Employee belongs to a Department
• Employee has a Manager
• Manager has Employees

First step is to set or leave all OneToMany relations to Lazy Fetching ( this is the default ). If you put everything on Eager then you will create an loop and EclipseLink will detect this and will throw an error.
If we only set the Department entity relations to Eager then the department named queries will be Ok but we can't retrieve everything from the Employee entity.   

But when you want to retrieve a Department with its Employees and Manager or a Manager with its Employees and Department plus you want to tune the fired SQL queries then you got the following options.

Here you see all the Department Named Queries.

Departments.findByName
select o from Departments o where o.departmentName = :name" 

this query will get all the Departments with a particular name and it retrieves all the ManyToOne relations like the department Manager. But this does not retrieve the Employees of the Department.

Departments.findByNameFetch
select o from Departments o left join fetch o.employeesList where o.departmentName = :name
If we want to fetch all the Employees of this department we can use join fetch. Fetch with join is necessary if you want to do Eager fetching. Also left join fetch ( outer join )  is necessary if you also want to query Departments without Employees. Plus this will do it in one SQL query.

Departments.findByNameFetch2
select o from Departments o where o.departmentName = :name
hints= { @QueryHint( name =QueryHints.LEFT_FETCH, value="Departments.employeesList") }
We can do the same with EclipseLink and use @QueryHint( name =QueryHints.LEFT_FETCH
This does the same as Departments.findByNameFetch , you can also use QueryHints.FETCH but this won't do an outer join.

You can also do the same on the Query, like this
em.createNamedQuery("Departments.findByName")
.setParameter("name", "Finance")
.setHint(QueryHints.LEFT_FETCH, "Departments.employeesList")
.getResultList();

Departments.findByNameFetch3
select o from Departments o where o.departmentName = :name
hints= { @QueryHint( name =QueryHints.LEFT_FETCH, value="Departments.employeesList.manager") }
EclipseLink can even do more than the standard JPA JQL query, in this example it can also retrieve the manager of the employees in the same SQL query instead of using separate queries ( this is not possible with normal JPA).

To see the all the executed SQL statements put the EclipseLink Logging to the Fine level.
This is the test client I used to test these queries
Here you can download or see all the code of my test project on github.