Issue
I am trying to build JPA criteria for the following scenario. I have the following 3 Entity classes, two of which have a @ManyToOne relationship to UniqueType, but UniqueType does not have an explicit relationship to any other class: The structure is as follows:
------------- ----------- ------------
event N:1 unique_type 1:N detail
------------- ----------- ------------
event_id (PK) type_code (PK) detail_id (PK)
type_code (FK) type_code (FK)
abbreviation
The SQL query that successfully selects the Event records filtered by the abbreviation:
SELECT
event.event_id,
event.type_code,
detail.abbreviation
FROM
event
JOIN unique_type ON event.type_code = unique_type.type_code
JOIN detail ON detail.type_code = unique_type.type_code
WHERE detail.abbreviation = 'ABC'
I have the Event class as my root, but when I join to the UniqueType class, I am stuck because there is no explicit relationship to Detail on UniqueType
Join<Event, UniqueType> joinToUniqueType = root.join(Event_.typeCode);
Join<UniqueType, ???>
I want to be able to search on abbreviation something like:
predicates.add(builder.lower(joinToDetail.get(Detail_.abbreviation)),'ABC');
How can I write the criteria? Any help is much appreciated.
Solution
Since you want inner join semantics, you can use cross joins instead which the database will optimize away anyway. Use the following
Root<Detail> event = query.from(Event.class);
Root<Detail> detail = query.from(Detail.class);
predicates.add(builder.eq(detail.get(Detail_.typeCode), event.get(Event_.typeCode));
predicates.add(builder.lower(detail.get(Detail_.abbreviation)),'ABC');
Answered By - Christian Beikov
Answer Checked By - Gilberto Lyons (JavaFixing Admin)