JPA: Query an embeddable List inside an entity
11,620
You must make a join to the PostalCarrier. You can't access a property from a collection. Thus, postalcarrier.area
isn't correct.
select postalcarrier from PostOffice p
inner join p.carriers postalcarrier
where p.id = :postOfficeId
and postalcarrier.area.code = :areaCode
Author by
avonhalo
Updated on June 08, 2022Comments
-
avonhalo almost 2 years
I am trying to "extract" Embeddable classes based on some criterias from a list in an Entity. Either with the help of JPQL or Criteria API. I am not a pro at this, so please help me out. Been googling for 4 hours solid for an answer without any results.
These are the classes:
@Entity public class PostOffice { @Id private Long id; @ElementCollection(fetch=FetchType.LAZY) @CollectionTable(joinColumns=@JoinColumn(name = "CARRIERID")) private List<PostalCarrier> carriers; } @Embeddable public class PostalCarrier { @JoinColumn(name = "area") private Area area; } @Entity public class Area { @Id private int code; }
So, basically what I'm trying to achieve is something like this.
TypedQuery<PostalCarrier> query = entityManager.createQuery("SELECT p.carriers FROM PostOffice p WHERE p.id = ?1 AND p.carriers.area.code = ?2", PostalCarrier.class); query.setParameter(1, postOfficeId); query.setParameter(2, areaCode);
I only want to get a list of the PostalCarriers at specific area code from a specific PostOffice. Any help is much appreciated! :)
I think I'm almost there, but keep getting the following error:
Error compiling the query [SELECT h FROM PostOffice p INNER JOIN p.carriers h WHERE p.id = ?1 AND h.area.code = ?2], line 1, column 71: unknown state or association field [area] of class [com.test.PostalCarrier].