CODEWITHSUNDEEP
jpa
Andrew Wheeler
Andrew Wheeler

Reputation: 281

CriteriaBuilder Many-to-one restriction on id

When doing string based queries it is possible to use the "id" keyword on a many-to-one attribute e.g.:

from Enrolment where person.id=:personId

In this case this would generate sql:

select * from enrolment where person_id=?

How do I achieve the same using the CriteriaBuilder?

CriteriaQuery<Enrolement> query = criteriaBuilder.createQuery(Enrolement.class);
Root<Enrolement> root = query.from(Enrolment.class);
query.select(root);

// Does not work
query.where(criteriaBuilder.equal(root.get("person.id"), 1L));

// Does not work
query.where(criteriaBuilder.equal(root.get("person").root.get("personId"), 1L));

I imagine you could join to person and add a restrict to the person root but that strikes me as overkill.

Upvotes: 2

Views: 3249

Answers (3)

Michael Pichler
Michael Pichler

Reputation: 11

This seems to have changed in Hibernate 6: the id is now required on comparing against an ID value both in HQL and also for the CriteriaQuery.

In Hibernate 5 the HQL condition could be written as where person = 123 (but not 123L) and the criteriaQuery.where(builder.equal(root.get("person"), personId))

In Hibernate 6 the HQL condition must be written as where person.id = 123 and the criteriaQuery.where(builder.equal(root.get("person").get("id"), personId)) (Note that root.get("person.id") is not allowed.)

If you do not add the id you get an error

Cannot compare left expression of type 'domain.MyEntityClass' with right expression of type 'java.lang.Long'

See Query Path Comparison in the Hibernate 6 upgrade guide.

The executed select query just contains where person_id=? as expected.

Upvotes: 1

Wim Deblauwe
Wim Deblauwe

Reputation: 26858

This worked for me in a similar setup:

criteriaBuilder.equal( datasheetRoot.get( Datasheet_.componentSubtype )
                                    .get( ComponentSubtype_.id ), 
                       componentSubtypeId );

(This is using Spring boot 1.0.1 with Spring Data JPA 1.5.1 and Hibernate 4.3.1)

My root is Datasheet which has a @ManyToOne to a ComponentSubtype which in turn has an id.

Upvotes: 2

Andrew Wheeler
Andrew Wheeler

Reputation: 281

After trial-and-error the following works:

query.where(criteriaBuilder.equal(root.get("person"), 1L));

I guess I have been writing string based queries for so long I couldn't see the wood for the trees. It would be nice if the two ways of writing queries was consistent:

from Enrolement where person=1L (does not work)

Upvotes: 0

Related Questions