CODEWITHSUNDEEP
javahibernatejparepositoryjpql
Kristoffer Tølbøll
Kristoffer Tølbøll

Reputation: 3327

Making query combining, one to many relationships in JPA with JPQL query

I have a database. with a one to many relationships.

pet 1--* event.

I want to make a query, that selects all pets, that has had an event on a given date. (I'm using the SQL date format)

As of now I just want to be able to get all entities, for a hardcoded date.

here is the reference in my PetEntity table

@OneToMany
private List<EventEntity> events = new ArrayList();

and in my EventEntity

@ManyToOne
PetEntity pet;

I'm using a pattern where I use a repository to handle the data layer, and then a facade to handle any logic(if any)

So far I have made a method like this.

  public Set<PetEntity> getPetsWithEvents(Date date){
    EntityManager em = emf.createEntityManager();
    Set<PetEntity> entities = new HashSet<>();
    List<EventEntity> eventEntities=  
em.createQuery("SELECT e from EventEntity e where e.date =: date", EventEntity.class).setParameter("date", date).getResultList();
        for(EventEntity entity: eventEntities){
            entities.add(entity.getPet());
        }
        return  entities;
   }
}

Is there a way to simply method this method into using one query, instead of looping through the vent and finding each pet?

Upvotes: 0

Views: 1534

Answers (1)

marknote
marknote

Reputation: 1077

As the others already mentioned, you should be able to select pet join event. The JPQL will be something like below:

SELECT p FROM PetEntity p join p.events e
WHERE e.date =: date

Upvotes: 1

Related Questions