How to Properly Express Jpql "Join Fetch" with "Where" Clause as JPA 2 Criteriaquery

How to properly express JPQL join fetch with where clause as JPA 2 CriteriaQuery?

In JPQL the same is actually true in the spec. The JPA spec does not allow an alias to be given to a fetch join. The issue is that you can easily shoot yourself in the foot with this by restricting the context of the join fetch. It is safer to join twice.

This is normally more an issue with ToMany than ToOnes.
For example,

Select e from Employee e 
join fetch e.phones p 
where p.areaCode = '613'

This will incorrectly return all Employees that contain numbers in the '613' area code but will left out phone numbers of other areas in the returned list. This means that an employee that had a phone in the 613 and 416 area codes will loose the 416 phone number, so the object will be corrupted.

Granted, if you know what you are doing, the extra join is not desirable, some JPA providers may allow aliasing the join fetch, and may allow casting the Criteria Fetch to a Join.

How to perform a join fetch in JPA Criteria without unchecked casting?

Fetch method is not supposed for creating JPA joins. In JPA join is used to create where conditions, not for loading data. This is different from native SQL. For JOIN use:

Join<Thing, Other> other = root.join(Thing_.other);

Independently collection can be loaded with or without calling join():

root.fetch(Thing_.other);

What is the difference between JOIN and JOIN FETCH when using JPA and Hibernate

In this two queries, you are using JOIN to query all employees that have at least one department associated.

But, the difference is: in the first query you are returning only the Employes for the Hibernate. In the second query, you are returning the Employes and all Departments associated.

So, if you use the second query, you will not need to do a new query to hit the database again to see the Departments of each Employee.

You can use the second query when you are sure that you will need the Department of each Employee. If you not need the Department, use the first query.

I recomend read this link if you need to apply some WHERE condition (what you probably will need): How to properly express JPQL "join fetch" with "where" clause as JPA 2 CriteriaQuery?

Update

If you don't use fetch and the Departments continue to be returned, is because your mapping between Employee and Department (a @OneToMany) are setted with FetchType.EAGER. In this case, any HQL (with fetch or not) query with FROM Employee will bring all Departments. Remember that all mapping *ToOne (@ManyToOne and @OneToOne) are EAGER by default.

JPA - Why cast Fetch to Join

I had to cast Fetch to Join when I wanted to get benefits of both.

• Fetch allows eager loading when annotations are not working see https://stackoverflow.com/a/25631785/2202712
• Join allows adding restriction/order on an attribute from joined relation

e.g., Say you want to get all Employee together with info about their departments and home countries as a single select query with two inner joins. This is possible by adding a root.fetch(...) each for department and homeCountry. If you also wish to order employees based on the population of their respective home countries (please assume you wish to), you will need a Join

Root<Employee> root = query.from(Employee.class);
root.fetch("department");  // <- for an eager join
Join<Employee,Country> joinCountry = (Join) root.fetch("homeCountry");  // <- for an eager join & orderBy
query.select(root).orderBy(builder.asc(joinCountry.get("population")));
Query<Employee> q = sessionFactory.getCurrentSession().createQuery(query);
List<Employee> employees = q.getResultList();

Above code fires a single select * to the db

select
    employee0_.emp_id as emp_id1_0_0_,
    department1_.department_id as depart1_2_1_,
    country2_.country_id as countr1_3_2_,
    employee0_.salary as salary2_0_0_,
    department1_.name as name3_0_0,
    country2_.continent as continent4_0_0
from
    employee employee0_
inner join
    department department1_
        on employee0_.department_id=department1_.department_id 
inner join
    country country2_
        on employee0_.country_id=country2_.country_id 
order by
    country2_.population asc

How do I write a MAX query with a where clause in JPA 2.0?

There are two ways, one using JPQL, the other using criteria queries.

JPQL is simply:

em.createQuery("select max(e.dateProcessed) from Event e where e.org = :myOrg")
  .setParameter("myOrg", myOrg)
  .getSingleResult();

while using criteria you might have:

CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Number> cq = qb.createQuery(Number.class);
Root<Event> root = cq.from(Event.class);
cq.select(qb.max(root.get("dateProcessed")));
cq.where(qb.equal(Event.get("org"), qb.parameter(MyOrgType.class, "myOrg")));
em.createQuery(cq).setParameter("myOrg", myOrg).getSingleResult();

JPQL query - hibernate: with-clause not allowed on fetched associations

You are right, when you add and p.status = :postStatus it filters out the results where the right side of the join does not exist (i.e. when the Campaign has no posts).

What worked for me is to add an OR clause to accept the case where the right side of the join is NULL.

So, you should replace and p.status = :postStatus with and (p IS NULL OR p.status = :postStatus).

So this request should work :

@Query("SELECT c from Campaign c" +
            " left join fetch c.postsList p" +
            " left join fetch p.platform" +
            " left join fetch c.campaignStatistics stat" +
            " where c.id =:id" +
            " and (p is NULL OR p.status = :postStatus)" +
            " and stat.updateDate = :updateDate")

As for the error message you received, I think you should not add the ON clause because that is already handled by JPA/Hibernate.

I am using Hibernate 5.0.12.

JPA Criteria API Join

Try this one.

CriteriaQuery<Long> q = cb.createQuery(Long.class);
Root<Person> person = q.from(Person.class);
Join<Person,Glance> glance = person.join("glanceList", JoinType.INNER);
q.select(cb.count(person))
.where(cb.lt(person.get("duration"), 1000), cb.lt(glance.get("duration"), 1000));

Assuming Glance is the class of the entity in the join.

And

CriteriaBuilder cb = em.getCriteriaBuilder();

JOIN not generated by Hibernate when first fetch-joining then simple-joining

As per Batch fetching - optimizing object graph loading:

JPQL does not allow the aliasing of the join fetch, so if you wish to also query on the relationship, you have to join it twice.

An example why this is prohibited is given in this answer.

Your casting allows to construct the criteria object however the provider doesn't seem to understand it properly and fails to resolve the articles alias.

Replacing the fetch() with join() might not be ideal in terms of loading lazy relationships but should construct a valid query.

---

Related Topics