CODEWITHSUNDEEP
javasqlspringspring-mvcjpa
CodeMed
CodeMed

Reputation: 9189

JPQL query not returning expected results

The following JPQL query is not returning results. How do I alter it so that it returns the results that are expected? 

@SuppressWarnings("unchecked")
public Collection<Description> findDescriptionsForConcept(Concept conc) {
    System.out.println("in JpaSnomedRepository(), conc.getConceptPk().getId() is: "+conc.getConceptPk().getId());;
    Query query = this.em.createQuery("SELECT descr FROM Description descr WHERE descr.concept =:cid");
    query.setParameter("cid", conc);
    return query.getResultList();
}

NOTE: The solution was to change the name of one of the joincolumns in the manytoone relationship in the description class. But I am marking one of the answers below as accepted because the person invested a lot of time trying to help me.

Upvotes: 2

Views: 932

Answers (2)

makasprzak
makasprzak

Reputation: 5220

The query generated by hibernate is fine. Let's analyze this step by step.

  • We have a SnomedDescription entity that has a complex key made of two columns: id and effectiveTime.
  • The SnomedDescription entity has a @ManyTwoOne relationship with another entity named SnomedConcept.
  • SnomedConcept has also a complex key. From your question we are not sure what columns is it made of, but from the @ManyTwoOne relationship definition we can assume that it is also id and effectiveTime. Which is weird actually, because that would mean @OneToOne relationship should be more suitable, like @Alex Malev suggested (or the mapping is defined incorrectly). Basically we can't have two SnomedDescriptions of same id and effectiveTime, so there will be at most one SnomedDescription assotiated with a single SnomedConcept at a time.

  • Why is the generated query fine? Because

    DESCRIPTION.CONCEPT.CONCEPTPK.ID = DESCRIPTION.ID

    That's how the relationship is defined!

  • If the JPQL was something like "SELECT descr FROM SnomedDescription descr WHERE descr.concept = :concept", the generated query would have two constraints: id and effectiveTime and would match at most one row.

  • If you still would like to utilise @ManyToOne relationship, I believe that just removing the second @JoinColumn - of name = "effectiveTime" - would do the trick.

Upvotes: 2

Alexey Malev
Alexey Malev

Reputation: 6533

Try changing method a little bit, setting id as parameter, not the whole Concept.

This code assumes your SnomedDescription class has something like private Concept concept:

Query query = this.em.createQuery("SELECT descr FROM SnomedDescription descr WHERE descr.concept.conceptPk.id =:cid");
query.setParameter("cid", conc.getConceptPk().getId());

Also one more thing looks suspicious for me - Concept and Description are bound with one-to-many relation. Consider revising that, you may want to make Concept has only one Description.

Upvotes: 1

Related Questions